Skip to content

SQL Server Database Restore

    To recover a SQL Server database from a failure, a database administrator has to restore a set of SQL Server backups in a logically correct and meaningful restore sequence. SQL Server restore and recovery supports restoring data from backups of a whole database, a data file, or a data page, as follows:

    • The database (a complete database restore)The whole database is restored and recovered, and the database is offline for the duration of the restore and recovery operations.
    • The data file (a file restore)A data file or a set of files is restored and recovered. During a file restore, the filegroups that contain the files are automatically offline for the duration of the restore. Any attempt to access an offline filegroup causes an error.
    • The data page (a page restore)Under the full recovery model or bulk-logged recovery model, you can restore individual databases. Page restores can be performed on any database, regardless of the number of filegroups.

    Restoring a full database

    RESTORE DATABASE AdventureWorks2012
      FROM AdventureWorks2012Backups;

    Restoring full and differential database backups

    RESTORE DATABASE AdventureWorks2012
        FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
        WITH FILE = 6
          NORECOVERY;
    RESTORE DATABASE AdventureWorks2012
        FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
        WITH FILE = 9
          RECOVERY;

    Restoring a database and move files

    RESTORE DATABASE AdventureWorks2012
        FROM AdventureWorksBackups
        WITH NORECOVERY,
          MOVE 'AdventureWorks2012_Data' TO
    'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
          MOVE 'AdventureWorks2012_Log'
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
    RESTORE LOG AdventureWorks2012
        FROM AdventureWorksBackups
        WITH RECOVERY;

    Copying a database using BACKUP and RESTORE

    BACKUP DATABASE AdventureWorks2012
        TO AdventureWorksBackups ;
    
    RESTORE FILELISTONLY
        FROM AdventureWorksBackups ;
    
    RESTORE DATABASE TestDB
        FROM AdventureWorksBackups
        WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MySQLServer\testdb.mdf',
        MOVE 'AdventureWorks2012_Log' TO 'C:\MySQLServer\testdb.ldf';
    GO

    Restoring to a point-in-time using STOPAT

    RESTORE DATABASE AdventureWorks2012
        FROM AdventureWorksBackups
        WITH FILE=3, NORECOVERY;
    
    RESTORE LOG AdventureWorks2012
        FROM AdventureWorksBackups
        WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
    
    RESTORE LOG AdventureWorks2012
        FROM AdventureWorksBackups
        WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
    RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;

    Restoring the transaction log to a mark

    USE AdventureWorks2012
    GO
    BEGIN TRANSACTION ListPriceUpdate
        WITH MARK 'UPDATE Product list prices';
    GO
    
    UPDATE Production.Product
        SET ListPrice = ListPrice * 1.10
        WHERE ProductNumber LIKE 'BK-%';
    GO
    
    COMMIT TRANSACTION ListPriceUpdate;
    GO
    
    -- Time passes. Regular database
    -- and log backups are taken.
    -- An error occurs in the database.
    USE master;
    GO
    
    RESTORE DATABASE AdventureWorks2012
    FROM AdventureWorksBackups
    WITH FILE = 3, NORECOVERY;
    GO
    
    RESTORE LOG AdventureWorks2012
      FROM AdventureWorksBackups
        WITH FILE = 4,
        RECOVERY,
        STOPATMARK = ListPriceUpdate;

    Restoring using TAPE syntax

    RESTORE DATABASE AdventureWorks2012
        FROM TAPE = '\\.\tape0';

    Restoring using FILE and FILEGROUP syntax

    RESTORE DATABASE MyDatabase
        FILE = 'MyDatabase_data_1',
        FILE = 'MyDatabase_data_2',
        FILEGROUP = 'new_customers'
        FROM MyDatabaseBackups
        WITH
          FILE = 9,
          NORECOVERY;
    GO  
    -- Restore the log backups
    RESTORE LOG MyDatabase
        FROM MyDatabaseBackups
        WITH FILE = 10,
          NORECOVERY;
    GO
    RESTORE LOG MyDatabase
        FROM MyDatabaseBackups
        WITH FILE = 11,
          NORECOVERY;
    GO
    RESTORE LOG MyDatabase
        FROM MyDatabaseBackups
        WITH FILE = 12,
          NORECOVERY;
    GO
    --Recover the database
    RESTORE DATABASE MyDatabase WITH RECOVERY;
    GO

    Restoring from the Microsoft Azure Blob storage service

    RESTORE DATABASE Sales
      FROM URL = 'https://mystorageaccount.blob.core.windows.net/mysecondcontainer/Sales.bak'
      WITH MOVE 'Sales_Data1' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_Data1.mdf',
      MOVE 'Sales_log' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_log.ldf',
      STATS = 10;
    RESTORE DATABASE Sales
      FROM URL = 'https://mystorageaccount.blob.core.windows.net/mysecondcontainer/Sales.bak'
      WITH MOVE 'Sales_Data1' to 'H:\DATA\Sales_Data1.mdf',
      MOVE 'Sales_log' to 'O:\LOG\Sales_log.ldf',
      STATS = 10;
    RESTORE DATABASE Sales
      FROM DISK = 'E:\BAK\Sales.bak'
      WITH MOVE 'Sales_Data1' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_Data1.mdf',
      MOVE 'Sales_log' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_log.ldf',
      STATS = 10;

    This command enables you to perform the following restore scenarios:

    • Restore an entire database from a full database backup (a complete restore).
    • Restore part of a database (a partial restore).
    • Restore specific files or filegroups to a database (a file restore).
    • Restore specific pages to a database (a page restore).
    • Restore a transaction log onto a database (a transaction log restore).
    • Revert a database to the point in time captured by a database snapshot.
    --To Restore an Entire Database from a Full database backup (a Complete Restore):
    RESTORE DATABASE { database_name | @database_name_var }
     [ FROM <backup_device> [ ,...n ] ]
     [ WITH
       {
        [ RECOVERY | NORECOVERY | STANDBY =
            {standby_file_name | @standby_file_name_var }
           ]
       | ,  <general_WITH_options> [ ,...n ]
       | , <replication_WITH_option>
       | , <change_data_capture_WITH_option>
       | , <FILESTREAM_WITH_option>
       | , <service_broker_WITH options>
       | , \<point_in_time_WITH_options-RESTORE_DATABASE>
       } [ ,...n ]
     ]
    [;]
    
    --To perform the first step of the initial restore sequence of a piecemeal restore:
    RESTORE DATABASE { database_name | @database_name_var }
       <files_or_filegroups> [ ,...n ]
     [ FROM <backup_device> [ ,...n ] ]
       WITH
          PARTIAL, NORECOVERY
          [  , <general_WITH_options> [ ,...n ]
           | , \<point_in_time_WITH_options-RESTORE_DATABASE>
          ] [ ,...n ]
    [;]  
    
    --To Restore Specific Files or Filegroups:
    RESTORE DATABASE { database_name | @database_name_var }
       <file_or_filegroup> [ ,...n ]
     [ FROM <backup_device> [ ,...n ] ]
       WITH
       {
          [ RECOVERY | NORECOVERY ]
          [ , <general_WITH_options> [ ,...n ] ]
       } [ ,...n ]
    [;]  
    
    --To Restore Specific Pages:
    RESTORE DATABASE { database_name | @database_name_var }
       PAGE = 'file:page [ ,...n ]'
     [ , <file_or_filegroups> ] [ ,...n ]
     [ FROM <backup_device> [ ,...n ] ]
       WITH
           NORECOVERY
          [ , <general_WITH_options> [ ,...n ] ]
    [;]
    
    --To Restore a Transaction Log:
    RESTORE LOG { database_name | @database_name_var }
     [ <file_or_filegroup_or_pages> [ ,...n ] ]
     [ FROM <backup_device> [ ,...n ] ]
     [ WITH
       {
         [ RECOVERY | NORECOVERY | STANDBY =
            {standby_file_name | @standby_file_name_var }
           ]
        | , <general_WITH_options> [ ,...n ]
        | , <replication_WITH_option>
        | , \<point_in_time_WITH_options-RESTORE_LOG>
       } [ ,...n ]
     ]
    [;]
    
    --To Revert a Database to a Database Snapshot:
    RESTORE DATABASE { database_name | @database_name_var }
    FROM DATABASE_SNAPSHOT = database_snapshot_name
    
    <backup_device>::=
    {
       { logical_backup_device_name |
          @logical_backup_device_name_var }
     | { DISK
         | TAPE
         | URL
       } = { 'physical_backup_device_name' |
          @physical_backup_device_name_var }
    }
    Note: URL is the format used to specify the location and the file name for the Microsoft Azure Blob. Although Microsoft Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seamless restore experience for all the three devices.
    <files_or_filegroups>::=
    {
       FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
     | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
     | READ_WRITE_FILEGROUPS
    }
    
    <general_WITH_options> [ ,...n ]::=
    --Restore Operation Options
       MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
              [ ,...n ]
     | REPLACE
     | RESTART
     | RESTRICTED_USER | CREDENTIAL
    
    --Backup Set Options
     | FILE = { backup_set_file_number | @backup_set_file_number }
     | PASSWORD = { password | @password_variable }
    
    --Media Set Options
     | MEDIANAME = { media_name | @media_name_variable }
     | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
     | BLOCKSIZE = { blocksize | @blocksize_variable }
    
    --Data Transfer Options
     | BUFFERCOUNT = { buffercount | @buffercount_variable }
     | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
    
    --Error Management Options
     | { CHECKSUM | NO_CHECKSUM }
     | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
    
    --Monitoring Options
     | STATS [ = percentage ]
    
    --Tape Options.
     | { REWIND | NOREWIND }
     | { UNLOAD | NOUNLOAD }
    
    <replication_WITH_option>::=
     | KEEP_REPLICATION
    
    <change_data_capture_WITH_option>::=
     | KEEP_CDC
    
    <FILESTREAM_WITH_option>::=
     | FILESTREAM ( DIRECTORY_NAME = directory_name )
    
    <service_broker_WITH_options>::=
     | ENABLE_BROKER
     | ERROR_BROKER_CONVERSATIONS
     | NEW_BROKER
    
    \<point_in_time_WITH_options-RESTORE_DATABASE>::=
     | {
       STOPAT = { 'datetime'| @datetime_var }
     | STOPATMARK = 'lsn:lsn_number'
                     [ AFTER 'datetime']
     | STOPBEFOREMARK = 'lsn:lsn_number'
                     [ AFTER 'datetime']
       }
    
    \<point_in_time_WITH_options-RESTORE_LOG>::=
     | {
       STOPAT = { 'datetime'| @datetime_var }
     | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                     [ AFTER 'datetime']
     | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                     [ AFTER 'datetime']
       }