Restore Database

Order To Restore Database Backups In

I’m often asked (both online and offline) once you have all your database backups, in what order to they need to be restored in?

I’ve actually asked this very question to senior level DBAs as an interview question before, and gotten some very interesting answers.

When restoring your database you start with your full backup, then restore your diffential backup (if you have taken one) then restore all the transaction log backups begining with the backup taken after the full (or differential) and going until the last transaction log backup available (or until you reach the point in time you want to stop the restore at).

Denny

Back To Basics: The RESTORE DATABASE Command

The restore database command is what is used to recover a database which has been backed up using the BACKUP DATABASE command.  The syntax of the RESTORE DATABASE command is very similar to the BACKUP DATABASE command in many respects.

 You start with where you are restoring the database from.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'

Then if you need to move the physical files to another location because your disks are laid out differently, or because your folder layout is different you can add a MOVE command for each file you want to move.  For each MOVE command you specify the local file name, and the new physical file name.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’

When restoring the database, many people think that you have to create the database first.  You do not.  When restoring a database through the UI (Enterprise Manager, or SQL Server Management Studio) if you create the database first, it will then be selectable in the drop down menu.  Even with using the UI, creating the database first is optional as you can simply type in the name of the new database in the UI.

If you are restoring from a striped database backup then you will need to specify the name of all the members of the strip.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase1.bak',
  DISK=’E:\Backups\MyDatabase2.bak’
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’

If you need to restore a differential or log backups after you restore your full backup you will want to place you backup with the NORECOVERY flag. This will tell SQL Server not to complete the recovery process, and to leave the database in an unusable state. This will allow you to continue the restore process. Once the database has been switched into a writable state you won’t be able to restore any transaction logs to the database without restoring from the full backup again.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’,
  NO RECOVERY

Denny