Simple Point in Time Database Restores

A few weeks ago I demonstrated the simplicity of performing point-in-time database restored in Azure Managed Instance. Whilst that has a lovely front-end, it can be just as easy with a proc call on your traditional SQL instance.

Let’s see how to use two open source tools to achieve this simplicity.

Back it up

Before we get to the restores, we want to look at how we’re backing up our databases. Ola’s backup and maintenance scripts are some of the most trusted and configurable openly available scripts available for SQL Server. These will comfortably handle our backups.

Once you’ve got them downloaded and installed we can set up some jobs to take the backups we need.

For this example we’ll have simple SQL Agent jobs taking Full, Differential, and Log backups. For example, the full backup script looks like this:

EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'C:\SqlBackup\',
    @BackupType = 'Full',
    @DirectoryStructure = '{DatabaseName}\{BackupType}',
    @FileName = '{DatabaseName}_{BackupType}_{Year}{Month}{Day}_{Hour}{Minute}{Second}.{FileExtension}'

These are some core options for what we need, plus I’ve customised the file structure to simplify naming. There are other schedules where the @BackupType parameter is changed from FULL to DIFF and LOG.

There’s a large selection of other options you can see in the online documentation which you might want to look into.

With the schedules in place we’re collecting a full backup daily at 12:00, differential backups at 10:00 and 15:00, and log backups every hour on the half hour (09:30, 10:30, etc.).

Point in time restore

When performing a RESTORE of a transaction log we can request it to stop at a specific point in time using STOPAT in the script, as demonstrated in the documentation:

RESTORE LOG AdventureWorks2022
    FROM AdventureWorksBackups
    WITH FILE = 4, 
        NORECOVERY,
        STOPAT = 'Apr 15, 2020 12:00 AM';

That’s all well and good, but we still need to find the right log files to restore. We can do better.

Rather than needing to select the latest full backup, differential, and subsequent transaction logs ourselves, we’ll again turn to a tool from the community to simplify the restore. This time it comes from the First Responder Kit.

As per the GitHub page, the sp_DatabaseRestore script in the toolkit “helps you rapidly restore a database to the most recent point in time”, specifically when backing up with Ola’s script we used above.

A restore of the database looks like this:

EXEC dbo.sp_DatabaseRestore
    @Database = 'AdventureWorks2022',
	@RestoreDatabaseName = 'AdventureWorks2022-Restore',
	@BackupPathFull = 'C:\SqlBackup\AdventureWorks2022\FULL',
	@BackupPathDiff = 'C:\SqlBackup\AdventureWorks2022\DIFF',
	@BackupPathLog = 'C:\SqlBackup\AdventureWorks2022\LOG',
	@RestoreDiff = 1,           /* Use differential backups where available */
	@RunRecovery = 1            /* Make the database available after restore */

This one call will recover the database to the latest point available by identifying the appropriate full, differential, and log backups from the specified paths, and restoring them all.

If you’ve ever had to trawl through folders manually and script your restores manually, this in itself is a game-changer.

We’re going further though by adding another parameter to this which will provide our point in time restore. The @StopAt parameter takes a date and time stamp in the format yyyyMMddHHmmss, for example we’ll use 16:00 on 14th February:

	@StopAt = '20250214160000'

Alternatively if you prefer to restore based on a DATETIME variable you could format the timestamp within your script:

DECLARE @RestoreTo DATETIME = '2025-02-14 16:00:00';
DECLARE @RestoreToStr VARCHAR(14) = FORMAT(@RestoreTo, 'yyyyMMddHHmmss');

EXEC dbo.sp_DatabaseRestore
    ...
    @StopAt = @RestoreToStr;

The restore will again identify the latest appropriate full, differential and log files to be restored, but now for the specific point in time.

By adding the parameter @Execute = 'N' onto the call, we can see the commands to be ran but without performing the restore. For the point-in-time example we see:

RESTORE DATABASE [AdventureWorks2022-Restore] FROM DISK = 'C:\SqlBackup\AdventureWorks2022\FULL\AdventureWorks2022_FULL_20250214_120001.bak' WITH NORECOVERY, REPLACE, MOVE 'AdventureWorks2022' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\AdventureWorks2022-Restore.mdf', MOVE 'AdventureWorks2022_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\AdventureWorks2022-Restore_log.ldf'
RESTORE DATABASE [AdventureWorks2022-Restore] FROM DISK = 'C:\SqlBackup\AdventureWorks2022\DIFF\AdventureWorks2022_DIFF_20250214_150001.bak' WITH NORECOVERY, MOVE 'AdventureWorks2022' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\AdventureWorks2022-Restore.mdf', MOVE 'AdventureWorks2022_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\AdventureWorks2022-Restore_log.ldf'
RESTORE LOG [AdventureWorks2022-Restore] FROM DISK = 'C:\SqlBackup\AdventureWorks2022\LOG\AdventureWorks2022_LOG_20250214_153001.trn' WITH NORECOVERY, STOPAT = '20250214 16:00:00'
RESTORE LOG [AdventureWorks2022-Restore] FROM DISK = 'C:\SqlBackup\AdventureWorks2022\LOG\AdventureWorks2022_LOG_20250214_163113.trn' WITH NORECOVERY, STOPAT = '20250214 16:00:00'
RESTORE DATABASE [AdventureWorks2022-Restore] WITH RECOVERY

This shows the full backup from noon on 14th Feb is used as that is the latest prior to specified restore point. The same is true for the differential. We then see each of the logs being restored with the STOPAT specified which we saw above.

We can see that the final log file applied will be after the restore point as it will need to replay through up to the specified time.

Wrap up

Here we’ve looked at how its just as easy to restore a copy of your database to a specific point in time nearly as simply as in a Managed Instance through the use of two fantastic community tools.

We’ve seen how the DatabaseBackup procedure from Ola’s maintenance solution can be used alongside the sp_DatabaseRestore procedure from the First Responder Kit to efficiently achieve point in time restores.

The combination of these not only provides a simple solution for point in time restores, but also a very strong baseline for backups and restores, particularly for the accidental DBAs who are getting started, or smaller organisations where premium solutions aren’t an option.

It’s worth noting that both of these community tools have a vast amount of options available to configure them for your particular needs. I’d certainly recommend reviewing the documentation for both if you aren’t familiar with either of them.