0

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.

drye
  • 101
  • 1
  • 3
  • 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 05 '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 05 '10 at 16:22
  • So you're trying to restore it over an existing database? – squillman Aug 05 '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 05 '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 05 '10 at 16:50

1 Answers1

3

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'
lg.
  • 4,649
  • 3
  • 21
  • 20
  • 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 05 '10 at 19:02