Restore Database Using SQL Server NORECOVERY Option

Andrew Jackson ~ Modified: January 14th, 2019 ~ SQL Backup & Restore ~ 2 Minutes Reading

While doing a restore operation in SQL Server, it is recommended to perform the SQL Server NORECOVERY option rather than RECOVERY option. This enables the database to be kept in a state where there are number of backups. This NORECOVERY parameter keeps the database offline and thus prevents any changes to be made in the database which can cause integrity issues. Once the database is recovered, run RESTORE command accompanied with RECOVER command in order to get the database functions in online state.

Difference Between RECOVERY And NORECOVERY

The rollback process of SQL Server database is controlled by RECOVERY and NORECOVERY options. RECOVERY option makes sure that the rollback does not occur. As a result roll forward is continued with the next statement that comes up in the sequence. With this option, the restore command restores all the sequences and they are forwarded.

On the other hand, RECOVERY option makes sure that the rollback operation is performed once the roll forward operation has been completed successfully. It is to be taken care that the roll forward should be set far enough so as to make it consistent with the database. If not, the Database Engine may generate an error.

Syntax to Restore Database With NORECOVERY Option in SQL Server

In order to restore complete database, restore database from full backup, differential database backup and transactional log backup is done with NORECOVERY option. Then afterwards, using WITH RECOVERY option the database is brought back online.

Full Database Backup Using NORECOVERY

Transactional Log Backup Using NORECOVERY

Differential Backup Using NORECOVERY

Conclusion

Be careful while restoring a database with SQL Server NORECOVERY option. All the restore operations of the logs need to be done with NORECOVERY option but the last log backup should be restored with RECOVERY option.