Restoring a SQL Server full backup to a new database

Assume you are in either of these two situations:

1. You have an existing database (say, "test") which you want to move to another database (say, "development"), or
2. You have a full backup file of a database, and you must create a new database from this database.

Basically, working with SQL Server databases is quite simple if you are able to detach or shutdown the database server. In this case, you can simply copy the .mdf and .ldf files, rename them, and attach them to SQL Server. But, if the source system (where the original database is) is always in production or you don't have the permission to detach for example, you need to work with backups.

Here's the quick summary: create a full backup of the source database, and then run a RESTORE DATABASE command specifying the new destination name (and .mdf/.ldf file locations) for the database to be created. Except for security settings, it doesn't matter to which database server you make the restore to.

Here's an example situation: you have a single database server in which you are running a database named "NorthwindTest". This database is constantly in use (say, by other developers in your team), so you cannot detach it, and thus the .mdf file is also locked. But, you can take a full backup of the database. This is good.

Now you have a .bak file from the NorthwindTest database, and from it, you want to create a new database called "NorthwindDevelopment". Efficiently, this new database will become a copy of the original source database. The next step is to run the RESTORE DATABASE command. Assuming the .bak file is at "C:\Backups\NorthwindTest.bak" and you want the new .mdf file to be stored at "C:\Database\NorthwindDevelopment.mdf", the command would be:

RESTORE DATABASE [NorthwindDevelopment]
FROM DISK = 'C:\Backups\NorthwindTest.bak'
WITH MOVE 'NorthwindTest' TO 'C:\Database\NorthwindDevelopment.mdf',
MOVE 'NorthwindTest_log' TO 'C:\Database\NorthwindDevelopment.ldf'

This is simple, but the "MOVE" parameters can cause trouble. The parameter immediately after the MOVE word refers to the source databases' logical name and the log name that were specified when the database was created (if you created the database interactively using SQL Server Management Studio [SSMS], the logical names are usually "mydata" and "mydata_log" respectively). If you are unsure, you can simply view the properties of the source database, or issue the command:

RESTORE FILELISTONLY

This command lists the logical names inside the backup file, and you can think of it as being a query statement. Then, you would substitute these names next to the MOVE parameters in the above RESTORE DATABASE command. Also note that the MOVE parameters have nothing to do with the backup filename but instead what's inside the backup file.

Remember, that this quick tip cannot discuss all the related security details such as backup permissions, user roles, and so on. With the newly created database that was restored from the backup, you will have to check manually whether the security settings are correct.

Good luck!