SQL Server Recovery States

Question

When restoring a SQL Server Database, I notice that there are 3 different Recovery States to choose from:

• Restore with Recovery
• Restore with No Recovery
• Restore with Standby

I've always left it at it's default value, but what do they all mean?

(Preferably in layman's terms)

Answer 1

GateKiller,

In simple terms (and not a copy-paste out of the SQLBOL) so you can understand the concepts:

RESTORE WITH RECOVERY uses the backup media file (eg. fulldata.bak) to restore the database to back to the time that backup file was created. This is great if you want to go back in time to restore the database to an earlier state - like when developing a system.

If you want to restore the database TO THE VERY LATEST DATA, (i.e. like if your doing a system Disaster Recovery and you cannot lose any data) then you want to restore that backup AND THEN all the transaction logs created since that backup. This is when you use RESTORE NORECOVERY. It will allow you to restore the later transaction logs right up to the point of failure (as long as you have them).

RECOVERY WITH STANDBY is the ability to restore the database up to a parital date (like NORECOVERY above) but to allow the database still to be used READONLY. New transaction logs can still be applied to the database to keep it up to date (a standby server). Use this when it would take too long to restore a full database in order to Return To Operations the system. (ie. if you have a multi TB database that would take 16 hours to restore, but could receive transaction log updates every 15 minutes).

This is a bit like a mirror server - but without having "every single transaction" send to the backup server in real time.

Answer 2

You can set a Microsoft SQL Server database to be in NORECOVERY, RECOVERY or STANDBY mode.

RECOVERY is the normal and usual status of the database where users can connect and access the database (given that they have the proper permissions set up).

NORECOVERY allows the Database Administrator to restore additional backup files such as Differential or Transactional backups. While the database is in this state then users are not able to connect or access this database.

STANDBY is pretty much the same as NORECOVERY status however it allows users to connect or access database in a READONLY access. So the users are able to run only SELECT command against the database. This is used in Log Shipping quite often for reporting purposes. The only drawback is that while there are users in the database running queries SQL Server or a DBA is not able to restore additional backup files. Therefore if you have many users accessing the database all the time then the replication could fall behind.