There are a number of valid ways to do this depending on your sources...here, I am assuming these are part of an ETL process for your development team or something that.
First, we need to know if what bak files are in sets and what logical names their files have. You can find this information via the RESTORE HEADERONLY and RESTORE FILELISTONLY statements. These statements will produce a tabular result showing you details about the backup sets: Where they came from, who owns them, LSN, if the files are corrupt, and other metadata that can be helpful to DBAs.
That said, it can be a pain to script all of this out. Hence, the following script:
/**************
Script out Backup Creation
by clifton_h (stackoverflow)
**************/
DECLARE @directory NVARCHAR(2000) = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup'
, @datafile_destination NVARCHAR(3000) = N'Z:\DATA\DISK1\sql_dat\'
, @logfile_destination NVARCHAR(3000) = N'Z:\LOG\DISK1\sql_log\'
, @file NVARCHAR(2000)
, @uniqueHash VARBINARY(100)
CREATE TABLE #BackupHeader (BackupName nvarchar(128)
, BackupDescription nvarchar(255)
, BackupType SMALLINT
, ExpirationDate datetime
, Compressed CHAR(1)
, Position smallint
, DeviceType tinyint
, UserName nvarchar(128)
, ServerName nvarchar(128)
, DatabaseName nvarchar(128)
, DatabaseVersion INT
, DatabaseCreationDate datetime
, BackupSize numeric(20,0)
, FirstLSN numeric(25,0)
, LastLSN numeric(25,0)
, CheckpointLSN numeric(25,0)
, DatabaseBackupLSN numeric(25,0)
, BackupStartDate datetime
, BackupFinishDate datetime
, SortOrder smallint
, CodePage smallint
, UnicodeLocaleId INT
, UnicodeComparisonStyle INT
, CompatibilityLevel tinyint
, SoftwareVendorId INT
, SoftwareVersionMajor INT
, SoftwareVersionMinor INT
, SoftwareVersionBuild INT
, MachineName nvarchar(128)
, Flags INT
, BindingID uniqueidentifier
, RecoveryForkID uniqueidentifier
, Collation nvarchar(128)
, FamilyGUID uniqueidentifier
, HasBulkLoggedData bit
, IsSnapshot bit
, IsReadOnly bit
, IsSingleUser bit
, HasBackupChecksums bit
, IsDamaged bit
, BeginsLogChain bit
, HasIncompleteMetaData bit
, IsForceOffline bit
, IsCopyOnly bit
, FirstRecoveryForkID uniqueidentifier
, ForkPointLSN numeric(25,0)
, RecoveryModel nvarchar(60)
, DifferentialBaseLSN numeric(25,0)
, DifferentialBaseGUID uniqueidentifier
, BackupTypeDescription nvarchar(60)
, BackupSetGUID uniqueidentifier
, CompressedBackupSize bigint
, Containment tinyint NOT NULL
, KeyAlgorithm nvarchar(32)
, EncryptorThumbprint varbinary(20)
, EncryptorType nvarchar(32)
/*ADDED columns so we can track this*/
, bak_location NVARCHAR(2000)
, unique_Hash AS HASHBYTES('SHA1', CAST(ServerName AS VARBINARY(100) ) + CAST(DatabaseName AS VARBINARY(100) ) + CAST(BackupStartDate AS VARBINARY(100) ) + CAST(DatabaseBackupLSN AS VARBINARY(100) ))
)
/* get list of expected files and their logical names */
CREATE TABLE #FileList (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128),
[MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128),
[BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128), SnapshotUrl VARCHAR(4000)
/*ADDED columns so we can track this*/
, unique_HASH VARBINARY(100)
)
DECLARE @List_Bak AS TABLE (FileName VARCHAR(1000), depth INT , [file] INT)
/*******************
[1] Prepare List
******************/
INSERT INTO @List_Bak
EXEC Master.dbo.xp_dirtree @directory, 0, 1
DECLARE cursor_bak CURSOR
FOR SELECT @directory + '\' + FileName
FROM @List_Bak
WHERE [FileName] LIKE '%.bak'
OPEN cursor_bak
FETCH NEXT FROM cursor_bak INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #BackupHeader (BackupName, BackupDescription, BackupType, ExpirationDate, Compressed, Position, DeviceType, UserName, ServerName, DatabaseName, DatabaseVersion, DatabaseCreationDate, BackupSize, FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN, BackupStartDate, BackupFinishDate, SortOrder, CodePage, UnicodeLocaleId, UnicodeComparisonStyle, CompatibilityLevel, SoftwareVendorId, SoftwareVersionMajor, SoftwareVersionMinor, SoftwareVersionBuild, MachineName, Flags, BindingID, RecoveryForkID, Collation, FamilyGUID, HasBulkLoggedData, IsSnapshot, IsReadOnly, IsSingleUser, HasBackupChecksums, IsDamaged, BeginsLogChain, HasIncompleteMetaData, IsForceOffline, IsCopyOnly, FirstRecoveryForkID, ForkPointLSN, RecoveryModel, DifferentialBaseLSN, DifferentialBaseGUID, BackupTypeDescription, BackupSetGUID, CompressedBackupSize, Containment, KeyAlgorithm, EncryptorThumbprint, EncryptorType) --, bak_location
EXEC('RESTORE HEADERONLY FROM DISK = N''' + @file + '''')
SELECT @uniqueHash = unique_Hash
FROM #BackupHeader
WHERE bak_location IS NULL
/* Hash will allow us to easily compare files with their backup sets */
UPDATE #BackupHeader
SET bak_location = @file
WHERE bak_location IS NULL
INSERT INTO #FileList ( LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize, FileId, CreateLSN, DropLSN, UniqueId, ReadOnlyLSN, ReadWriteLSN, BackupSizeInBytes, SourceBlockSize, FileGroupId, LogGroupGUID, DifferentialBaseLSN, DifferentialBaseGUID, IsReadOnly, IsPresent, TDEThumbprint, SnapshotUrl)
EXEC('
RESTORE FILELISTONLY
FROM DISK=''' +@file+ '''
')
UPDATE #FileList
SET unique_Hash = @uniqueHash
WHERE unique_Hash IS NULL
FETCH NEXT FROM cursor_bak INTO @file
END
CLOSE cursor_bak
DEALLOCATE cursor_bak
/*******************
[2] Script Out Backup Sets
******************/
;WITH List AS (
SELECT bak_location, BH.ServerName, BH.DatabaseName, DatabaseCreationDate, BH.CheckpointLSN, BH.LastLSN, BackupSize / 1024.0 / 1024 /1024 AS BackupSizeGB, CompatibilityLevel/*, SoftwareVendorId, MachineName*/
/* , IsSnapshot, HasBackupChecksums, IsDamaged, HasIncompleteMetaData, IsCopyOnly, RecoveryModel, BackupTypeDescription, KeyAlgorithm*/, EncryptorType
, BH.Position, BH.Unique_Hash
FROM #BackupHeader BH
WHERE DeviceType = 2
)
SELECT DISTINCT BH.ServerName, BH.DatabaseName, BH.CheckpointLSN, LastLSN, D.Param, BH.BackupTypeDescription
FROM List BH
LEFT OUTER JOIN (SELECT A.Unique_Hash
, '--CheckPoint: ' + CAST(MAX(A.CheckpointLSN) AS VARCHAR(100) ) + ' | LastLSN: ' + CAST( MAX(A.LastLSN) AS VARCHAR(100) ) + '
RESTORE DATABASE ' + A.DatabaseName + '
FROM '
/* Backup file list */
+ STUFF(( SELECT DISTINCT ', DISK =''' + bak_location + ''''
+ CHAR(13) + CHAR(9)
FROM #BackupHeader
WHERE unique_Hash = A.Unique_Hash
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,2,'')
/* Move Locations */
+ ' WITH ' + STUFF(( SELECT ', MOVE ''' + FL.LogicalName
/* Make this the directory you need */
+ ''' TO ''' + CASE WHEN FL.Type = 'D' THEN @datafile_destination WHEN FL.Type = 'L' THEN @logfile_destination END + '''' + FL.LogicalName
+ CASE WHEN FL.Type = 'D' AND FL.FileId = 1 THEN '.mdf'
WHEN FL.Type = 'D' THEN '.mdf'
WHEN FL.Type = 'L' THEN '.ldf' END + ''''
+ CHAR(13) + CHAR(9)
FROM ( SELECT DISTINCT unique_Hash, LogicalName, Type, FileID
FROM #FileList) FL
LEFT OUTER JOIN ( SELECT DISTINCT unique_Hash FROM #BackupHeader) BH ON BH.unique_Hash = FL.unique_HASH
WHERE FL.unique_Hash = A.Unique_Hash
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,2,'')
/*Recover?*/ + ', NORECOVERY'
AS [Param]
FROM List A
GROUP BY A.Unique_Hash, A.DatabaseName
) D ON D.Unique_Hash = BH.Unique_Hash
GO
Edited: The outputted column with the script is simple:
--CheckPoint: 179000000011500001 | LastLSN: 179000000011800001
RESTORE DATABASE Testing
FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Ttrial.bak'
, DISK ='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\unknown.bak'
WITH MOVE 'Testing' TO 'Z:\DATA\DISK1\sql_dat\Testing.mdf'
, MOVE 'Testing_log' TO 'Z:\LOG\DISK1\sql_log\Testing_log.ldf'
, NORECOVERY
