Problem
I have a database backup that I need to restore on another instance of SQL Server. Is there way to generate the SQL restore command by reading data from the backup file?
Solution
When a database backup is created, several pieces of information are stored in the backup file. Some of the items are the name and location of the database files that make up the database. This information is key when writing a RESTORE command.
SQL Server Default Folders
When you setup SQL Server, there are several default folders that are used for installation, database files, etc.
- For my instance of SQL Server, it is installed in the default directory: C:\Program Files\Microsoft SQL Server
- For a default instance, the data directory would be C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER
- For a named instance called TEST, the data directory would be C:\Program Files\Microsoft SQL Server\MSSQL15.TEST
This is key information when doing a restore on a server. You can either use the default folders or use the path information that is stored in the backup file.
Backup File Information with RESTORE FILELISTONLY
We have a backup for the database AdventureWorks2012 created as C:\tests\aw.bak.
We can use the RESTORE FILELISTONLY command on the backup file as follows:
restore filelistonly from disk = 'C:\tests\aw.bak'Below shows a subset of the data that is returned. The information below is key for creating the restore command.
By default, a RESTORE command restores the database in the directory shown in the PhysicalName field. Other key info includes the LogicalName which we will use below and also the Type which is the file type for the database such as data (D) or log (L).
In addition, a database can have multiple data and log files, so we need to be able to account for that too.
Default Data and Log Paths
As mentioned, there are default folders used for data and log files. We can get this information as follows.
SELECT SERVERPROPERTY('InstanceDefaultDataPath')
SELECT SERVERPROPERTY('InstanceDefaultLogPath')For my instance this returns the same for both, but this could be different on your instance.
- C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
- C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
SQL Code to Generate Restore Command
Let’s say we want to restore the “aw.bak” backup on a test instance of SQL Server. We can copy the backup to the other instance and use the following code to do the restore.
Unless we want to create the data and log files in the same folder location, we can need to use the “move” option so the data and log files are created in the correct folder.
In the code below, we create a temporary table variable to store the results from the RESTORE FILELISTONLY command.
In addition, we get the default paths for the data and log files that will be used.
Then we will assemble the restore command with the rest of the code.
declare @dbname varchar(128),
@sql varchar(2048),
@DefaultDataFileLocation varchar(2048),
@DatabaseBackupLocation varchar(2048),
@DefaultLogFileLocation varchar(2048);
-- default data file location - you could include your own path instead of using default
set @DefaultDataFileLocation = convert(varchar(512), (SELECT SERVERPROPERTY('InstanceDefaultDataPath')));
-- default log file location - you could include your own path instead of using default
set @DefaultLogFileLocation = convert(varchar(512), (SELECT SERVERPROPERTY('InstanceDefaultLogPath')));
-- location of the backup file to be restored
set @DatabaseBackupLocation = 'c:\tests\aw.bak'
-- name of restored database
set @dbname = 'AdventureWorksTestRestore'
declare @tmp table
(
[LogicalName] VARCHAR(128),
[PhysicalName] VARCHAR(260),
[Type] CHAR(1),
[FileGroupName] VARCHAR(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),
[SnapshotURL] VARCHAR(360)
);
-- get data from the RESTORE FILELISTONLY command
set @sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @databaseBackupLocation + ''''
insert into @tmp exec(@sql);
-- build restore command
set @sql = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @DatabaseBackupLocation + ''' WITH REPLACE, '
-- add each data and log file
set @sql = @sql + STUFF((
SELECT ', move ''' + logicalName + ''' to ''' + CASE WHEN Type = 'D' THEN @DefaultDataFileLocation ELSE @DefaultLogFileLocation END + reverse(substring(reverse(PhysicalName),1,CHARINDEX('\',reverse(PhysicalName)) - 1)) + ''''
FROM @tmp FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
print (@sql);
--exec(@sql);Here is the output that gets created for the above code. The format was changed a bit to make it more readable instead of just one long string.
There is one data file and one log file for the “aw.bak” backup.
RESTORE DATABASE [AdventureWorksTestRestore]
FROM DISK = 'C:\test\aw.bak'
WITH REPLACE,
move 'AdventureWorks2012' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012.mdf',
move 'AdventureWorks2012_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf'Here is another example where the are multiple data files for the “Test4” database.
RESTORE DATABASE [TestRestore]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\test4.bak'
WITH REPLACE,
move 'Test4' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Test4.mdf',
move 'Test4-2' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Test4-2.ndf',
move 'Test4_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Test4_log.ldf'Summary
With this code you can automate restores and make sure to move the data and log files to the appropriate folders.
As you probably noticed, the name of the data and log files use the same names as the original database. You could further enhance this script to change the file names to new names to correspond with the restored database name.
Next Steps
