2

Assume that my mdf file is deleted and I set my database into single user mode. The first thing that I do is take tail log backup. My question is do I need to add NORECOVERY option while taking tail log backup? if so, then why? My point of view is if my db is set into single user mode which means that no one except me accessing database, then I can omit writing NORECOVERY as the main function of this keyword is to stop the subsequent transactions.

I would like ask the same question about NO_TRUNCATE keyword. As I know, when we take t-log backup (in full recovery model) we actually truncate t-log file and by writing NO_TRUNCATE keyword, SQL Server will not truncate t-log file after backup. Why do I need to keep that inactive part when I have already taken tail-log backup successfully?

Improve this question

1 Answer 1

Reset to default
2

According to Tail-Log Backups (SQL Server) - (highlighting mine)

  • If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the ... WITH NORECOVERY option of the BACKUP Transact-SQL statement.
  • If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no transactions can occur at this time, using the WITH NORECOVERY is optional.

According to SQL Server expert Paul Randal regarding Disaster recovery 101: backing up the tail of the log

Use the NO_TRUNCATE option, which allows the log backup even if the database files aren’t there

In your case, the required database files do not exist which would require you to use NO_TRUNCATE

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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