Sql-server – SQL Server tail log backup fails where it shouldn’t

sql serversql-server-2012

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…

Best Answer

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' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE

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' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE, NORECOVERY

It's certainly worth testing anyway.

Related Question