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:

Solarwinds

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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – A Quick Trick about SQL Server 2012 CONCAT Function – PRINT
Next Post
SQL SERVER – A Simple Puzzle and Simple Solution of Datatype and Computed Column

Related Posts

17 Comments. Leave new

  • 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…

    Reply
  • Is there any other way that we can roll back implicit transaction in SQL SERVER ? with out using log recovery ? Just like Undo !

    Reply
  • 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

    Reply
  • Hi Pinal,

    Thanks for for your valuable script,i reduced my time to backup and restore database.

    Thanks,
    Prasad.

    Reply
  • 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

    Reply
  • Hello Pinal,

    Thank you so much for providing this information. I have learned several new topics from your blog.

    Thanks

    Reply
  • 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.

    Reply
  • Hi pinal,

    i am very happy for the really help for me. your are sql master.this is very use full for me.

    thanks a lot .

    Reply
  • 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.

    Reply
  • Dear Sir I want to know ,

    How to generate outomatically scripts …in sql server

    Reply
  • Really a useful script…

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Hi Pinal,

    What is that standby file name ?

    Thanks.

    Reply

Leave a Reply

Menu