Simple Full Restore
Here is a simple example of restoring a full backup.
RESTORE DATABASE MyDatabase FROM DISK = 'C:\backups\MyDatabase.bak'
If the database is already there, we use the REPLACE to specify we want the existing database to be replaced and we are happy to lose any updates that were applied since the backup was taken, even if they are still present on disk. In the following example we also use the STATS=10 option to get feedback every 10%.
RESTORE DATABASE MyDatabase FROM DISK = 'C:\backups\MyDatabase.bak' WITH REPLACE, STATS = 10
Backup-Based Move
The first thing you should do before any backup-based move operation is to check the file contained in the backup.
RESTORE FILELISTONLY FROM DISK = 'C:\backups\MyDatabase.bak'
With the information provided by that we can define how we want to move the files using the MOVE WITH option. The move specifies the logical name of the file you want to move and the new destination for that file. You can move all files, or just a subset of them. In the following case we restore and move the files for the data and the logs.
RESTORE DATABASE MyDatabase FROM DISK = 'C:\backups\MyDatabase.bak' WITH MOVE 'MyDatabase' TO 'C:\NewLocation\MyDatabase\MyDatabase.mdf' MOVE 'MyDatabaseLog' TO 'C:\NewLocation\MyDatabase\MyDatabaseLog.ldf' REPLACE, STATS = 10
Restore to New Name
This is similar to the backup-based move shown above. This time we restore to a new database name and make sure all the files have a different path and/or name, so we don’t have any collisions. We don’t need the REPLACE option as there is nothing to replace here.
RESTORE DATABASE MyDatabase2 FROM DISK = 'C:\backups\MyDatabase.bak' WITH MOVE 'MyDatabase' TO 'C:\NewLocation\MyDatabase2\MyDatabase2.mdf', MOVE 'MyDatabaseLog' TO 'C:\NewLocation\MyDatabase2\MyDatabaseLog2.ldf', STATS = 10
If this were being done on a new server we wouldn’t need to rename the files, but they would be placed in their original paths, which isn’t great.
Restore Logs
A restore using logs has to start with a full restore. We need to make sure we don’t rollback any uncommitted transactions during the recovery, as that allows us to continue with the restore using the logs. We prevent this rollback using the NORECOVERY option.
RESTORE DATABASE MyDatabase FROM DISK = 'C:\backups\MyDatabase.bak' WITH STATS = 10, NORECOVERY
We can now restore the first log since the full backup. Once again we use NORECOVERY to allow us to continue.
RESTORE LOG MyDatabase FROM DISK = 'C:\backups\MyDatabaseLog1.trn' WITH STATS = 10, NORECOVERY
We can now restore the second log since the full backup. If we know this is the last log we want to restore we can use the RECOVERY option to complete the process. In this case we will use NORECOVERY again.
RESTORE LOG MyDatabase FROM DISK = 'C:\backups\MyDatabaseLog2.trn' WITH STATS = 10, NORECOVERY
If we didn’t use RECOVERY for the last restore, we can still finish the recovery process using the following command, which will rollback any uncommitted transactions.
RESTORE LOG MyDatabase WITH RECOVERY
Until the final recovery has taken place you don’t have any access to the database.
Restore Logs (Standby)
If you need read-only database access during the restore, to check the database contents for example, you can replace the NORECOVERY option with the STANDBY option.
-- Full backup. RESTORE DATABASE MyDatabase FROM DISK = 'C:\backups\MyDatabase.bak' WITH STATS = 10, STANDBY = 'C:\backups\Restore.txt -- Log. RESTORE LOG MyDatabase FROM DISK = 'C:\backups\MyDatabaseLog1.trn' WITH STATS = 10, STANDBY = 'C:\backups\Restore.txt
What this does is rollback the uncommitted transactions, but store them in the standby file, so they can be reapplied before the next restore operation. Between each restore operation the database will be left in read-only mode, allowing you to check the contents of the database. As before, once you’ve restored the last log you can recovery the database.
RESTORE LOG MyDatabase WITH RECOVERY
Restore Differential Backups
Here we restore the full backup, the latest differential, then any additional log backups.
-- Full backup. RESTORE DATABASE MyDatabase FROM DISK = 'C:\backups\MyDatabase.bak' WITH STATS = 10, NORECOVERY -- Latest differential. RESTORE DATABASE MyDatabase FROM DISK = 'C:\backups\MyDatabase.diff' WITH STATS = 10, NORECOVERY -- 0-many logs, using standby. RESTORE LOG MyDatabase FROM DISK = 'C:\backups\MyDatabaseLog1.trn' WITH STATS = 10, STANDBY = 'C:\backups\Restore.txt -- Check the read-only database to see if -- this is the correct place to stop. -- Complete the recovery. RESTORE LOG MyDatabase WITH RECOVERY
For more information see: