SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore

In this demo we will see two step operations:

1. Set up script and backup database
2. Restore the database in point in time

Let us see each step with simple script:

1. Set up Script and backup database
------------------------------------------------
-- Creating environment
------------------------------------------------
-- Create Database
CREATE DATABASE DEMO
GO
-- Make sure database is in full recovery
ALTER DATABASE DEMO
SET RECOVERY FULL
GO
USE DEMO
GO
-- Create Table
CREATE TABLE TestTable (ID INT)
GO
-- Taking full backup
BACKUP DATABASE [DEMO]
TO DISK = N'D:\DEMO.bak'
GO
INSERT INTO TestTable (ID)
VALUES (1)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (2)
GO
-- Taking log backup
BACKUP LOG [DEMO] TO
DISK = N'D:\DEMO1.trn'
GO
INSERT INTO TestTable (ID)
VALUES (3)
GO
INSERT INTO TestTable (ID)
VALUES (4)
GO
BACKUP LOG [DEMO] TO
DISK = N'D:\DEMO2.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (5)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (6)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (7)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (8)
GO
-- Marking Time Stamp
SELECT GETDATE() BeforeTruncateTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
-- Quick Delay before Truncate
WAITFOR DELAY '00:00:01'
GO
TRUNCATE TABLE TestTable
GO
-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO
-- Marking Time Stamp
SELECT GETDATE() AfterTruncateTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (9)
GO
-- Taking log backup
BACKUP LOG [DEMO] TO
DISK = N'D:\DEMO3.trn'
GO
-- Marking Time Stamp
SELECT GETDATE() CurrentTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
USE MASTER
GO

2. Restore the database in point in time
-----------------------------------------------
-- Restoring Database
------------------------------------------------
USE [master]
GO
-- Taking tail log
BACKUP LOG [DEMO] TO
DISK = N'D:\DEMO5.trn'
WITH NORECOVERY
GO
-- Restore full backup
RESTORE DATABASE [DEMO]
FROM DISK = N'D:\DEMO.bak'
WITH
STANDBY = N'D:\DEMO11.bak'
GO
-- Restore transaction backup
RESTORE LOG [DEMO]
FROM DISK = N'D:\DEMO1.trn'
WITH STANDBY = N'D:\DEMO11.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM DEMO.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [DEMO]
FROM DISK = N'D:\DEMO2.trn'
WITH STANDBY = N'D:\DEMO21.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM DEMO.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [DEMO]
FROM DISK = N'D:\DEMO3.trn'
WITH STOPAT = '2011-12-21 11:12:18.797', -- Insert Your Time
STANDBY = N'D:\DEMO33.trn'
GO
-- Rolling database forward
RESTORE LOG [DEMO]
WITH RECOVERY
GO
-- Selecting the data from TestTable
SELECT *
FROM DEMO.dbo.TestTable
GO