Restoring a Database to a Point Within a Backup

Question: If my database has crashed on Wednesday 04:48 PM, how will you recover the database to 04:40 PM.?

Answer: You may want to restore and recover a database to a specific point in time. The first step is to take a tail log backup of the database if a t-log file is available. Now you will select the last full backup available, then you will select the last differential backup available after the last full backup and then you will restore all t-; log backups from the last differential back until 04:48 PM t-log backup. Then you will restore trail log backups until 04:40 PM using the stop at command. We can use the following process in SQL Server 2005, 2008, 2008r2 and 2012.

While restoring a database to a point within the backup you will see the following four types of operations:

• Set up script and backup database
• Restore the database to a point in time
• Clean up database
• Set up script and backup database

Backup database

Step 1: Create Database

CREATE DATABASE Testing
GO

Step 2: Ensure database is in full recovery

ALTER DATABASE Testing
SET RECOVERY FULL
GO

Step 3: Choose Database

USE Testing 
GO

Step 4: Create Table

CREATE TABLE Test
  (
     ID     INT,
     [Name] CHAR(10)
  ) 
GO



Step 5: Taking full backup

BACKUP DATABASE [Testing] TO DISK = N'D:\backup\Testing.bak'
GO




Step 6: INSERT INTO table

INSERT INTO Test
            (ID,
             [Name])
VALUES      (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E') 
GO



Step 7: Selecting the data from Test

SELECT *
FROM   Test 
GO



Step 8: Taking differential backup

BACKUP DATABASE [Testing] TO DISK = N'D:\backup\Testing1.dif'
WITH DIFFERENTIAL 
GO



Step 9: Insert into table

INSERT INTO Test
            (ID,
             [Name])
VALUES      (6,'F'),(7,'I')



Step 10: Taking log backup

BACKUP LOG [Testing] TO
DISK = N'D:\backup\Testing1.trn'
GO



Step 11: Marking Time Stamp

SELECT GETDATE() BeforeTruncateTime;
-- Selecting the data from Test
SELECT *
FROM Test
GO
-- Quick Delay before Truncate
WAITFOR DELAY '00:00:01'
GO



Step 12: Truncate table

TRUNCATE TABLE Test
GO
-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO
-- Marking Time Stamp
SELECT GETDATE() AfterTruncateTime;
-- Selecting the data from Test
SELECT *
FROM Test
GO

• Restore the database to a point in time

Restore database

Step 13: Choose master database

USE [master]
GO
-- Taking tail log
BACKUP LOG Testing TO
DISK = N'D:\backup\Testing2.trn'
WITH NORECOVERY
GO



Step 14: Restore full backup

RESTORE DATABASE Testing
FROM DISK = N'D:\backup\Testing.bak'
WITH
STANDBY = N'D:\backup\Testing.bak'
GO



You will see the following if your database is in standby (read-only) mode.



Step 15: Restore differential backup

RESTORE DATABASE Testing
FROM DISK = N'D:\backup\Testing1.dif'
WITH
STANDBY = N'D:\backup\Testing1.dif'
GO



Step 16: Selecting the data from Test

SELECT *
FROM testing..Test
GO



Step 17: Restore transaction backup

RESTORE LOG Testing
FROM DISK = N'D:\backup\Testing1.trn'
WITH STOPAT = '2013-09-28 04:56:26.093', -- Insert Your Time
STANDBY = N'D:\backup\Testing1.trn'
GO



Step 18: Rolling database forward

RESTORE LOG Testing
WITH RECOVERY
GO



You will see the following if your database is in recovery mode.



SELECT *
FROM testing..Test
GO

• Clean up database

Step 19: Clean up

USE MASTER
GO
ALTER DATABASE testing
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE testing
GO