Restore database in SQL Server TSQL Statement

In this post, I will demonstrate how to retrieve backup file details and relevant information using SQL queries. The query below fetches the latest 100 backup records ordered by date.

SELECT TOP 100
    DatabaseName = bs.database_name,
    BackupStartDate = bs.backup_start_date,
    CompressedBackupSize = bs.compressed_backup_size,
    ExpirationDate = bs.expiration_date,
    BackupSetName = bs.name,
    RecoveryModel = bs.recovery_model,
    ServerName = bs.server_name,
    CASE bs.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        WHEN 'F' THEN 'File or filegroup'
        WHEN 'G' THEN 'Differential file'
        WHEN 'P' THEN 'Partial'
        WHEN 'Q' THEN 'Differential partial'
        ELSE bs.[type]
    END AS BackupType,
    LogicalDeviceName = bmf.logical_device_name,
    PhysicalDeviceName = bmf.physical_device_name,
    'RESTORE LABELONLY FROM DISK = ''' + bmf.physical_device_name + ''' WITH CHECKSUM ' AS ChkSQL,
    'RESTORE ' + CASE bs.[type]
        WHEN 'D' THEN 'DATABASE'
        WHEN 'I' THEN 'DATABASE'
        WHEN 'L' THEN 'LOG'
    END + ' ' + bs.database_name + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY' AS 'RestoreSQL'
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = '<Database Name>'
ORDER BY bs.backup_start_date DESC;

Make sure to replace `` with the actual database name, or simply omit this part from the query.

The following query extracts backup media information based on the backup file name retrieved from the previous query:

RESTORE LABELONLY FROM DISK = 'I:\\MSSQL\\Backup\\dbname\\dbname_backup_2020_12_04_090003_0380615.trn' WITH CHECKSUM

This query will retrieve file details from the backup file name provided:

RESTORE FILELISTONLY FROM DISK = 'I:\\MSSQL\\Backup\\DBNAME\\DBNAME_backup_2020_12_03_200002_1858707.bak'

The following query provides the full backup header information for all backups stored on a specific device in SQL Server:

RESTORE HEADERONLY FROM DISK = N'I:\\MSSQL\\Backup\\AdventureWorks-FullBackup.bak';

Here’s a query that creates a temporary table and stores file information from the backup file. This can be useful if you want to restore the database to a different file path.

CREATE TABLE #RestoreFileList (
    RowID INT IDENTITY(1, 1),
    LogicalName VARCHAR(250),
    PhysicalName VARCHAR(500),
    [Type] VARCHAR(2),
    FileGroupName VARCHAR(100),
    Size VARCHAR(100),
    MaxSize VARCHAR(100),
    FileId INT,
    CreateLSN VARCHAR(250),
    DropLSN VARCHAR(250),
    UniqueId VARCHAR(250),
    ReadOnlyLSN VARCHAR(250),
    ReadWriteLSN VARCHAR(250),
    BackupSizeInBytes VARCHAR(250),
    SourceBlockSize INT,
    FileGroupId INT,
    FileGroupGUID VARCHAR(250),
    DifferentialBaseLSN VARCHAR(250),
    DifferentialBaseGUID VARCHAR(250),
    IsReadOnly VARCHAR(2),
    IsPresent VARCHAR(2),
    TDEThumbPrint VARCHAR(250)
);

INSERT INTO #RestoreFileList (
    LogicalName,
    PhysicalName,
    [Type],
    FileGroupName,
    Size,
    MaxSize,
    FileId,
    CreateLSN,
    DropLSN,
    UniqueId,
    ReadOnlyLSN,
    ReadWriteLSN,
    BackupSizeInBytes,
    SourceBlockSize,
    FileGroupId,
    FileGroupGUID,
    DifferentialBaseLSN,
    DifferentialBaseGUID,
    IsReadOnly,
    IsPresent,
    TDEThumbPrint
)
EXEC ('RESTORE FILELISTONLY FROM DISK = ''I:\\MSSQL\\Backup\\DBNAME\\DNNAME_backup_2020_12_03_200002_1858707.bak''');

The following query generates a restore script with the specified path. You should replace `@newPath` and the backup filename as per your requirements. Depending on your SQL Server version, you might need to adjust the columns accordingly.

DECLARE @dataFile VARCHAR(1000) = '', @newPath VARCHAR(100) = 'c:\\testpat\\';
SET @dataFile = 'RESTORE DATABASE [signphoto] FROM DISK = N''I:\\MSSQL\\signphoto.bak'' WITH FILE = 1, ';
SELECT @dataFile = @dataFile + 'MOVE' + '''' + LogicalName + '''' + ' TO '
 + '''' + @newPath + '\\' + RIGHT(PhysicalName, CHARINDEX('\\', REVERSE(PhysicalName)) - 1) + ''',' + CHAR(13)
FROM #RestoreFileList
ORDER BY FileId;
PRINT @dataFile + ', NOUNLOAD, STATS = 5';

The generated restore script will look like this:

RESTORE DATABASE [signphoto] FROM DISK = N'I:\\MSSQL\\signphoto.bak' WITH FILE = 1, MOVE 'signphoto' TO 'c:\\testpat\\signphoto.mdf',
MOVE 'signphoto_log' TO 'c:\\testpat\\signphoto_log.ldf', NOUNLOAD, STATS = 5;

Additional References:

Restore HeaderOnly Documentation

Restore FileListOnly Documentation

Related blog posts:

Backup History Using T-SQL

Backup History