SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY

I maintain a spreadsheet of questions sent by users and from that I single out a topic to write and share my knowledge and opinion. Unless and until I find an issue appealing, I do not prefer to write about it, till the issue crosses the threshold. Today the question that crossed the threshold is – what is the difference between NORECOERY and RECOVERY when restoring database.

The fact is that one can easily go to MSDN and learn the difference, so I head myself to MSDN and read the difference. This is what the Book On Line suggests here.

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them forward.
RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

Book On Line has very clearly explained the same subject and provides succinct explanation of the difference.

In other words -

While doing RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep database in state where more backup file are restored. This will also keep database offline also to prevent any changes, which can create itegrity issues. Once all backup file is restored run RESTORE command with RECOVERY option to get database online and operational.

It is also important to be acquainted with the restore sequence of how full database backup is restored.

First, restore full database backup, differential database backup and all transactional log backups WITH NORECOVERY Option. After that, bring back database online using WITH RECOVERY option.

Following is the sample Restore Sequence

RESTORE DATABASE DATABASE FROM full_database_backup WITH NORECOVERY;
RESTORE DATABASE DATABASE FROM differential_backup WITH NORECOVERY;
RESTORE LOG DATABASE FROM log_backup WITH NORECOVERY;
-- Repeat this till you restore last log backup
RESTORE DATABASE DATABASE WITH RECOVERY;

I hope now it is very clear to you all what is restore sequence and the difference between recovery options.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

35 thoughts on “SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY

  1. Hi Pinal,

    For recovery process as you wrote a code

    RESTORE DATABASE DATABASE FROM full_database_backup WITH NORECOVERY;
    RESTORE DATABASE DATABASE FROM differential_backup WITH NORECOVERY;
    RESTORE LOG DATABASE FROM log_backup WITH NORECOVERY;

    But as My experiance, The last lag_backup must be WITH RECOVERY mode

    RESTORE DATABASE COREDATA FROM DISK=’H:\CDATA\FULL_CoreData.bak’ WITH REPLACE, NORECOVERY;
    RESTORE DATABASE COREDATA FROM DISK=’H:\CDATA\DIFF_CDATA.BAK’ WITH REPLACE, NORECOVERY;
    RESTORE DATABASE COREDATA FROM DISK=’H:\CDATA\CDATA_tail.bak’ WITH REPLACE, RECOVERY;

    With Regards,
    Rajiv Singh

    Like

  2. THIS IS WHAT PINAL TOLD US:

    READ AGAIN:

    — Repeat this till you restore last log backup
    RESTORE DATABASE DATABASE WITH RECOVERY;

    ALLWAYS THE LAST WILL BE WITH RECOVERY.

    AND NEVER FORGET TO VERIFY ONLY YOUR BACKUP FILES BEFORE RESTORING.

    REGARDS,
    MARCOS ROSA

    Like

  3. What if the last successful restore was completed in NORECOVERY, then you discover the final differential backup is corrupted… Can simply set the database to operational mode (truncating any open logs) or do you have to do something spectactular?

    Thx!!!!

    Like

  4. Hi Pinal,
    Can’t we use the Standby Option when we restore the database so that we can apply the differential and log backups to bring the database into consistent state.

    Regards
    Vivek Singh

    Like

  5. @Vivek.

    When you restore a backup, you can choose from 3 restore option,

    1. With Recovery – Database is ready to use, and user can connect to database, user can change data inside database.

    2. No Recovery – Database is not ready, there are few more backups that has to be applied to this database instance. User cannot connect to database because it is in Restoring Status. (Exception: Not considering Database Snapshots )

    3. Standby / Read Only – Database is ready to use but database is in Read Only mode, user can connect to database but they cannot change data inside database.

    Now, if you have more backups to apply, definitely you want to choose with No Recovery Mode option when applying all your backups, and when you apply your last / final backup, then either you can restore with Recovery or Standby option.

    ~ IM.

    Like

  6. Hello Ahmad,

    A running database con not be changed to standby mode. Only a data in no-recovery state can be moved to standby mode. This is an option that is specified while restoring a database or transaction log.

    Regards,
    Pinal Dave

    Like

  7. I have to restore transcation logs to a secondary database every 30 minutes.Can i restore a transaction log backup when the database is in RECOVRY mode. ? i dont want to set the database in NORECOVERY Mode each time i carry out the restoration process.

    Like

  8. Hello Rashmi,

    Once a database converted in RECOVERY mode, no more transaction log backup can be restored. You can not set a database back to NORECOVERY mode.
    So to resotre more transactional log backups keep the database in NORECOERY mode.

    Regards,
    Pinal Dave

    Like

  9. Hi Pinal,

    When a transaction log recovery is running on the database with recovery mode, can th database be accessible to other users, for example can we fire a select query to the database? If yes then what is the process to do so? Since, we tried that when we were running the recovery on the DB and we parallely fired a select query, the recovery got aborted.

    Please suggest.

    Our aim is that when we are restoring the transaction log the database should be accessible to the users.

    Thanks in advance!!

    Like

  10. Hello Rashmi,

    A database in recovey mode can be made accessible using STANDBY option. This option is usually used in log shipping to make the secondary database available.

    Regards,
    Pinal Dave

    Like

  11. Hi Pinal Dave,

    We have a Production DB Server (SQL Server 2000) and Reporting DB Server (SQL Server 2005)
    Earlier both servers were having SQL 2000. But now they have upgraded Reporting DB Server to SQL 2005.
    Backup Process – We have a process which syncs 5 databases between Production and Reporting. Complete restore is taken on a weekly basis (Sunday) and transaction logs are taken on a daily basis.

    After upgradation of Reporting server to SQL Server 2005, backup process is not running as expected.
    We were able to restore the complete backup successfully which happens weekly. But having some issues while restoring transaction logs.

    While trying to restore the database and transaction logs , we have 3 status modes:
    Recovery : We can’t use it, as it does not allow to restore transaction logs. If transaction logs are not there, we can use it very well for complete backup.
    NoRecovery : It can be used to restore but again database is no useable. It will be in recovery mode.
    StandBy : In the old reporting server(SQL 2000), they were using StandBy mode.
    It is read-only mode. But we are not able to use it as it says a message “This backup cannot be restored using WITH STANDBY because a database upgrade
    is needed. Reissue the RESTORE without WITH STANDBY.”
    As backup is from SQL 2000, it can’t be restored on SQL 2005.

    We have restored the complete backup which was done using “recovery” mode. If complete backup is done in “recovery” mode, it is not restoring transaction logs.

    Could you please help me out in resolving this issue

    Like

  12. Hello Ravi,

    You can not use WITH STANDBY clause to restore a SQL Server 2000 database in SQL Server 2005.
    You would have to upgrade the source server to 2005.

    Regards,
    Pinal Dave

    Like

  13. Hi Pinal,

    yes i understand i cant use standby clause. Currently my db full backup restored using recovery mode so that the users can access the db after restoration. This happens on a weekend. Now we are unable to restore the daily transactional logs as the DB is in the recovery mode. In this scenario we have following questions
    1. how can we change the mode of the DB to No recovery?
    2. Is there anyother way that we can restore the transaction logs when the DB is in recovery mode?
    3. From some of the article i found out that, the transaction logs should be restored with “no Recovery” and the final transaction log should be restored with “Recovery”

    Like

  14. Pingback: SQL SERVER – Fix : Error : 3117 : The log or differential backup cannot be restored because no files are ready to rollforward Journey to SQL Authority with Pinal Dave

  15. Hi,

    I have current Live server which from now I want to use as Secondary for Log shipping. and then setup a new server as Primary.
    But I don’t know how to convert current live to take in NoRecovery Mode without data loss.

    I am very confused in this.

    Thanks!

    Like

  16. A best practice is to restore all log files with NORECOVERY.
    Then, follow that with:

    restore database with recovery

    Microsoft suggests this practice to prevent accidental recovery before all logs are restored.

    Like

  17. A portion of my reply was removed when I posted.

    The command syntax is
    RESTORE DATABASE dbname WITH RECOVERY

    where dbname is the name of the database you are recovering.

    Like

  18. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  19. Dear All,

    My sql server database goes to recovery mode on every 2-3 hours. current database size is 3173 MB.

    Could you please any one help to resolve this?

    Thanks

    Like

  20. Hi Everyone,

    I tried to restore SQL Server 2000 xxx.bak database on another database. The code is

    Use Master
    go

    Alter Database xxx
    SET SINGLE_USER With ROLLBACK IMMEDIATE

    RESTORE DATABASE xxx
    FROM DISK = N’E:\Backup\xxxxx\xxxxxx.bak’
    WITH FILE = 1,
    MOVE N’xxx99_Data’
    TO N’E:\MSSQLDATA\exercises_edi.mdf’,
    MOVE N’xxx99_Log’
    TO N’E:\MSSQLLOGS\exercises_edi.ldf’,
    NOUNLOAD,
    REPLACE,
    STATS = 10
    GO

    The process got disconnected in the middle and still the database appears to be in “restoring mode”. How can I stop that mode and start again from scratch

    Like

  21. Dear Pinal Dave,

    can i restore LOG without restore the FULL backup at first, namely i need to restore a full backup first then i need to add new files to make partitioning on 2 tables, after that i need to restore LOG backup. so i need the database to be online but i will not make any transaction on database while it’s online.

    it this double?

    Like

  22. Hi Pinal Sir,

    I have been a frequent visitor of your Site, and must say it is quite informative and helpful !! Hearty Thank You.

    I was wondering if you could also let us know something about the “Security”

    Regards,
    Rukku

    Like

  23. Hi Pinal Sir,

    Please let me know, if there is a way to create a full backup daily with log backups/ differential backups.

    My Primary Server is too far. I am sitting at the DR Site. I cannot transfer fullbackup daily. I need to take full backup and copy to tape.

    Regards,
    nvn

    Like

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  25. sir,
    in my office we hv been using ms sql server management studio as a backend.
    each time after inputting id and paswd an error occurs ” sql server does not allow remote connection” i.e error 26 ..please suggest any solution..i hv checked the tcp/ip also..bt still the problem is same.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s