Proper way to duplicate a database

Question

Hi,

I have a SQL Server Database that is used by the company I work for. I'll call this one 'ProductionDatabase'.

I'd like to create a duplicate copy of 'ProductionDatabase' called 'TestDatabase' that I can use for my own purposes. (I'm going to try to develop a new front end application, but I do not want to risk compromising the company's real data).

It is not clear to me how to do this. I did a Full Backup of ProductionDatabase that I saved to disk as a .bak file. Now I am trying to figure out how to restore this backup to a differently named database (TestDatabase). In the 'Restore Database' utility in SSMS, I couldn't find a way to restore a backup to a database name that doesn't exist. This led to me creating a new (empty) database named 'TestDatabase'. I then went back into the Restore Database utility and tried to restore from the .bak file to the newly created 'TestDatabase'. When I tried this, I got the following error message:

"Restore of database 'TestDatabase' failed. However, the Tail-log backup operation completed successfully."

If anyone here can tell how I can accomplish this task, I would greatly appreciate it.

Thanks in advance,
Paul

Answer 1

This is the command:

RESTORE DATABASE TestDatabase FROM DISK = 'C:\temp\MyBackup.bak'
WITH MOVE 'ProductionDatabase' TO '<somepath>\TestDatabase.mdf'.
    MOVE 'ProductionDatabase_log' TO '<somepath>\TestDatabase.ldf'

Some notes:

The name that follow DISK = is the path to the backup file. Replace with the actual backup.

The names that follow MOVE are the logical names of the files, and must agree with those in the backup. You can find them with this command:

RESTORE FILELISTONLY FROM DISK = 'C:\temp\Backup.bak'

The logical names are in the first column.

The names that follow TO are the file locations for the files of the new database. They can be anywhere, but you should make a judicious choice. Presumably they should be in the same path as other database. (You can find the locations of the files for an existing database with sp_helpdb.)

If you created an empty database called TestDatabase, drop it first. RESTORE will create a new database. If you keep the empty database, you will get an error as SQL Server thinks you may lose data if it would just wipe away the files for the existing database.

As for the dialogues in SSMS, I can't help much there since I never use them. But I recall that they are more confusing than helpful.

Answer 2

Thank you Erland - that worked perfectly!!

Best regards,
Paul