Restore to a point in time–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

One of the things I had to show recently was a restore to a point in time, and I needed to actually lookup syntax. That’s a good excuse for a #SQLNewBlogger post, so here we go.

When you restore, the default is to restore to the end of the backup file, whether full, diff, or log. In the latter, you have the option to stop early, and only restore part of the log. In doing this, you have two choices:

  • restore to a time
  • restore to a marked transaction

Relatively few of us use marked transactions, but they can be useful. However, this post looks at time.

Let’s do a few things and show how this works.


The first thing I need to do is get a database, with some data. I’ll create a database, add a table with a row, and then back up the database.

USE RestoreTime
, logmessage VARCHAR(200)
-- add a message
INSERT dbo.BackupLog
        ( logdate, logmessage )
    VALUES ( SYSDATETIME(), '1st Log, before a backup' )
BACKUP DATABASE RestoreTime TO disk = 'RestoreTime.bak'

Next we want to add some data to the table and get some log records. In this case, I’ll use a simple loop to add a new row to the table every second. This gives me some points in time to look for and use to show I  am restoring to a point in time.

DECLARE @seconds INT = 10,
        @starttime DATETIME2 = SYSDATETIME();

WHILE (SYSDATETIME() < DATEADD( SECOND, @seconds, @starttime))
  INSERT dbo.BackupLog
        ( logdate, logmessage )
    VALUES ( SYSDATETIME(), 'Log record entered at ' + CAST(SYSDATETIME() AS VARCHAR(30)) )
  WAITFOR DELAY '00:00:01'

BACKUP LOG RestoreTime TO DISK = 'RestoreTime.trn'

I’ve added data, and my table looks like this:

2017-05-23 15_23_12-SQLQuery8.sql - (local)_SQL2016.RestoreTime (PLATO_Steve (57))_ - Microsoft SQL

My log backup contains all these records. If I restore the full backup and log now, by default I’ll end up in this state, with all these rows in my table. However, what if I want to only get the first 5?

I can use the STOPAT syntax in my restore. I start by restoring the full with NORECOVERY and then the log. However, in the log, I’ll choose a time that is after row 5, but before 6. In this case, that’s 2017-05-23T15:22:57. Here’s the code:

USE master
RESTORE DATABASE RestoreTime FROM DISK = 'RestoreTime.bak' WITH NORECOVERY, replace
RESTORE LOG RestoreTime FROM DISK = 'RestoreTime.trn' WITH RECOVERY, STOPAT = '2017-05-23T15:22:57'
USE RestoreTime
 FROM dbo.BackupLog

I have my results below

2017-05-23 15_31_10-SQLQuery8.sql - (local)_SQL2016.RestoreTime (PLATO_Steve (57))_ - Microsoft SQL

Only the log activity before this time is restored.


After I’d looked up the syntax, I spent only about 10 minutes setting up the demo and getting this ready. Practice skills and write about it. Show your next employer you are always learning and working.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

2 Responses to Restore to a point in time–#SQLNewBlogger

  1. A Mk says:

    Good, Thanks for info.


Comments are closed.