Friday, October 29, 2010

SQL Server - Restore Database and difference between RECOVERY and NORECOVERY

When I was preparing myself for exam 70-432 I found, that one objective was about database restoring. After study I had one question I couldn't answer. The question was: what is the difference between RECOVERY and NORECOVERY? After a short googling I found some definitions. On MSDN I found this:

Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:

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.

But in books online I think it is much more better explained:

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 mydbname FROM full_database_backup WITH NORECOVERY;

RESTORE DATABASE mydbname FROM differential_backup WITH NORECOVERY;

RESTORE LOG mydbname FROM log_backup WITH NORECOVERY;

-- Repeat this till you restore last log backup

RESTORE DATABASE mydbname WITH RECOVERY;

11 comments:

  1. sql repair has all necessary probabilities for working out troubles related to bad mdf files. It can repair .mdf files after various issues: power failures, viruses, hacker attacks. The tool view restored mdf files for you.

    ReplyDelete
  2. Restoring data from corrupt SQL Database is not an easy task. A third party SQL database recovery software may help the users to get upto 100 % data in such type situations. One of the best and globally used application can be downloaded from here:

    http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

    ReplyDelete
  3. There are so many tools that i have used for sql database recovery because the most of the tool does not work successfully. But sql database recovery is possible by only tool. the mdf repair tool is available for sql database recovery.

    ReplyDelete
  4. It repairs and restores all SQL Server components like tables, functions, stored procedures, views, constraints, triggers and more.Compatible with all Windows versions and recovers database from SQL Server 2000, 2008 and 2005.

    Download:- http://www.filesrecoverytool.com/sql-database-repair.html

    ReplyDelete
  5. I would like to share another Sql Database Recovery Software efficiently recovers all corrupt database files (mdf & ndf) and shows the preview of repaired database that will be recovered by the software.It repairs & restores all SQL database objects like table, trigger, view, index, primary key, foreign key and many more.

    So Download:- http://www.pcrecoveryutility.com/sql-database-recovery.html

    ReplyDelete
  6. Check my Blog post for Repair MDF File of SQL Server :-

    http://repairandmanage.blogspot.in/2014/06/sql-server-recovery.html

    Hope this blog post is useful to you

    Thanks

    ReplyDelete
  7. SQL Server Recovery Software are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. There are basically three different types of recovery models available in SQL Server 2000 and higher versions namely Simple, Full and Bulk Logged. The choice of a specific recovery model purely depends up on the criticality of the data which will be stored within the database.

    For Download :- http://www.undeletepcfiles.com/sql-database-recovery-tool.html/

    ReplyDelete
  8. SQL Server Database Recovery Software which is one of the tremendous software that you never seen anywhere. Its features and its unique technology are amazing. You can easily get back all your damaged SQL Server database files along with its database objects like tables, triggers, functions, stored procedure etc. After that it has the ability to restore corrupt MDF file by creating a separate script file and then it further be exported to SQL Server easily. So prefer this website to read more information: http://www.recoverfilesdata.com/sql-database-mdf-recovery.html


    ReplyDelete
  9. SQL Database Recovery is a program designed to retrieve back almost all database items, such as views, tables, stored procedures, keys, triggers, indexes and deleted records. It has got very user-friendly interface and can be operated by even a novice user.There is no need to know the main reason of how data was corrupt. The task of repair and recovery can be done easily with SQL Database Recovery. Read more :- http://www.recoverfilesdownload.com/sql-database-recovery.html

    ReplyDelete
  10. SQL Server Database Recovery Software which is one of the tremendous software that you never seen anywhere. Its features and its unique technology are amazing. You can easily get back all your damaged SQL Server database files along with its database objects like tables, triggers, functions, stored procedure etc. After that it has the ability to restore corrupt MDF file by creating a separate script file and then it further be exported to SQL Server easily.
    So prefer this website to read more information :- http://www.tools4recovery.com/sql-database-recovery.html

    ReplyDelete