Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Recovering a database that is in the restoring state



By:

Overview

The RESTORE ... WITH RECOVERY option puts the database into a useable state, so users can access a restored database. 

Explanation

When you issue a RESTORE DATABASE or RESTORE LOG command the WITH RECOVERY option is used by default.  This option does not need to be specified for this action to take place.

If you restore a "Full" backup the default setting it to RESTORE WITH RECOVERY, so after the database has been restored it can then be used by your end users.

If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each restore except the last.

If your database is still in the restoring state and you want to recover it without restoring additional backups you can issue a RESTORE DATABASE .. WITH RECOVERY to bring the database online for users to use.


T-SQL

Restore full backup WITH RECOVERY
As mentioned above this option is the default, but you can specify as follows.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH RECOVERY
GO

Recover a database that is in the "restoring" state
The following command will take a database that is in the "restoring" state and make it available for end users.

RESTORE DATABASE AdventureWorks WITH RECOVERY
GO

Restore multiple backups using WITH RECOVERY for last backup
The first restore uses the NORECOVERY option so additional restores can be done.  The second command restores the transaction log and then brings the database online for end user use.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

SQL Server Management Studio

When restoring using SSMS the WITH RECOVERY option is used by default, so there is nothing that needs to be set but this can be set or changed on the options page when restoring.


Last Update: 3/17/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools