I’ve never had to do this in production, and I’ve only practiced it a few times, but I think this is a core DBA skill. Along with being able to backup and restore your databases, you should be able to recover to a point in time. That can mean a tail log backup.
I read this in Paul Randal’s blog recently and decided to practice it. So I created my own new database and added a few transactions:
createdatabase db5 go use db5 go createtable MyLog ( Txt varchar(max) , LogDate datetimedefault (getdate()) ) go insert MyLog select'No backup',GETDATE() go backupdatabase db5 todisk='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_full.bak'withinit go insert MyLog select'Full backup complete',GETDATE() insert MyLog select'Misc Transaction',GETDATE() go backuplog db5 todisk='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn'withinit go insert MyLog select'Log Backup Complete',GETDATE() go
I’ve added a couple backups here. If I were to restore this full, I ought to have 1 row in this table. If I add the log restore, I’ll have 3, but I’ll be missing the last line that says “Log Backup Complete”. Now I’ll wreck the database, as per Paul Randal.
usemaster go alterdatabase db5 setoffline go
I then rename the mdf file from db5.mdf to db5xxx.mdf, essentially “deleting” it from view by the SQL Server service. When I set this db online
alterdatabase db5 setonline go
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db5.mdf". Operating system error 2: "2(The system cannot find the file specified.)". Msg 945, Level 14, State 2, Line 1 Database 'db5' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
As expected, we have a problem. How do I backup the tail of the log? Remember that file is still visible. We’ll use Paul’s trick to add NO_TRUNCATE to the command:
I get a successful backup, so let’s test. Here’s my restore script, restoring this db as a new database on this instance.
RESTOREDATABASE [db7] FROM DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\db5_full.bak' WITH MOVEN'db5'TON'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7.mdf' ,MOVEN'db5_log'TON'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7_1.LDF' ,STANDBY=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7.BAK' ,NOUNLOAD,STATS= 10 GO select*From db7.dbo.mylog go RESTORElog [db7] FROMdisk='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn' withstandby=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK' go select*From db7.dbo.mylog go RESTORElog [db7] FROMdisk='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn' withstandby=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK' go select*From db7.dbo.mylog
When I go through this, I get result sets of 1, 3, and 4 rows respectively. I see all my inserts, so despite having a corrupted, destroyed, or renamed (in my case) MDF, I can get all my data back with the log.
Learn and practice a tail log restore. It’s worth it.