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:
go
use db5
go
create table MyLog
( Txt varchar(max)
, LogDate datetime default (getdate())
)
go
insert MyLog select 'No backup', GETDATE()
go
backup database db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_full.bak' with init
go
insert MyLog select 'Full backup complete', GETDATE()
insert MyLog select 'Misc Transaction', GETDATE()
go
backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn' with init
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.
go
alter database db5 set offline
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
go
I get
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.
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\db5_full.bak'
WITH
MOVE N'db5' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7.mdf'
, MOVE N'db5_log' TO N'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
RESTORE log [db7]
FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn'
with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
go
select * From db7.dbo.mylog
go
RESTORE log [db7]
FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn'
with standby = 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.
And don’t forget to set the db active:
