Script for Point in Time Recovery – Back Up and Restore

Here is one of the script which I refer whenever I faced situation about restoring the database at point in time

1.    Set up script and backup database

2.    Restore the database in point in time

3.    Clean up database

Set up Script and backup database

CREATE DATABASE Test
GO

ALTER DATABASE Test
SET RECOVERY FULL
GO
USE Test
GO

CREATE TABLE tblTable (ID INT)
GO
-- Taking full backup
BACKUP DATABASE [Test]
TO DISK = N'D:\Test.bak'
GO
INSERT INTO tblTable (ID)
VALUES (1)
GO

-- Selecting the data from tblTable
SELECT *
FROM tblTable
GO
INSERT INTO tblTable (ID)
VALUES (2)
GO

-- Taking log backup
BACKUP LOG Test] TO
DISK = N'D:\Test1.trn'
GO
INSERT INTO tblTable (ID)
VALUES (3)
GO
INSERT INTO tblTable (ID)
VALUES (4)
GO
BACKUP LOG [Test] TO
DISK = N'D:\Test2.trn'
GO

-- Selecting the data from tblTable
SELECT *
FROM tblTable
GO
-- Quick Delay before Truncate
WAITFOR DELAY '00:00:01'
GO
TRUNCATE TABLE tblTable
GO

-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO

-- Taking log backup
BACKUP LOG [Test] TO
DISK = N'D:\Test3.trn'
GO
-- Marking Time Stamp
SELECT GETDATE() CurrentTime;
-- Selecting the data from tblTable
SELECT *
FROM tblTable
GO
USE MASTER
GO

Restore the database in point in time

-- Restoring Database
USE [master]
GO
-- Taking tail log
BACKUP LOG [Test] TO
DISK = N'D:\Test5.trn'
WITH NORECOVERY
GO
-- Restore full backup
RESTORE DATABASE [Test]
FROM DISK = N'D:\Test.bak'
WITH
STANDBY = N'D:\Test.bak'
GO

-- Restore transaction backup
RESTORE LOG [Test]
FROM DISK = N'D:\Test1.trn'
WITH STANDBY = N'D:\Test11.trn'
GO
-- Selecting the data from tblTable
SELECT *
FROM Test.dbo.tblTable
GO

-- Restore transaction backup
RESTORE LOG [Test]
FROM DISK = N'D:\Test2.trn'
WITH STANDBY = N'D:\Test21.trn'
GO
-- Selecting the data from tblTable
SELECT *
FROM Test.dbo.tblTable
GO
-- Restore transaction backup
RESTORE LOG [Test]
FROM DISK = N'D:\Test3.trn'
WITH STOPAT = '2011-12-21 11:12:18.797', -- Insert Your Time
STANDBY = N'D:\Test33.trn'
GO

-- Rolling database forward
RESTORE LOG [Test]
WITH RECOVERY
GO
-- Selecting the data from TestTable
SELECT *
FROM Test.dbo.tblTest
GO

Clean up database

------------------------------------------------
-- Clean up
------------------------------------------------
USE MASTER
GO
ALTER DATABASE [Test]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [Test]
GO