Script for Point in Time Recovery – Back Up and Restore

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