Script for Point in Time Recovery – Back Up and Restore

Script for Point in Time Recovery – Back Up and Restore

Script which we can refer whenever we face a situation about restoring the database at point in time.

In this demo we will see 3 step operations:

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

Let us see each step with simple script:

1. Set up Script and backup database

------------------------------------------------
-- Creating environment
------------------------------------------------
-- Create Database
CREATE DATABASE SQLAuthority
GO
-- Make sure database is in full recovery
ALTER DATABASE SQLAuthority
SET RECOVERY FULL
GO
USE SQLAuthority
GO
-- Create Table
CREATE TABLE TestTable (ID INT)
GO
-- Taking full backup
BACKUP DATABASE [SQLAuthority]
TO DISK = N'D:\SQLAuthority.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 [SQLAuthority] TO
DISK = N'D:\SQLAuthority1.trn'
GO
INSERT INTO TestTable (ID)
VALUES (3)
GO
INSERT INTO TestTable (ID)
VALUES (4)
GO
BACKUP LOG [SQLAuthority] TO
DISK = N'D:\SQLAuthority2.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 [SQLAuthority] TO
DISK = N'D:\SQLAuthority3.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 [SQLAuthority] TO
DISK = N'D:\SQLAuthority5.trn'
WITH NORECOVERY
GO
-- Restore full backup
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority.bak'
WITH
STANDBY = N'D:\SQLAuthority11.bak'
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority1.trn'
WITH STANDBY = N'D:\SQLAuthority11.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority2.trn'
WITH STANDBY = N'D:\SQLAuthority21.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority3.trn'
WITH STOPAT = '2011-12-21 11:12:18.797', -- Insert Your Time
STANDBY = N'D:\SQLAuthority33.trn'
GO
-- Rolling database forward
RESTORE LOG [SQLAuthority]
WITH RECOVERY
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO


3. Clean up database

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

Few quick points to note: Database has to be either in full recovery or bulk recovery mode. 

While restoring the database it should be done either WITH NORECOVERY (Leave the database non-operational, and do not roll back the uncommitted transactions. 

Additional transaction logs can be restored) or WITH STANDBY (Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted).

We can use this script in need of Point in Time Recovery script.

http://blog.sqlauthority.com/2011/12/23/sql-server-a-quick-script-for-point-in-time-recovery-back-up-and-restore/