It’s September 2020, and it’s time for T-SQL Tuesday. Our host, this time, is Elizabeth Noble (b|t). And her theme of choice is automation. Not automating the work of others, our day to day job, but automating our own work. To limit the risk of error, to reduce the stress, and simply to avoid boredom from doing the same thing over and over again.

I don’t really automate many of my tasks. My work focuses more on writing and optimizing tuning, and less on database administration. That means I do less repetitive tasks than many DBAs do. That said, one of my customers has me as their only database person and expects me to do DBA tasks as well. With the limited amount of instances they run, I’ve never felt a need to invest time to learn Powershell, or to get into using the excellent dbatools module.

But there are still things I do automate. Let’s talk about one of them.

Restore to test

Some time ago, a customer was working on a project where they regularly had to ask me to restore one or more test databases from a copy of the production database. This was good for me, because it gave me some much needed practice with the restore process. Always good to have, in case production goes down and the entire management team is pinging me every minute to know when the database will be available again.

But at one point I had enough practice and got bored of going through the motions over and over again. So I decided I had to automate it.

There’s always a script. Right?

Now I’ve been to quite a few conferences. And I’ve heard a lot of DBAs talk about best practices. One of the constants in those talks is: automate your restores. So I felt confident that a quick internet search would surely be enough to find me an existing script for restoring a database. Sure, I’d need to modify it to restore to a test database, but that should be minimal effort.

To my surprise, I was unable to find a script. Is scripting this so easy that every DBA can do it with their eyes closed, and nobody feels a need to share it? Is it so hard that they all decided they’re sitting on gold and will only share it for big money? Or were my internet search skills simply severely lacking?

Anyway, bottom line is that I had to do it myself. And I’ll share the result in this post.

Intended purpose

I wrote this script specifically for the intended purpose: to restore a backup of a database to another location. Probably another instance, perhaps a different database name, and most likely with different file names. This means that the script does a lot of things that would not be needed in a script that is intended for restoring a database from its own backup.

For the same reason, I have also excluded some actions that should definitely be done when you need to restore a production database from its own backup. Most importantly, the creation of a tail-log backup.

So if you come here looking for a script to restore a database from its own backup, then feel free to use my script as a starting point but be aware that you will need to add some things, and might want to remove some other things.

Explain your work

I always tell people to never trust scripts they find on the internet. They can be a good starting point. But always check to make sure they are correct for your situation. And that, of course, requires understanding what the script does. If you don’t understand code found on the internet, don’t use it!

In order to ensure that everyone reading this will indeed first go through my script and ensure they understand it, I’ll now walk you through the important parts.

Section 1

The script is intended to restore the database on a different instance than where the source database resides. I think (though untested!) that it should work unchanged when both databases are on the same server, but it’s written for the more generic use case.

The script should be executed on the instance where you want the new database to be restored. But it does need access to data on the source instance. In order to easily access that data without having to use a big steaming heap of dynamic SQL, I create a linked server in this section. The linked server points to the instance of the source database.

You’ll need to provide the name of the source instance in the @Server_OLD variable at the start of this section.

This section is in a separate batch because the rest of the code requires that the “SourceSRV” linked server exists when the code is compiled.

Section 2

The next section of the script is where the real action happens. You’ll need to provide some data in the variables declared at the top; the comments should explain what is needed. I have added support for databases with up to two data files and one single log file, but it is really easy to add support for more logical files.

It is important to point out that in the rest of this section, dynamic SQL needs to be used. I have parameterized code where possible, but not all elements of the RESTORE statement support parameters. This means that this script does open you up for possible SQL injections. Since this script is intended to be used by privileged users only, that should be a fairly minimal risk. But please, do NOT create a stored procedure for this code and expose it to end users through some friendly interface. Unless you really enjoy making headlines in the news and updating your resume.

Section 2, step 1

The first step starts by looking in the msdb database on the source server to find the most recent full backup that was made before the requested point in time for the restore. It then builds a dynamic SQL statement to restore a new database on the target server (where the script should be executed) from that backup.

The customer where I used this stores all database backups on network shares that are visible to all database servers. That enabled this code to work as written. If backups are stored locally, then you’ll need to manually copy all (possibly) required backup files to the destination server first (for the full backup, and for backups used in the other steps). You’ll also need to modify the script with some REPLACE logic to ensure that the folder name of the backup file is changed.

This first step concludes with actually executing the restore statement it created. The restore statement used the WITH NORECOVERY option because the other steps will build and execute additional restore statements.

Section 2, step 2

In the second step, the script again looks in the msdb database on the source server. It now finds the most recent differential backup that was made before the requested point in time for the restore, but after the full backup it has just restored.

If the backup schedule of the database doesn’t use differential backups, or if there was no differential backup made between the last full backup and the target point in time, then this step does nothing. If one or more differential backups do exist, it will now build and then execute the SQL needed to restore the last of those differential backups. Again with NORECOVERY, to enable further restores in the next step. And just as in step 1, you might need to copy files first and do some string manipulation to get the correct pathname for the backup file if they are not on a network share that is visible from both servers.

Section 2, steps 3 and 4

The main part of the script ends with a loop (using a cursor, with all the options to get the best possible performance a cursor can give) over all the log backups that, according to the msdb database on the source server, are made after the time of either the differential backup restored in step 2 or the full backup restored in step 1. These are processed in chronological order.

For each of these log backups, the appropriate restore statement is once more built and executed. Here, once more, the same remarks about the NORECOVERY option and about copying the backup file and modifying the path may apply.

The loop ends either after restoring the last log backup made on the source database, or after restoring a log backup that was taken after the requested point in time for the restore.

After ending the loop and cleaning up the cursor objects, one final restore statement is executed. This restore statement does not restore any actual database file. It merely instructs SQL Server to perform recovery and make the database available for normal use.

Section 3

This section really needs no comment. We created a temporary linked server in step 1. I have rather strong feelings about scripts that create “temporary” objects and then don’t clean them up when finished.

The script

After all the explanation, I’ll simply conclude this post by sharing the actual script. Enjoy!

USE master;
GO

SET NOCOUNT ON;
GO

-- SECTION 1: Create a linked server
-- One of the constants to be set before running the script [needs to be in its own batch!]
DECLARE @Server_OLD sysname = '<<must be provided>>'; -- Instance where source database is

IF EXISTS (SELECT * FROM sys.servers WHERE name = 'SourceSRV')
BEGIN;
    EXEC sys.sp_dropserver 'SourceSRV';
END;

-- Create temporary linked server name to point to source server. (This prevents a lot of dynamic SQL!)
EXEC sys.sp_addlinkedserver 'SourceSRV', '', 'SQLNCLI', @Server_OLD;
GO

-- SECTION 2: Restore a copy of the database, up to the requested point in time
-- Constants to be set before running the script
DECLARE @DB_name_OLD    sysname  = '<<must be provided>>', -- Name of source database to copy
        @DB_name_NEW    sysname  = '<<must be provided>>', -- Name of destination database to create
        @DataFileName1  sysname  = '<<must be provided>>', -- Logical filename of primary data file (must match original)
        @DataFilePath1  sysname  = '<<must be provided>>', -- Path and filename where to create primary data file
        @DataFileName2  sysname  = NULL,                   -- Logical filename of secondary data file (must match original) - use NULL for n/a
        @DataFilePath2  sysname  = NULL,                   -- Path and filename where to create secondary data file
        @LogFileName    sysname  = '<<must be provided>>', -- Logical filename of log file (must match original)
        @LogFilePath    sysname  = '<<must be provided>>', -- Path and filename where to create log file
        @recovery_point datetime = '<<must be provided>>'; -- Target date and time for the point-in-time recovery (please use yyyy-mm-ddThh:mm:ss notation)

-- Variables used in the script
DECLARE @sql                  nvarchar(MAX),
        @physical_device_name nvarchar(260),
        @full_backup_time     datetime,
        @diff_backup_time     datetime,
        @last_log_time        datetime,
        @message              nvarchar(MAX);


-- Step 1: Find last full backup before recovery point, then restore it
SET @sql = N'RESTORE DATABASE @DB_name_NEW
FROM DISK = @physical_device_name
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @recovery_point,
     MOVE N''' + @DataFileName1 + N''' TO N''' + @DataFilePath1 + N''','
           + COALESCE('
     MOVE N''' + @DataFileName2 + ''' TO N''' + @DataFilePath2 + ''',', '')
           + N'
     MOVE N''' + @LogFileName + N''' TO N''' + @LogFilePath + N''';';

SELECT     TOP (1) @physical_device_name = bmf.physical_device_name,
                   @full_backup_time     = bs.backup_start_date
FROM       SourceSRV.msdb.dbo.backupset         AS bs
INNER JOIN SourceSRV.msdb.dbo.backupmediafamily AS bmf
   ON      bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DB_name_OLD
AND        bs.type                 = 'D'
AND        bs.backup_start_date    < @recovery_point
ORDER BY   bs.backup_start_date DESC;

SET @message = N'Starting restore of full backup file '
               + @physical_device_name + N', taken '
               + CONVERT(nvarchar(30), @full_backup_time, 120);
RAISERROR(@message, 0, 1) WITH NOWAIT;
EXEC sys.sp_executesql @sql,
                       N'@DB_name_NEW sysname, @physical_device_name nvarchar(260), @recovery_point datetime',
                       @DB_name_NEW,
                       @physical_device_name,
                       @recovery_point;


-- Step 2: Find last differential backup before recovery point, then restore it
SET @sql = N'RESTORE DATABASE @DB_name_NEW
FROM DISK = @physical_device_name
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @recovery_point;';

SELECT     TOP (1) @physical_device_name = bmf.physical_device_name,
                   @diff_backup_time     = bs.backup_start_date
FROM       SourceSRV.msdb.dbo.backupset         AS bs
INNER JOIN SourceSRV.msdb.dbo.backupmediafamily AS bmf
   ON      bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DB_name_OLD
AND        bs.type                 = 'I'
AND        bs.backup_start_date    >= @full_backup_time
AND        bs.backup_start_date    < @recovery_point
ORDER BY   bs.backup_start_date DESC;

IF @@ROWCOUNT > 0
BEGIN;
    SET @message = N'Starting restore of differential backup file '
                   + @physical_device_name + N', taken '
                   + CONVERT(nvarchar(30), @diff_backup_time, 120);
    RAISERROR(@message, 0, 1) WITH NOWAIT;
    EXEC sys.sp_executesql @sql,
                           N'@DB_name_NEW sysname, @physical_device_name nvarchar(260), @recovery_point datetime',
                           @DB_name_NEW,
                           @physical_device_name,
                           @recovery_point;
END;


-- Step 3: Find all log backups taken after the just-restored differential or full backup; restore them until we are past the recovery point
SET @sql = N'RESTORE LOG @DB_name_NEW
FROM DISK = @physical_device_name
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @recovery_point;';

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY TYPE_WARNING FOR
SELECT     bmf.physical_device_name,
           bs.backup_start_date
FROM       SourceSRV.msdb.dbo.backupset         AS bs
INNER JOIN SourceSRV.msdb.dbo.backupmediafamily AS bmf
   ON      bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DB_name_OLD
AND        bs.type                 = 'L'
AND        bs.backup_start_date    >= COALESCE(@diff_backup_time, @full_backup_time)
ORDER BY   bs.backup_start_date ASC;

OPEN c;

FETCH NEXT FROM c
INTO @physical_device_name,
     @last_log_time;

WHILE @@FETCH_STATUS = 0
BEGIN;
    SET @message = N'Starting restore of log backup file '
                   + @physical_device_name + N', taken '
                   + CONVERT(nvarchar(30), @last_log_time, 120);
    RAISERROR(@message, 0, 1) WITH NOWAIT;
    EXEC sys.sp_executesql @sql,
                           N'@DB_name_NEW sysname, @physical_device_name nvarchar(260), @recovery_point datetime',
                           @DB_name_NEW,
                           @physical_device_name,
                           @recovery_point;

    IF @last_log_time > @recovery_point
        BREAK;

    FETCH NEXT FROM c
    INTO @physical_device_name,
         @last_log_time;
END;

CLOSE c;
DEALLOCATE c;


-- Step 4: Perform recovery
SET @sql = N'RESTORE DATABASE @DB_name_NEW
WITH RECOVERY;';

RAISERROR('Starting recovery', 0, 1) WITH NOWAIT;
EXEC sys.sp_executesql @sql,
                       N'@DB_name_NEW sysname, @physical_device_name nvarchar(260), @recovery_point datetime',
                       @DB_name_NEW,
                       @physical_device_name,
                       @recovery_point;
GO

-- SECTION 3: Remove the temporary linked server that we created earlier
EXEC sys.sp_dropserver 'SourceSRV';
GO

Enjoy!

(And remember: test, test, and test again, before using this on really important data)