Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
"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
up vote 0 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.