Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Restoring multiple backups to the same database



By:

Overview

The RESTORE ... WITH NORECOVERY option puts the database into a "restoring" state, so additional backups can be restored.  When the database is in a "restoring" state no users can access the database or the database contents.

Explanation

When you issue a RESTORE DATABASE or RESTORE LOG command the WITH NORECOVERY option allows you to restore additional backup files before recovering the database.  This therefore allows you to get the database as current as possible before letting your end users access the data.

This option is not on by default, so if you need to recover a database by restoring multiple backup files and forget to use this option you have to start the backup process all over again.

The most common example of this would be to restore a "Full" backup and one or more "Transaction Log" backups.


T-SQL

Restore full backup and one transaction log backup
The first command does the restore and leaves the database in a restoring state and second command restores the transaction log backup and then makes the database useable.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

Restore full backup and two transaction log backups
This restores the first two backups using NORECOVERY and then RECOVERY for the last restore.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks2.TRN'
WITH RECOVERY
GO

Restore full backup, latest differential and two transaction log backups
This restores the first three backups using NORECOVERY and then RECOVERY for the last restore.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks2.TRN'
WITH RECOVERY
GO

SQL Server Management Studio

To restore a database backup using the WITH NORECOVERY option go to the options page and select the item highlighted below.






More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools