I am trying to set up a demo of backing up the tail of the log in case of disk failure, but it doesn't work as expected.

The setup:

  1. Set AdventureWorks backup model to "Full", create a full, a differential and a log backup (always with some writing in between)

  2. Move the only primary data file to a USB drive:

    ALTER DATABASE [AdventureWorks2012] SET OFFLINE

    ALTER DATABASE [AdventureWorks2012] MODIFY FILE ( NAME = AdventureWorks2012_Data, FILENAME = 'E:\AdventureWorks2012_Data.mdf')

    -- Copy file physically here

    ALTER DATABASE [AdventureWorks2012] SET ONLINE

  3. Check if file is opened on USB drive (rename fails because file is locked), then disconnect USB drive

  4. INSERT some more data (to prove that it still works and gets restored with the log tail)

  5. Try tail log backup:

    BACKUP LOG [AdventureWorks2012] TO
    DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH NAME = N'AW Tail Log Backup', NORECOVERY, CONTINUE_AFTER_ERROR

This last step fails with the following message:

Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.

Msg 823, Level 24, State 3, Line 1 The operating system returned error 21(Das Gerät ist nicht bereit.) to SQL Server during a write at offset 0x00000002860000 in file 'E:\AdventureWorks2012_Data.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

So why does SQL Server insist on writing to the data file during a tail log backup where the whole point is that you only need the log file for this? Have I missed a backup parameter or some obscure trace flag?

BTW: I can still do a log backup if I use NO_TRUNCATE as the only parameter, but that's no real tail backup because it lacks the NORECOVERYportion and thus still allows users to change data after that. If I add NORECOVERY to that, it fails again with the same message as above. If I try to restore with the NO_TRUNCATE log it complains that I have to take a tail log backup first...

  • why does SQL Server insist on writing to the data file during a tail log backup where the whole point is that you only need the log file for this? Does the un-backed-up log contain any minimally logged operations as it would require reading data extents - which cannot be done if the data files are damaged.
    – Kin Shah
    Aug 29 '13 at 14:57
  • I understand that you moved the MDF to a USB drive and then updated some data. I am pretty sure that a USB drive does not measure up to what SQL Server is expecting from a drive. Could you try your process by moving the MDF to a different folder on your computer and seeing if the process works there?
    – RLF
    Aug 29 '13 at 15:12
  • @Kin: No, as stated I use the 'full' option and the were just normal inserts anyway.
    – TToni
    Aug 29 '13 at 15:54
  • @RLF: I could, but don't see the point. The USB uses NTFS, so apart from probably not supporting the correct write cache modes it looks just like another hard drive.
    – TToni
    Aug 29 '13 at 15:57
  • @RLF the point of the USB drive was so he could simulate the drive with the data file going bad. Kimberly Tripp used the same technique in one of her MCM videos. Aug 29 '13 at 15:59

Paul Randal did a good piece on backing up the tail of the log that includes just this case. In it he says that the method to use when there is no data file is using NO_TRUNCATE. Based on that your command should be:

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 

BOL does say

To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

So you could try using both of them together but I'm not sure if NORECOVERY can be used if the data file is missing or not, even with NO_RECOVERY.

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 

It's certainly worth testing anyway.

  • OK, so the confusion in my example seems to come from the point that the DB is still online. I first have to prevent users from doing more updates by setting it offline, then I can take the log backup with NO_TRUNCATE and then basically delete the database and restore everything (including the last user transactions while the file was already gone - which was the whole point of the exercise). I'll try that.
    – TToni
    Aug 29 '13 at 16:02
  • In theory you should be able to do a NO_TRUNCATE, NORECOVERY at the same time. (messed up my example, I'll fix it). You might find Kimberly Tripp's MCM video on Partial Database Availability & Online Piecemeal Restore very interesting. Aug 29 '13 at 18:33
  • I already mentioned in my last paragraph that NO_TRUNCATE, NORECOVERY produces the original error again. I know these videos, I'm preparing for the MCM lab exam right now, that's how I got the idea in the first place :-)
    – TToni
    Aug 29 '13 at 20:26
  • The link to Pauls blog brought me on the right track, so I'm going to accept this as answer. Thanks Kenneth!
    – TToni
    Aug 29 '13 at 20:31
  • Good luck on the MCM! I'm studying for my 2012 MCSA/MCSE upgrade tests now. I should probably give this a shot one of these days myself. Aug 29 '13 at 21:27

Sorry @Kenneth, but I found out some more things, which demand a more complete answer, so I have to answer myself :-)

My main issue was that although the database file is damaged/nonexistent any more, users can still connect to the DB and modify it. So if I just take a log backup with NO_TRUNCATE, the DB might be modified during and after that backup. If you do a "normal" tail log backup you prevent this behaviour by using NORECOVERY instead of NO_TRUNCATE which leaves the DB in "Restoring..." state.

However, as posted in my question, that doesn't work if the DB is sufficiently damaged.

The solution I finally found is like this:

  1. First, set the DB to SINGLE_USER mode, preventing connections from other users. EMERGENCY and OFFLINE don't work, because they don't let you take backups in the damaged state.

  2. Then take a log backup with NO_TRUNCATE as the only option.

  3. When this is done, you can start a restore process including that last backup

  4. Since the DB was in single user mode at the time of the last backup, the restore will set it to single user again. So the final step is to set MULTI_USER to make it accessible for the users again.

  • BOL does say If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. so you could try the SUSPENDED state. You might also try the SUSPECT state as BOL only mentions that OFFLINE and EMERGENCY don't allow NO_TRUNCATE backups. Aug 30 '13 at 12:02
  • @KennethFisher "SUSPENDED" refers to a mirroring state, which doesn't apply here. The normal states are listed here technet.microsoft.com/en-us/library/ms190442.aspx, the mirroring states here technet.microsoft.com/en-us/library/ms189284.aspx. And the states available through "ALTER DATABASE" are only "ONLINE, OFFLINE and EMERGENCY" in combination with "SINGLE_USER, MULTI_USER and RESTRICTED_USER"
    – TToni
    Aug 30 '13 at 12:15

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.