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.

5
  • 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, 2010 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, 2010 at 16:22
  • So you're trying to restore it over an existing database?
    – squillman
    Aug 5, 2010 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, 2010 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, 2010 at 16:50

1 Answer 1

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'
1
  • 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, 2010 at 19:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .