Can any one share a script that will allow me to restore all the .bak files in my one folder into SQL Server using a TSQL script?


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
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
        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)
           FROM DISK=''' +@file+ '''

        UPDATE #FileList
            SET unique_Hash = @uniqueHash
            WHERE unique_Hash IS NULL

        FETCH NEXT FROM cursor_bak INTO @file
    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
, '--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)
/*  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)
/*Recover?*/        + ', NORECOVERY' 
            AS [Param]
        FROM List A
        GROUP BY A.Unique_Hash, A.DatabaseName
        ) D ON D.Unique_Hash = BH.Unique_Hash

Edited: The outputted column with the script is simple:

--CheckPoint: 179000000011500001 | LastLSN: 179000000011800001
    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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.