[blog] Reply to Tail-Log Backup with Primary Data File Missing (cc @pinaldave; @vinodk_sql)

Let’s Play the Feud

Files and FileGroups with SQL Server

“Quick Quiz: Do you need the primary data file available to backup your transaction log after a crash?”

SQL SERVER – Finding Location of Log File when Primary Datafile is Crashed

“Let us assume that you have corrupted (beyond repairable) or missing MDF. Along with that you have full backup of the database at TimeA. Additionally there has been no backup since TimeA. You have now recovered log file at TimeB. How to get the Database back online with the same state as TimeB?”

Our Survey Said

Backup Overview (SQL Server)

Typically, a log backup succeeds even if one or more data files are unavailable. However, if any file contains bulk-logged changes made under the bulk-logged recovery model, all the files must be online for the backup to succeed.”

Tail-Log Backups

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:
BACKUP LOG database_name TO <backup_device> WITH CONTINUE_AFTER_ERROR
If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

<family feud correct answer bell>

1. Create a database with a primary data file on a USB drive and a transaction log on a DAS disk
2. Add populated tables to the database
3. Take a full backup of the database
4. Drop some of the tables
5. Take the first backup of the transaction log
6. Drop the remaining tables
7. Pull the USB drive
8. Try the second backup of the transaction log (will result in failure)
9. Attach the USB drive
10. Rename the primary data file
11. Restart the SQL Server service
12. Observe the database does not come online
13. Retry the second backup of the transaction log (will result in success)
14. Drop the database
15. Restore the database with norecovery from the full backup
16. Restore the database with standby from the first transaction log backup
17. Observe the database contains some tables
18. Restore the datbase with recovery from the second transaction log backup
19. Observe the database contains no tables

<family feud wrong answer buzzer>

This is one way to create a “database is in a state that supports tail-log backups” and falls within a category best described as “Typically” 🙂  Books online may be a definitive source but its content may not be definitive.  (All comments welcome!)


About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.