Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

1 Answer 1

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

RESTORE DATABASE db_new_name
  FROM DISK = 'C:\MSSQL\BACKUP\old_db.BAK'
  WITH MOVE 'old_db_Data' TO 'c:\MSSQL\DATA\new_db.mdf',
       MOVE 'old_db_Log' TO 'c:\MSSQL\DATA\new_db.ldf',
  RECOVERY

The sql command above can help you:

RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\BACKUP\old_db.BAK'
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

 
discard

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.