SQL Server Tail Log Backup Demo

A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the LSN intact. Before we can recover a SQL Server database to its latest point in time, we must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.


1. Create a TEST database

.CREATE DATABASE [TEST] ON  PRIMARY
( NAME = N'TEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\TEST.mdf',SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\TEST_log.ldf',SIZE = 1024KB , FILEGROWTH = 10%)

GO

ALTER DATABASE [TEST] SET COMPATIBILITY_LEVEL = 100
 GO

change the database owner to 'SA"
Use TEST
GO
sp_changedbowner 'sa'

2.Create a table in the database:

Use TEST
GO

Create table Employee
(
EMP_NO int,
EMP_FirstName varchar(30)
)


insert few records into table
Insert into Employee values(1,'Rajesh')
go
Insert into Employee values(2,'Bharat')
go

3. Perform full Backup of TEST database

Backup database TEST to disk='D:\TEST_FULL.bak' with stats=10,compression

4. Insert few more records in the table

Use TEST
GO
Insert into Employee values(3,'Ravi')
go
Insert into Employee values(4,'Tarun')

5. Perform T-log Backup of TEST database
Use master
go
Backup Log TEST to disk='D:\TEST_TLOG.trn'
go

6. insert few more records into EMPLOYEE table

Use TEST
GO
Insert into Employee values(5,'Santosh')
go
Insert into Employee values(6,'Srikanta')

7. Find out the location of data file of TEST database.
Use master
go
Select * from sys.master_files where name='TEST'

8. Offline the TEST database

Use Master
Go

alter database TEST set offline

9. Delete the data file from Drive to simulate the database crash.

10. Try to Bring the database online but we cannot bring it online as no data file will found.

Use master
Go
alter database TEST set online

11. Perform the Tail log backup of current log file.

Option 1: In case the database is offline and unable to start,use NO_TRUNCATE :
Backup Log TEST to disk='D:\TEST_TailBackup.trn' with No_Truncate

Option 2 :If a database is damaged, you should try to make a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option in the BACKUP statement. CHECKSUM is also an option which works well in this situation.

Backup Log TEST to disk='D:\TEST_TailBackupDBDamage.trn'with checksum,CONTINUE_AFTER_ERROR
If tail-log cannot be performed, any transactions commited after the latest log backup will be lost.

12. Find-out the Logical name using Restore FileListonly clause.
Restore filelistonly from  disk='D:\TEST_Full.bak'

13. Restore database with Different name from FULL backup with Norecovery option So Subsequent backups can be applied.

Restore database TEST1 from disk='D:\TEST_Full.bak'
with FILE=1,
Move 'TEST'to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\TEST1.mdf',
move 'TEST_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\TEST1_log.ldf',
NORecovery,Stats=10

14. Restore T-log backup With Norecovery clause.

Restore database TEST1 from Disk='D:\TEST_TLOG.trn' with Norecovery, stats=10

15. Apply the Tail Log backup with Recovery option so database can be online and functional.

Restore database TEST1 from Disk='D:\TEST_TailBackup.trn' with Recovery, stats=10

16. Fetch records to check if all the records are there in the table

Use TEST1
Go

select * from Employee

• Tweet
• Share
• Share
• Share
• Share