/**************
    Script out Backup Creation
    by clifton_h (stackoverflow)
**************/

DECLARE @directory NVARCHAR(2000) = N'C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackup'
      , @datafile_destination NVARCHAR(3000) = N'Z:DATADISK1sql_dat'
      , @logfile_destination NVARCHAR(3000) = N'Z:LOGDISK1sql_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
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

--CheckPoint: 179000000011500001 | LastLSN: 179000000011800001
RESTORE DATABASE Testing
    FROM  DISK ='C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackupTtrial.bak'
    ,  DISK ='C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackupunknown.bak'
     WITH  MOVE 'Testing' TO 'Z:DISK1sql_datTesting.mdf'
    ,  MOVE 'Testing_log' TO 'Z:DISK1sql_datTesting_log.ldf'
    , NORECOVERY