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

I have a database in MS SQL Sever 2005 Express. I need to make a duplicate of it to test some upgrade scripts. I've tried to backup and restore to a new database, and it fails because it can tell the databases are different. I don't see a way to script out the entire database, otherwise I'd try that. What is the best way to make a copy of an existing database on the same machine.

EDIT: I need all data and schema.

share|improve this question
What do you mean by "it fails because it can tell the databases are different"? The upgrade script fails or the backup/restore fails? Also, do you need the data or just the db schema? – squillman Aug 5 '10 at 16:07
I need both data and schema, and the "it fails..." relates to the sentence it is in, the backup and restore fails because it detects that the database from the backup is different then the target for the restore. – drye Aug 5 '10 at 16:22
So you're trying to restore it over an existing database? – squillman Aug 5 '10 at 16:34
No, a new database. Turns out there is a force parameter and you have to move the MDF and LDF files. – drye Aug 5 '10 at 16:49
Got it. Yeah, that was where I was going next. lg's answer should do it for you also. – squillman Aug 5 '10 at 16:50

Backup and restore is the quickest way to do it. With RESTORE sql command you specify the name of new database:

  WITH MOVE 'old_db_Data' TO 'c:\MSSQL\DATA\new_db.mdf',
       MOVE 'old_db_Log' TO 'c:\MSSQL\DATA\new_db.ldf',

The sql command above can help you:

share|improve this answer
that didn't quite work for me, but your suggestion reminded me to move the mdf and ldf files. On top of that I also had to use the force check box. – drye Aug 5 '10 at 19:02

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.