Skip to content

Core DBA Skill – Backing up the tail log

July 14, 2010

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:

create database db5
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.

use master
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

alter database db5 set online
go

I get

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:

backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn' with init, no_truncate

I get a successful backup, so let’s test. Here’s my restore script, restoring this db as a new database on this instance.

RESTORE DATABASE [db7]
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:

restore database db7 with recovery

About these ads

From → Blog

One Comment

Trackbacks & Pingbacks

  1. Two Types of Tail Log Backups « Voice of the DBA

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,314 other followers

%d bloggers like this: