SQL Server -- Different RESTORE Methods

The RESTORE command is what we use to restore database backups, previously created using the BACKUP command (see this post).  Much like BACKUP, you may perform the RESTORE in the gui, but in this post I will show you varied RESTORE methods using tSQL. I am going to be using my DBA database in each of the following examples.  

This would be the most basic form of the RESTORE statement:

     RESTORE DATABASE DBA
     FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak'
     GO

In the above example, we are restoring the DBA database from the referenced bak file. Of course, the RESTORE will fail if the bak file is not there.  This is the error you would receive:

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device 'C:\MSSQL\Backup\DBA_20150209.bak'. Operating system error 2
    (The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

Ok. Let's assume that you've got the bak file in place, but you're not exactly sure of the database file names or paths for the DBA data/log files.  You can run this statement to provide the logical and physical name of all database files.

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

This is a portion of the output from my DBA database:

With these details we could perform the RESTORE as I sampled in the first example, or we will use the MOVE option. This option is used to restore the files to another location on disk. For example, let's say I am restoring the DBA database to another machine, with a completely different directory structure. We would restore the bak file to the D drive on the new box, like this:

RESTORE DATABASE DBA

FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 

WITH 

            MOVE 'DBA_Primary' TO 'D:\MSSQL\Data\DBA_Primary.mdf', 

            MOVE 'DBA_Data' TO 'D:\MSSQL\Data\DBA_Data.mdf', 

            MOVE 'DBA_Log' TO 'D:\MSSQL\Log\DBA_Log.ldf'

      GO

Or, maybe I want to create a copy of the DBA database, on the same server, yet with a different name. We accomplish that also with the MOVE option, and a new database name, like this:

RESTORE DATABASE DBANEW

FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 

WITH

            MOVE 'DBA_Primary' TO 'C:\MSSQL\Data\DBANEW_Primary.mdf', 

            MOVE 'DBA_Data' TO 'C:\MSSQL\Data\DBANEW_Data.mdf', 

            MOVE 'DBA_Log' TO 'C:\MSSQL\Log\DBANEW_Log.ldf'

      GO

This next example demonstrates how to RESTORE the database AND differential backup for the DBA database. Note we use NORECOVERY for the first part of the RESTORE of the database backup. This will leave the database unusable, or not fully recovered, until the dif is also restored.

      RESTORE DATABASE DBA 

      FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 

      WITH NORECOVERY

      GO

      RESTORE DATABASE DBA 

      FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.dif'

      WITH RECOVERY 

      GO

Ok.  What about the DBA LOG backup?  In this example we restore the DBA log from a bak file created on another server, with a different directory structure. We do this using the MOVE option.

      RESTORE LOG DBA

     FROM DISK = 'C:\MSSQL\Backup\DBA_LOG_20150209.bak' 

     WITH NORECOVERY, FILE = 1

          MOVE 'DBA_Primary' TO 'D:\MSSQL\Data\DBA_Primary.mdf', 

          MOVE 'DBA_Data' TO 'D:\MSSQL\Data\DBA_Data.mdf', 

          MOVE 'DBA_Log' TO 'D:\MSSQL\Log\DBA_Log.ldf'

GO

Now we will perform the final RESTORE with RECOVERY, so that the database will again be usable:

     -- Perform final restore

     RESTORE DATABASE DBA

     WITH RECOVERY

     GO

On a slightly different note, how do you know your backup file is even usable?  Maybe it's a copy from somebody else, and you're not aware of how it was created. Here we can use the VERIFYONLY option. This allows us to check the bak file BEFORE running the RESTORE, to ensure that it is valid. This is the statement, and the desired output:

Lastly, one thing that people often overlook is the database ownership.  When you restore a database - in the gui or via tSQL - the database ownership is inherited by whomever is performing the restore. After a restore, right click your database in SSMS, choose Properties;  You can see the Owner is your domain login, rather than 'sa', or any other authorized owner that you may be using.  Easy enough to correct, using this statement:

USE DBA -- change this to whatever database you are targeting

EXEC sp_changedbowner 'sa'