[SQL Snacks Video] Tail Log Backup and Recovery Demo

In December 2013 I presented at a SQL Saturday event in Washington DC. My presentation was about Backup and Recovery Fundamentals which I had done before for the PASS DBA Fundamentals Virtual Chapter. This time around, I decided to add a Tail Log (Active Log) Backup and Recovery demo to enhance my Presentation. The presentation went quite well, and so I’ve decided to put a short video together to demonstrate how to do a Tail Log Backup and Recovery.

***Make sure you change the backup, database, and log file paths to match your configuration***

Code for demo below.


----SYNTAX for T-Log Tail Backup
Create Database TaillogTest
GO

USE TailLogTest

Create Table Test1
(column1 int,
column2 varchar(10),
column3 datetime default getdate()
)
GO
----------------------
Insert into Test1 (column1, column2) Values (1,'One')
Insert into Test1 (column1, column2) Values (2,'Two')
GO

---Run FULL Backup
BACKUP Database TailLogTest to Disk = N'C:\MSSQLServer\Backups\TailLogTest_FULL.bak'
GO

Insert into Test1 (column1, column2) Values (3,'Three')
Insert into Test1 (column1, column2) Values (4,'Four')
GO

---RUN TLOG Backup
BACKUP LOG TailLogTest to Disk = N'C:\MSSQLServer\Backups\TailLogTest_TLOG.trn'
GO

Insert into Test1 (column1, column2) Values (5,'Five')
Insert into Test1 (column1, column2) Values (6,'Six')
GO

--Set Database Offline
USE MASTER
ALTER Database TailLogTest SET OFFLINE
---Delete the Datafile from the drive 🙂
---Set the DB Back Online
USE MASTER
ALTER Database TailLogTest SET ONLINE

---Oppssss! Let's get a TailLog Backup before we lose those last two rows we inserted
USE MASTER
Backup LOG TailLogTest
TO DISK = N'C:\MSSQLServer\Backups\TailLogTest_TAILBACKUP.trn'
WITH NO_TRUNCATE

---Let's restore it to another DB and check to see if our data is there

USE Master
RESTORE DATABASE TailLogTest2 FROM DISK = N'C:\MSSQLServer\Backups\TailLogTest_FULL.bak'
WITH FILE = 1,
MOVE N'TailLogTest' TO N'C:\MSSQLServer\Data\TailLogTest2.mdf',
MOVE N'TailLogTest_Log' TO N'C:\MSSQLServer\Logs\TailLogTest2_Log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5
GO
--------------------------------
RESTORE Log TailLogTest2 FROM DISK = N'C:\MSSQLServer\Backups\TailLogTest_TLOG.trn'
WITH NORECOVERY, STATS = 5
GO
--------TAIL LOG RECOVERY------------------------------
RESTORE LOG TailLogTest2 FROM DISK = N'C:\MSSQLServer\Backups\TailLogTest_TAILBACKUP.trn'
WITH STATS = 5

GO

USE TailLogTest2
Select * from Test1

--Clean up
USE MASTER DROP DATABASE TaillogTest
USE MASTER DROP DATABASE TaillogTest2