Automatically Generate Restore Database SQL Server Scripts

By: Nisarg Upadhyay   |   Updated: 2024-11-28   |   Comments   |   Related: > Restore

Problem

Recently, we were assigned a project to migrate the entire development environment to a new set of SQL Servers. There are 50 databases, most being approximately 15-20 GB. We have sufficient downtime and the point-in-time recovery is not needed. The only challenge was to reduce the effort. How can you restore multiple SQL databases on the same SQL Server instance using T-SQL?

Solution

There are multiple ways to migrate them to a new server:

1. Set up log shipping between source and destination servers. This method is very efficient and requires less downtime. But, the problem is if you have to establish log shipping on a lot of databases can be time-consuming.
2. Backup all databases on the old server and restore them on the new server. This method is also efficient, but again you have to manually restore all the databases on a new server, which is time-consuming and less efficient.

We decided to use the backup and restore method. To reduce the manual effort, we created a T-SQL script to generate the RESTORE DATABASE command for all databases on the source server based on the backup files that existed.

Note:

1. The script works when there is one backup file per database. If you must split the backup file into multiple parts, the script will not generate the required output.
2. The script works for the databases that have one data file and one log file. If you have multiple data files or filegroups, the script will not produce the desired results. (future article to come)
3. The script restores the Full backups.

For a detailed description, I have divided the entire script into multiple parts.

Part 1: Variable Declaration

Below is the list of variables used to store the required values and build the RESTORE DATABASE command:

1. @BackupDirectory: The location of the backup of the user database. In our case, the backup directory is C:\Devlopment\Backup\.
2. @DatabaseName: The database name to be restored.
3. @BackupFile: The backup file name.
4. @RestoreCommand: The restore command that is generated using dynamic T-SQL.
5. @LogicalFileName: The logical file name of the data file.
6. @LogicalLogFileName: The logical name of the transaction log file.
7. @PhysicalFileName: The physical name (location) of the database file.
8. @PhysicalLogFileName: The physical name (location) of the transaction log file.
9. @destinationserver_datadirectory: The location of the data file directory on the destination server. In our case, the destination server data directory is C:\Devlopment\Data\.
10. @destinationserver_logdirectory: The location of the log file directory on the destination server. In our case, the destination server data directory is C:\Devlopment\Log\.

Here is the T-SQL code for the variable declaration:

DECLARE @BackupDirectory NVARCHAR(255) = N'C:\Development\Backup\' 
DECLARE @DatabaseName NVARCHAR(255) 
DECLARE @BackupFile NVARCHAR(max) 
DECLARE @RestoreCommand NVARCHAR(MAX) 
DECLARE @LogicalFileName NVARCHAR(255) 
DECLARE @LogicalLogFileName NVARCHAR(255) 
DECLARE @PhysicalFileName NVARCHAR(255) 
DECLARE @PhysicalLogFileName NVARCHAR(255) 
 
DECLARE @destinationserver_datadirectory nvarchar(max) ='C:\Development\Data\'
DECLARE @destinationserver_logdirectory nvarchar(max)='C:\Development\Log\'
DECLARE @MigrationDBCount int
DECLARE @i int =0

Now, let us understand how to assign the appropriate values to the above declared variables.

Part 2: Assign the Values to the Parameters

First, create a temp table to store the list of databases that you want to migrate. I have created a linked server between source and destination servers. You can read this article to learn more about SQL Server Linked Servers.

Here is the code to create a temp table and insert the list of databases to be migrated:

CREATE TABLE #DatabaseToMigrate (DatabaseName varchar(500))
INSERT INTO #DatabaseToMigrate (DatabaseName)
SELECT name FROM master.sys.databases WHERE database_id > 4

As per the scenario, the naming convention of the backup file is <dbname>.bak and the backup files are in the C:\Devlopment\Backups directory. To get the exact path of the backup, we will concat the parameters @backupDirectory and @databasename. To obtain the list of databases to be migrated, I have created a WHILE loop. The loop will iterate through the temp table and store the database name in @databasename variable.

Here is the code to create the WHILE loop and set the path of the backup file:

SET @MigrationDBCount = (SELECT count(1) FROM #DatabaseToMigrate)
WHILE @I<@MigrationDBCount
BEGIN

As per our use case, the default data and log directory are different than a source server. Hence, we must obtain the logical name of the database files. We can use the BACKUP FILELIST ONLY command to get the logical file names from the backup files. To do that, we need to create a table variable to store all the information returned by the BACKUP FILELIST command.

Here is the query to declare a table variable and save the backup information.

SET @DatabaseName_Destination = (select top 1 databasename from #DatabaseToMigrate)
SET @BackupFile = @BackupDirectory + @DatabaseName_Destination + '.bak'
DECLARE @LogicalFileNameTable TABLE (
    LogicalName NVARCHAR(128),
    PhysicalName NVARCHAR(260),
    Type CHAR(1),
    FileGroupName NVARCHAR(128),
    Size NUMERIC(20,0),
    MaxSize NUMERIC(20,0),
    FileId BIGINT,
    CreateLSN NUMERIC(25,0),
    DropLSN NUMERIC(25,0),
    UniqueId UNIQUEIDENTIFIER,
    ReadOnlyLSN NUMERIC(25,0),
    ReadWriteLSN NUMERIC(25,0),
    BackupSizeInBytes BIGINT,
    SourceBlockSize INT,
    FileGroupId INT,
    LogGroupGUID UNIQUEIDENTIFIER,
    DifferentialBaseLSN NUMERIC(25,0),
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    IsReadOnly BIT,
    IsPresent BIT,
    TDEThumbprint VARBINARY(32),
    SnapshoURL nvarchar(max)
)

INSERT INTO @LogicalFileNameTable
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')

Now, we will obtain the logical name and path of the databases to be migrated. Run the SELECT query on @logicalfilename table variable.

1. The path of the data file will be obtained by concatenating @destinationserver_datadirectory and the logicalName column. It will be stored in @PhysicalFileName.
2. The path of the log file will be obtained by concatenating @destinationserver_logdirectory and the logicalName column. It will be stored in @PhysicalLogFileName.
3. The logical name of the data file and log file will be stored in the @logicalFileName and @LogicalLogFileName variables.

Here is the script:

SELECT 
    @LogicalFileName = LogicalName,
    @PhysicalFileName = @destinationserver_datadirectory + LogicalName + '.mdf'
FROM @LogicalFileNameTable
WHERE Type = 'D'

SELECT 
    @LogicalLogFileName = LogicalName,
    @PhysicalLogFileName = @destinationserver_logdirectory + LogicalName + '.ldf'
FROM @LogicalFileNameTable
WHERE Type = 'L'

Part 3: Build the Dynamic RESTORE DATABASE Command

Now, let us build the RESTORE DATABASE command by using all the variables. Note: The data directory and log directory on the destination server are different than the source server; hence, we will use the WITH MOVE option in the RESTORE DATABASE command.

Below is the dynamic SQL query that generates a RESTORE DATABASE command:

SET @RestoreCommand = '
RESTORE DATABASE [' + @DatabaseName_Destination + '] 
FROM DISK = ''' + @BackupFile + ''' 
WITH 
    MOVE ''' + @LogicalFileName + ''' TO ''' + @PhysicalFileName + ''',
    MOVE ''' + @LogicalLogFileName + ''' TO ''' + @PhysicalLogFileName + ''',
    RECOVERY,  STATS=5'

-- Print the restore command (for debugging purposes)
PRINT @RestoreCommand
DELETE FROM #DatabaseToMigrate WHERE DatabaseName=@DatabaseName_Destination
SET @i=@i+1

Entire Restore Script

SET nocount on
DECLARE @BackupDirectory NVARCHAR(255) = N'C:\Development\Backup\' -- Backup directory
DECLARE @DatabaseName_Destination NVARCHAR(255) -- Variable to hold database name
DECLARE @BackupFile NVARCHAR(255) -- Variable to hold backup file name
DECLARE @RestoreCommand NVARCHAR(MAX) -- Variable to hold restore command
DECLARE @LogicalFileName NVARCHAR(255) -- Variable to hold logical file name
DECLARE @LogicalLogFileName NVARCHAR(255) -- Variable to hold logical log file name
DECLARE @PhysicalFileName NVARCHAR(255) -- Variable to hold physical file name
DECLARE @PhysicalLogFileName NVARCHAR(255) -- Variable to hold physical log file name
 
DECLARE @destinationserver_datadirectory nvarchar(max) ='C:\Development\Data\'
DECLARE @destinationserver_logdirectory nvarchar(max)='C:\Development\Log\'
DECLARE @MigrationDBCount int
DECLARE @i int =0
 
CREATE TABLE #DatabaseToMigrate (DatabaseName varchar(500))
INSERT INTO #DatabaseToMigrate (DatabaseName)
SELECT name FROM master.sys.databases where database_id > 4

SET @MigrationDBCount = (SELECT count(1) FROM #DatabaseToMigrate)

WHILE @I < @MigrationDBCount
BEGIN
    
    SET @DatabaseName_Destination= (SELECT top 1 databasename FROM #DatabaseToMigrate)
    SET @BackupFile = @BackupDirectory + @DatabaseName_Destination + '.bak'
    DECLARE @LogicalFileNameTable TABLE (
        LogicalName NVARCHAR(128),
        PhysicalName NVARCHAR(260),
        Type CHAR(1),
        FileGroupName NVARCHAR(128),
        Size NUMERIC(20,0),
        MaxSize NUMERIC(20,0),
        FileId BIGINT,
        CreateLSN NUMERIC(25,0),
        DropLSN NUMERIC(25,0),
        UniqueId UNIQUEIDENTIFIER,
        ReadOnlyLSN NUMERIC(25,0),
        ReadWriteLSN NUMERIC(25,0),
        BackupSizeInBytes BIGINT,
        SourceBlockSize INT,
        FileGroupId INT,
        LogGroupGUID UNIQUEIDENTIFIER,
        DifferentialBaseLSN NUMERIC(25,0),
        DifferentialBaseGUID UNIQUEIDENTIFIER,
        IsReadOnly BIT,
        IsPresent BIT,
        TDEThumbprint VARBINARY(32),
        SnapshoURL nvarchar(max)
    )
 
    INSERT INTO @LogicalFileNameTable
    EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')
    
    SELECT 
        @LogicalFileName = LogicalName,
        @PhysicalFileName = @destinationserver_datadirectory + LogicalName + '.mdf'
    FROM @LogicalFileNameTable
    WHERE Type = 'D'
 
    SELECT 
        @LogicalLogFileName = LogicalName,
        @PhysicalLogFileName = @destinationserver_logdirectory + LogicalName + '.ldf'
    FROM @LogicalFileNameTable
    WHERE Type = 'L'
 
    -- Construct the restore command
    SET @RestoreCommand = '
    RESTORE DATABASE [' + @DatabaseName_Destination + '] 
    FROM DISK = ''' + @BackupFile + ''' 
    WITH 
        MOVE ''' + @LogicalFileName + ''' TO ''' + @PhysicalFileName + ''',
        MOVE ''' + @LogicalLogFileName + ''' TO ''' + @PhysicalLogFileName + ''',
        RECOVERY,  STATS=5'
 
    -- Print the restore command (for debugging purposes)
    PRINT @RestoreCommand
    DELETE FROM #DatabaseToMigrate WHERE DatabaseName=@DatabaseName_Destination
    SET @i=@i+1
END
DROP TABLE #DatabaseToMigrate

Testing the Script and Sample Output

Before running the entire script, we need to verify that the script is generating the proper RESTORE DATABASE command. Instead of using exec sp_executesql statement, I have used the Print command to see the actual restore commands which you can then copy and execute on the destination server.

Below is a sample RESTORE DATABASE command generated by the above script:

RESTORE DATABASE [StackOverflow2010] 
FROM DISK = 'C:\Development\Backup\StackOverflow2010.bak' 
WITH 
    MOVE 'StackOverflow2010' TO 'C:\Development\Data\StackOverflow2010.mdf',
    MOVE 'StackOverflow2010_log' TO 'C:\Development\Log\StackOverflow2010_log.ldf',
    RECOVERY,  STATS=5

RESTORE DATABASE [VehicleWorkshop] 
FROM DISK = 'C:\Development\Backup\VehicleWorkshop.bak' 
WITH 
    MOVE 'VehicleWorkshop' TO 'C:\Development\Data\VehicleWorkshop.mdf',
    MOVE 'VehicleWorkshop_log' TO 'C:\Development\Log\VehicleWorkshop_log.ldf',
    RECOVERY,  STATS=5

As you can see, the script has generated the RESTORE DATABASE command per our requirement.

Summary

In this tip, I have explained how to create a T-SQL script that can be used to generate the RESTORE DATABASE command for multiple databases. The script works on specific use cases.

In the next SQL tutorial, I will explain how to generate the RESTORE DATABASE statement for databases with multiple file groups and backup files.

Next Steps

• Read SQL Server Restore Database Options and Examples to learn more about database restores in SQL Server.
• Read Auto generate SQL Server restore script from backup files in a directory to learn the process of restoring full, differential, and log backups in one go.

About the author

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips