1

The reason I ask this is because it doesn't work. I haven't tried taking it offline yet because it is almost always in use. In the worst case I will attempt to take it offline and use "Copy Database" but I'm looking for alternatives first.

We want to make a copy to use in a test environment. Would it work if I did a one time Back Up/Restore? Or would there be some sort of error having two databases named the same on one SQL server? If not, would there be a way to change the name of database?

Even if the back up didn't work, we still have back ups of the primary database so we could always use those.

We are running MSSQL Server 2005

Dan P.
  • 115
  • 4
  • "Would it work if I did a one time Back Up/Restore?" Yes to another server or a named instance. "Or would there be some sort of error having two databases named the same on one SQL server?" SQL Server won't let this happen but you could restore to a different name (and different, underlying mdf and ldf files). – jl. Oct 27 '10 at 19:07
  • +1 for the Copy Database Wizard is a piece of junk. – phoebus Oct 27 '10 at 19:37

2 Answers2

0

You can do a backup and restore to the same server/instance, but only if you change the name of the database (and the filenames) when you restore.

To keep the same name you would have to restore to a different instance.

HTH, Dan

SQL3D
  • 670
  • 1
  • 6
  • 11
0

If you don't want to affect transaction logs in a production environment, I might recommend you backup and restore from the command line:

  1. First make a backup using the backup command from within SQLCMD.exe with the COPYONLY option

  2. Then, restore using the RESTORE command. NOTE: that if the database moves servers or directories, then you need to use RESTORE with the MOVE options.

djangofan
  • 4,182
  • 10
  • 46
  • 59