0

Could you tell me the reason why it is mandatory to restore the full backup with NORECOVERY option before restoring the transaction log? otherwise Transaction log is always grayed (Task -> restore -> Transaction log)

Thanks a lot in advanced

migrated from stackoverflow.com Apr 4 '16 at 23:09

This question came from our site for professional and enthusiast programmers.

  • MS SQL database sever – S. Fernando Mar 30 '16 at 6:20
1

If you perform a full RESTORE WITH RECOVERY, that means you want to only restore the full backup and immediately bring the database online.

When you perform a full RESTORE and want to also restore differential or transaction log backups as well, then you want to RESTORE WITH NORECOVERY so that the database does not come online and additional restores are possible.

You can use T-SQL to perform both at the same time (copied from BOL): Restore Transaction Log Backup

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorks2012_1
   WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 1,
   NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 2,
   NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 3,
   NORECOVERY;
GO
RESTORE DATABASE AdventureWorks2012
   WITH RECOVERY;
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.