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.
Set AdventureWorks backup model to "Full", create a full, a differential and a log backup (always with some writing in between)
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
Check if file is opened on USB drive (rename fails because file is locked), then disconnect USB drive
INSERTsome more data (to prove that it still works and gets restored with the log tail)
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...