RSS

[SQL Snacks Video] Tail Log Backup and Recovery Demo

16 Jan
[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

Advertisements
 

Tags: , , , , , , ,

4 responses to “[SQL Snacks Video] Tail Log Backup and Recovery Demo

  1. Paul H

    March 12, 2014 at 7:08 AM

    Thanks very much. An excellent and easy to understand demo.

     
    • Ayman El-Ghazali

      March 12, 2014 at 7:15 AM

      I’m glad I could teach someone something useful 🙂

       
  2. Jitendra Mohanty

    May 2, 2015 at 2:44 PM

    Very nice and use full video. Short and sweet

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: