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

image1.gif

Step 5: Taking full backup

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

image2.gif
image3.gif

Step 6: INSERT INTO table

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

image4.gif

Step 7: Selecting the data from Test

SELECT *
FROM
   Test 
GO

image5.gif

Step 8: Taking differential backup

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


image6.gif

Step 9: Insert into table

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

image7.gif

Step 10: Taking log backup

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

image8.gif

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

image9.gif

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

image10.gif

  • 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

image11.gif

Step 14: Restore full backup

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

image12.gif

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

image13.gif

Step 15: Restore differential backup

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

image14.gif

Step 16: Selecting the data from Test

SELECT *
FROM testing..Test
GO

image15.gif

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

image16.gif

Step 18: Rolling database forward

RESTORE LOG Testing
WITH RECOVERY
GO

image17.gif

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

image18.gif

SELECT *
FROM testing..Test
GO

  • Clean up database

image19.gif

Step 19:
Clean up

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