Restore multiple SQL Server backup files to the same database

By: Greg Robidoux

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.