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:BackupsMyDatabase.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:BackupsMyDatabase.bak'
WITH MOVE 'MyDatabase_Data' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Data.mdf',
  MOVE 'MyDatabase_Log' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_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:BackupsMyDatabase1.bak',
  DISK='E:BackupsMyDatabase2.bak'
WITH MOVE 'MyDatabase_Data' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Data.mdf',
  MOVE 'MyDatabase_Log' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_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:BackupsMyDatabase.bak'
WITH MOVE 'MyDatabase_Data' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Data.mdf',
  MOVE 'MyDatabase_Log' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Log.ldf',
  NO RECOVERY

Denny

Share

2 Responses

  1. Hi Denny,
    Nice Post..

    I would like to know something more about restore database with move files. Which file name I should use in WITH MOVE ‘MyDatabase_Data’ TO statement.
    Actually my company assigned me a task of bulk restore from files. I don’t know the original file names when they were backed up. So which file name I should write in that statement ?
    Or any other option for bulk restore ?

    Thanks in advance

  2. In that case use the RESTORE FILELISTONLY command to get a list of the database files which are contained within the database backup.

    [CODE]RESTORE FILELISTONLY from disk=’D:YourBackup.bak'[/CODE]

    Denny

Leave a Reply to GaurangbtripathiCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?