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

Blogging is like writing a big novel in parts. It has its own mood and it has its own colors. Someday I feel like writing philosophy and some day I like writing theory and some day just a script. Today is one of the day when I just feel like providing working script for user requested frequently. Here is one of the script which I refer whenever I faced situation about restoring the database at point in time.

In this demo we will see three 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).

Watch a 60 second video on this subject

Lots of scripts today but if you are in need of Point in Time Recovery script, this script is for you. I am open for suggestion, enhancements etc. Let me know your comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

19 thoughts on “SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore

  1. Awesome Pinal. This is a great info. I have never done all these things through commands but have done it only through UI. Thank you so much for Your superb lessons SQL Guru…

    Like

  2. Thanks Sir,
    Really informative post for me as I learned some new things from this post about backup and restore.
    Please describe the restore statement of “D:\SQLAuthority3.trn”. After executing this restore command, I got four rows in TestTable having IDs (1,2,3,4). which i don’t understand. because at the time of doing log backup in SQLAuthority3.trn file, TestTable has only one row having ID=9.

    Regards,
    Ashish Jain

    Like

  3. Hi Pinal,

    Do you best thing about your articles, its simply, easy to understand every setup and most import, it’s not a theory. It’s completely based on real life problems which we faced in our day by day work.

    Thanks a lots.

    Vinay Kumar

    Like

  4. hi to all,

    i am happy for the really a basic useful tutorial. most of the time , i got errors and deletes the bak n log files so i got problem my database always in stanb by mode . how to recover database if i loss bak n log files.

    Like

  5. Hi Pinal
    U r a genius man. U r my god. I have improved a lot in database knowledge only because of you. I am a die hard fan of u Pinal.

    Like

  6. Pingback: SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31 « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  9. One of my team-members may have messed up while performing a restore to the point in time i had asked him to. Now he is unavailable and i need to check if the database he restored he used the correct point in time. Is there anyway i can find this information from the SQL Server restore logs. I may end up restoring a multi TB database and cause a lot of disruption to users if this information cannot be determined. Any help will be greatly appreciated.

    Like

  10. Pingback: SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video « SQL Server Journey with SQL Authority

  11. Hi Pinal,

    Could you please let me know, Is it possible to find what was the STOPAT time used in Point in Time restore??

    Thank you Sir.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s