Category:
Database
Key Benefits of the Automated Point-in-Time Restore Solution
- Automated Restore Scripting
- Handles Complex Backups
- One-Click Execution
- Written by Gé Brander
1. The Challenge
When you need to restore a database, you often must manually piece together several backup files. Typically, you have to:
- Restore a full backup: This is the foundation.
- Apply a differential backup (if available): Captures changes since the full backup.
- Apply log backups: Bring the database to the desired point in time.
If your full backup is split across multiple files—or if your database has multiple filegroups—manually creating the proper MOVE clauses (to map logical file names to new physical file paths) can be tedious and errorprone. Mistakes here can lead to restore failures and prolonged downtime.
2. The Modular Approach
The solution is divided into three modules, each handling a specific part of the process:
- GetSQLVersionPrefix Function
This scalar function retrieves the SQL Server version prefix (e.g., MSSQL15 for SQL Server 2019, MSSQL16 for SQL Server 2022). This is used to correctly build registry paths for named instances. - GetDefaultPaths Procedure
This helper procedure reads the default data and log file paths from the Windows registry. For named instances, it constructs the correct registry path using the version prefix and the instance name. - CreateRestoreScript Procedure
This main procedure:- Identifies the backup chain (full, differential, and log backups) from the MSDB backup history.
- Builds MOVE clauses for the full backup, even if multiple data or log files exist.
- Generates a complete restore script that restores the database stepbystep.
- Supports an optional alternate restore database name and an optional instance name to retrieve the correct file paths.
- Aggregates all the restore commands into a single string using STRING_AGG.
3. The Code Overview
3.1 GetSQLVersionPrefix Function
This function uses the SERVERPROPERTY function to fetch the product version and then extracts the first two characters to form a prefix like MSSQL15 or MSSQL16.
IF OBJECT_ID(N'[dbo].[GetSQLVersionPrefix]', N'FN') IS NOT NULL
DROP FUNCTION [dbo].[GetSQLVersionPrefix];
GO
CREATE FUNCTION [dbo].[GetSQLVersionPrefix]()
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @SQLVersion NVARCHAR(50);
DECLARE @VersionPrefix NVARCHAR(10);
SELECT @SQLVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(50));
-- Example: if ProductVersion returns '15.0.2000.5', the prefix becomes 'MSSQL15'
SET @VersionPrefix = 'MSSQL' + SUBSTRING(@SQLVersion, 1, 2);
RETURN @VersionPrefix;
END;
GO3.2 GetDefaultPaths Procedure
This procedure retrieves the default data and log file paths. It uses the version prefix and instance name to construct the registry path for a named instance. If no instance name is provided, it defaults to the standard path for the default instance.
IF OBJECT_ID(N'[dbo].[GetDefaultPaths]', N'P') IS NOT NULL
DROP PROCEDURE [dbo].[GetDefaultPaths];
GO
CREATE PROCEDURE [dbo].[GetDefaultPaths]
@InstanceName SYSNAME = NULL, -- Uses default instance (MSSQLSERVER) if NULL
@DefaultDataPath NVARCHAR(260) OUTPUT,
@DefaultLogPath NVARCHAR(260) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
IF @InstanceName IS NULL
BEGIN
-- Default instance path
EXEC master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\MSSQLSERVER\MSSQLServer',
N'DefaultData',
@DefaultDataPath OUTPUT;
EXEC master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\MSSQLSERVER\MSSQLServer',
N'DefaultLog',
@DefaultLogPath OUTPUT;
END
ELSE
BEGIN
DECLARE @VersionPrefix NVARCHAR(10);
DECLARE @RegistryPath NVARCHAR(500);
SET @VersionPrefix = dbo.GetSQLVersionPrefix();
-- Construct the registry path for a named instance.
-- For example: 'Software\Microsoft\Microsoft SQL Server\MSSQL16.INS01\MSSQLServer'
SET @RegistryPath = N'Software\Microsoft\Microsoft SQL Server\'
+ @VersionPrefix + N'.' + @InstanceName + N'\MSSQLServer';
EXEC master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
@RegistryPath,
N'DefaultData',
@DefaultDataPath OUTPUT;
EXEC master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
@RegistryPath,
N'DefaultLog',
@DefaultLogPath OUTPUT;
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
RETURN;
END CATCH;
END;
GO
3.3 CreateRestoreScript Procedure
This is the main procedure that assembles the entire restore script. It:
- Retrieves the latest full backup (and differential backup, if available) from the MSDB backup history.
- Uses the helper procedure to get default file paths.
- Queries the backup file metadata to build MOVE clauses for every file in the full backup.
- Generates restore commands for the full backup (including MOVE clauses), differential backup, and log backups.
- Aggregates all commands into a single script using STRING_AGG.
IF OBJECT_ID(N'[dbo].[CreateRestoreScript]', N'P') IS NOT NULL
DROP PROCEDURE [dbo].[CreateRestoreScript];
GO
CREATE PROCEDURE [dbo].[CreateRestoreScript]
@DatabaseName SYSNAME = 'AdminDb',
@RecoveryPoint DATETIME,
@AlternateDatabaseName SYSNAME = NULL, -- Optional alternate restore database name
@InstanceName SYSNAME = NULL -- Optional instance name for default file paths
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
DECLARE @LastFullBackupDate DATETIME;
DECLARE @LastDiffBackupDate DATETIME;
DECLARE @RecoveryPointStr VARCHAR(50);
DECLARE @DefaultDataPath NVARCHAR(260) = '';
DECLARE @DefaultLogPath NVARCHAR(260) = '';
DECLARE @RestoreDatabaseName NVARCHAR(128);
DECLARE @MaxBackupDate DATETIME;
DECLARE @FullBackupSetId INT;
DECLARE @FullMoveClauses NVARCHAR(MAX) = '';
SET @RecoveryPointStr = CONVERT(VARCHAR(50), @RecoveryPoint, 120);
-- Get the latest full backup at or before the recovery point.
SELECT TOP 1
@LastFullBackupDate = [backup_finish_date]
FROM [msdb].[dbo].[backupset]
WHERE [database_name] = @DatabaseName
AND [type] = 'D'
AND [backup_finish_date] <= @RecoveryPoint
ORDER BY [backup_finish_date] DESC;
IF @LastFullBackupDate IS NULL
BEGIN
RAISERROR('No full backup found for database [%s] at or before the recovery point [%s].', 16, 1, @DatabaseName, @RecoveryPointStr);
RETURN;
END
-- Retrieve the backup_set_id of the full backup.
SELECT TOP 1 @FullBackupSetId = backup_set_id
FROM [msdb].[dbo].[backupset]
WHERE [database_name] = @DatabaseName
AND [type] = 'D'
AND [backup_finish_date] = @LastFullBackupDate;
-- Get the latest differential backup (if any) after the full backup.
SELECT TOP 1
@LastDiffBackupDate = [backup_finish_date]
FROM [msdb].[dbo].[backupset]
WHERE [database_name] = @DatabaseName
AND [type] = 'I'
AND [backup_finish_date] > @LastFullBackupDate
AND [backup_finish_date] <= @RecoveryPoint
ORDER BY [backup_finish_date] DESC;
-- Retrieve default file locations.
EXEC [dbo].[GetDefaultPaths]
@InstanceName = @InstanceName,
@DefaultDataPath = @DefaultDataPath OUTPUT,
@DefaultLogPath = @DefaultLogPath OUTPUT;
SET @RestoreDatabaseName = ISNULL(@AlternateDatabaseName, @DatabaseName);
-- Generate MOVE clauses for the full backup by querying the backupfile table.
SELECT @FullMoveClauses = STUFF((
SELECT ', MOVE ''' + logical_name + ''' TO ''' +
CASE
WHEN file_type = 'D' AND UPPER(filegroup_name) = 'PRIMARY'
THEN @DefaultDataPath + '\' + @RestoreDatabaseName + '.mdf'
WHEN file_type = 'D'
THEN @DefaultDataPath + '\' + @RestoreDatabaseName + '_' + logical_name + '.ndf'
WHEN file_type = 'L'
THEN @DefaultLogPath + '\' + @RestoreDatabaseName + '_' + logical_name + '.ldf'
ELSE ''
END + ''''
FROM [msdb].[dbo].[backupfile]
WHERE backup_set_id = @FullBackupSetId
ORDER BY file_number
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
-- Create a table to hold the backup chain.
DECLARE @Results TABLE (
[DatabaseName] SYSNAME,
[BackupType] NVARCHAR(20),
[BackupDate] DATETIME,
[BackupFileList] NVARCHAR(MAX)
);
-- Insert full backup record.
INSERT INTO @Results ([DatabaseName], [BackupType], [BackupDate], [BackupFileList])
SELECT TOP 1
[database_name],
'Full' AS [BackupType],
[backup_finish_date],
STUFF((
SELECT ', DISK = ''' + [physical_device_name] + ''''
FROM [msdb].[dbo].[backupmediafamily]
WHERE [media_set_id] = bs.[media_set_id]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM [msdb].[dbo].[backupset] bs
WHERE [database_name] = @DatabaseName
AND [type] = 'D'
AND [backup_finish_date] = @LastFullBackupDate;
-- Insert differential backup record if exists.
IF @LastDiffBackupDate IS NOT NULL
BEGIN
INSERT INTO @Results ([DatabaseName], [BackupType], [BackupDate], [BackupFileList])
SELECT TOP 1
[database_name],
'Differential' AS [BackupType],
[backup_finish_date],
STUFF((
SELECT ', DISK = ''' + [physical_device_name] + ''''
FROM [msdb].[dbo].[backupmediafamily]
WHERE [media_set_id] = bs.[media_set_id]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM [msdb].[dbo].[backupset] bs
WHERE [database_name] = @DatabaseName
AND [type] = 'I'
AND [backup_finish_date] = @LastDiffBackupDate;
END
-- Insert log backup records.
INSERT INTO @Results ([DatabaseName], [BackupType], [BackupDate], [BackupFileList])
SELECT
[database_name],
'Log' AS [BackupType],
[backup_finish_date],
STUFF((
SELECT ', DISK = ''' + [physical_device_name] + ''''
FROM [msdb].[dbo].[backupmediafamily]
WHERE [media_set_id] = bs.[media_set_id]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM [msdb].[dbo].[backupset] bs
WHERE [database_name] = @DatabaseName
AND [type] = 'L'
AND [backup_finish_date] > ISNULL(@LastDiffBackupDate, @LastFullBackupDate)
AND [backup_finish_date] <= @RecoveryPoint
ORDER BY [backup_finish_date] ASC;
-- Find the last backup date.
SELECT @MaxBackupDate = MAX([BackupDate]) FROM @Results;
-- Build restore commands and aggregate into a single string.
;WITH CTE_RestoreCommands AS (
SELECT
[BackupDate],
CASE
WHEN [BackupType] = 'Full' THEN
'RESTORE DATABASE [' + @RestoreDatabaseName + '] FROM ' + [BackupFileList] +
' WITH ' + @FullMoveClauses +
CASE WHEN [BackupDate] = @MaxBackupDate THEN ' , RECOVERY' ELSE ' , NORECOVERY' END +
' , REPLACE;'
WHEN [BackupType] = 'Differential' THEN
'RESTORE DATABASE [' + @RestoreDatabaseName + '] FROM ' + [BackupFileList] +
CASE WHEN [BackupDate] = @MaxBackupDate THEN ' WITH RECOVERY;' ELSE ' WITH NORECOVERY;' END
WHEN [BackupType] = 'Log' THEN
'RESTORE LOG [' + @RestoreDatabaseName + '] FROM ' + [BackupFileList] +
CASE WHEN [BackupDate] = @MaxBackupDate THEN ' WITH RECOVERY;' ELSE ' WITH NORECOVERY;' END
ELSE ''
END AS [RestoreCommand]
FROM @Results
)
SELECT STRING_AGG([RestoreCommand], CHAR(13)+CHAR(10)) AS [CombinedRestoreCommands]
FROM CTE_RestoreCommands;
END TRY
BEGIN CATCH
DECLARE @ErrorMsg NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
SELECT @ErrorMsg = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
END CATCH;
END;
GO
4. Testing the Solution
Before using this in production, test it thoroughly with your backup history:
- Default Instance vs. Named Instance:
Test with and without specifying @InstanceName to ensure default file paths are retrieved correctly. - Multiple Files:
Verify that the generated MOVE clauses correctly map all data and log files (with MDF, NDF, and LDF extensions). - Backup Chain Variations:
Test scenarios with only full backups, full + differential, and full + differential + log backups. - Alternate Restore Database Name:
Ensure the restore commands reference the alternate name when provided. - Error Handling:
Test error scenarios (e.g., no full backup exists) to see that appropriate error messages are raised.
5. Conclusion
This modular TSQL solution automates the tedious process of creating a restore script for pointintime recovery. By breaking the solution into a version prefix function, a helper procedure for default file paths, and a main procedure that constructs the restore chain, the approach is not only robust and flexible but also easy to maintain and extend.
Feel free to adapt the naming conventions and file mapping logic to suit your environment. If you have any questions or suggestions, please leave a comment below!
Happy restoring!
