Planning restore operations ahead of time is important to determine the space needed and in automation task Here, we provide the actual path to the folder where the BAK files are kept, and we generate a resotre script using the RESTORE HEADERONLY and FILELISTONLY operations for all databases on that folder.
/* DESCRIPTION Script Generator It creates the RESTORE scripts for all backup (bak) files on a directory and lists the space needed AT THE BOTTOM of output. Configuration At the top: Compatibility list: MSSQL2008 MSSQL2012 Does not work: MSSQL2005 for SQL2005 to work, the two tables for restore fileheaderonly and filelistonly need to be modified. */ set nocount on DECLARE @pathname VARCHAR(1024) DECLARE @DataPath NVARCHAR(3000) DECLARE @TlogPath NVARCHAR(3000) DECLARE @RELOCATE INT DECLARE @OVERRIDE INT --************** CONFIGURATION ************** -- Set the pathname for the location of the backup files -- must end wit ha backslash \ set @pathname = '"t:\bk"' -- Overrider: Say 1 if the database already exist and needs to be over written -- Overrider: Say 0 if the database is new to this server set @OVERRIDE = 1 --RELOCATE: If you want to relocate the data files, say 1 and fill in the following: -- if not, say 0 and skip. set @RELOCATE = 1 -- Data files must end wit ha backslash set @DataPath = 'E:\MSSQL\DATA\' -- Tlog Files must end wit ha backslash set @TlogPath = 'F:\TlogFolder\' --************** END CONFIGURATION ************** /* ************************************************** ************************************************** Declare Variables DO NOT MODIFY BELOW ************************************************** ************************************************** */ DECLARE @databasename NVARCHAR(1000) DECLARE @physicalName NVARCHAR(1000) DECLARE @filename NVARCHAR(1000) DECLARE @filepath NVARCHAR (1000) DECLARE @temp1 NVARCHAR (1000) DECLARE @temp2 NVARCHAR (1000) DECLARE @temp3 NVARCHAR (1000) DECLARE @move NVARCHAR (4000) DECLARE @CMD VARCHAR(512) DECLARE @bksizeTotal BIGINT DECLARE @DATAsizeTotal BIGINT DECLARE @TLOGsizeTotal BIGINT -- initialize size counters set @bksizeTotal = 0 set @DATAsizeTotal = 0 set @TLOGsizeTotal = 0 -- Create Tables to store data IF OBJECT_ID('tempdb..#filelist') IS NOT NULL DROP TABLE #filelist IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header CREATE TABLE #filelist ( LogicalName NVARCHAR(1024),PhysicalName NVARCHAR(1024),[Type] char(1),FileGroupName NVARCHAR(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,SourceBlocSize INT,FileGroupId INT, LogGroupGUID UNIQUEIDENTIFIER,DIFferentialBaseLSN NUMERIC(25,0),DIFferentialBaseGUID UNIQUEIDENTIFIER, IsReadOnly BIT,IsPresent BIT ) CREATE TABLE #header ( BackupName NVARCHAR(512),BackupDescription NVARCHAR(255),[BackupType] SMALLINT,ExpirationDate DATETIME,Compressed BIT, Position SMALLINT,DeviceType TINYINT,UserName NVARCHAR (128) ,ServerName NVARCHAR (128),DatabaseName NVARCHAR(512),DatabaseVersion INT ,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN NUMERIC(25,0),CheckPointLSN NUMERIC (25,0), DBBKLSN NUMERIC (25,0),BackupStartDate DATETIME,BackupFinishDate DATETIME ,SortOrder SMALLINT,[CodePage] INT,UnicodeLocal INT,UnicodeComp INT,CompLevl INT,softwVendId INT,SoftVersionMajor INT, SoftVersionMinor INT,SoftVersionBuild INT,MachineName NVARCHAR(128) ,flag INT,BindingId UNIQUEIDENTIFIER,RecoveryForkID UNIQUEIDENTIFIER,collation Nvarchar(128) ,FamilyGUID UNIQUEIDENTIFIER,hasbulkloggedata BIT,issnapshot BIT,isreadonly BIT ,issingleuser bit,hadbackupchecksums bit, isdamaged bit, beginslogchain bit, HasIncomplMetaData bit , isforceoffline INT,iscopyonly INT,FirstRecoveryForkID UNIQUEIDENTIFIER, forkpointlsn NUMERIC (25,0), RecoveryModel NVARCHAR(60), dIFfBaseLSN NUMERIC (25,0), dIFfbaseGUID UNIQUEIDENTIFIER ,backupTypeDesc NVARCHAR (50),BKSETGUID UNIQUEIDENTIFIER ) DECLARE @Version numeric(18,10) SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10)) -- If this is SQL2008 or higher, add more columns to support compression IF @VERSION >= 10 BEGIN ALTER TABLE #filelist ADD TDEThumbPRINT bit ALTER TABLE #header ADD CompressedBackupSize BIGINT END -- If this is SQL2012 or higher, add more columns to support contained database IF @Version >= 11 BEGIN ALTER TABLE #Header ADD containmnet bit END IF OBJECT_ID('tempdb..#CommandShell') IS NOT NULL DROP TABLE #CommandShell CREATE TABLE #CommandShell ( Line VARCHAR(1024) ) -- List all files in a directory - T-SQL parse string for date and filename -- Microsoft SQL Server command shell statement - xp_cmdshell -- these variables keep track if we changed these options, we'll change them back before finishing DECLARE @chkOLE as sql_variant DECLARE @adv_opt as sql_variant DECLARE @op_ch1 as INT DECLARE @op_ch2 as INT print '/* Ignore this commented block' set @op_ch1=0 set @op_ch2=0 select @adv_opt = value from sys.configurations where name = 'show advanced options' if @adv_opt = 0 begin EXEC sp_configure 'show advanced options',1 RECONFIGURE WITH OVERRIDE; set @op_ch1 = 1 end select @chkOLE = value from sys.configurations where name = 'xp_cmdshell' if @chkOLE = 0 begin EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE; set @op_ch2 = 1 end -- Adds CMD compatible quotation marks if there is none IF ((SELECT CHARINDEX('"',@pathname))=0) SET @pathname = '"'+@pathname+'"' -- Set the DIRECTORY to search for backups SET @CMD = 'DIR ' + @PathName + ' *.bak /A /B' -- Populate table with the files in directory INSERT INTO #CommandShell EXEC MASTER..xp_cmdshell @CMD -- Delete lines not containing a filename DELETE FROM #CommandShell WHERE Line is null -- checking if we change settings, and reverting them if @op_ch2 = 1 begin EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE WITH OVERRIDE; end if @op_ch1 = 1 begin EXEC sp_configure 'show advanced options',0 RECONFIGURE WITH OVERRIDE; end print ' Ignore this commented block*/ ' IF EXISTS (select TOP 1 (Line) from #CommandShell where Line = 'Access is denied.') BEGIN Print ' Either the SQL Server does not have access to the path, or it does not exist' print @pathname GOTO EOF END --- end CMDshell -- Removing double quotations from pathname set @pathname = REPLACE(@pathname,'"','') -- Makes sure path ends with a slash, and adds one if needed IF ((SELECT CHARINDEX('\',reverse(@pathname)))>1) SET @pathname = @pathname+'\' -- Removing Last line of table delete from #CommandShell where Line = 'File Not Found' -- First Cursor, grabs a file, and extracts the database name -- ********************************************** -- ********************************************** -- ********************************************** DECLARE LIST CURSOR FOR SELECT @pathname+Line FROM #CommandShell OPEN LIST FETCH NEXT from LIST INTo @temp3 WHILE (@@fetch_status = 0) BEGIN -- populate the tables with the BAK file info insert #header exec ('restore headeronly from disk = ''' + @temp3 + '''') insert #filelist exec ('restore filelistonly from disk = ''' + @temp3 + '''') -- Grabs DATABASE name for the BAK file select @databasename=DatabaseName from #header -- start adding size info select @bksizeTotal=@bksizeTotal+backupsize from #header set @move = ' -- ************************************************* -- Restore Script For Database ' + @databasename + ' -- ************************************************* ' if @OVERRIDE=1 set @move=@move+'ALTER DATABASE ['+@databasename+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' set @move=@move+ ' RESTORE DATABASE ['+@databasename+'] FROM DISK ='''+@temp3+''' WITH FILE = 1, ' -- Second Cursor for data files, nested. DECLARE cur CURSOR FOR SELECT LogicalName, reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) from #filelist where [type]= 'D' OPEN cur FETCH NEXT from cur INTO @temp1 ,@temp2 WHILE (@@fetch_status = 0) BEGIN -- start adding size info SELECT @DATAsizeTotal=@DATAsizeTotal+size FROM #filelist WHERE LogicalName =@temp1 -- Set the new path SET @temp2 = @DataPath+@temp2 -- If Relocate is 0, gets the current physical path and assign it to @temp2 IF @RELOCATE=0 SELECT @temp2 = PhysicalName FROM #filelist WHERE LogicalName = @temp1 -- concatenante the move portion of the restore script. SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''', ' FETCH NEXT from cur INTo @temp1 ,@temp2 END CLOSE cur DEALLOCATE cur -- Third cursor for Tlog files. Nested. DECLARE cur cursor for SELECT LogicalName, reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist WHERE [type]= 'L' OPEN cur FETCH NEXT FROM cur INTo @temp1 ,@temp2 WHILE (@@fetch_status = 0) BEGIN -- start adding size info select @TLOGsizeTotal=@TLOGsizeTotal+size from #filelist where LogicalName =@temp1 -- If Relocate is 0, gets the current physical path and assign it to @temp2 IF @RELOCATE=0 SELECT @temp2 = PhysicalName from #filelist where LogicalName = @temp1 set @temp2 = @TlogPath+@temp2 set @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''', ' FETCH NEXT from cur INTo @temp1 ,@temp2 END close cur deallocate cur print @move +' NOUNLOAD, REPLACE, STATS = 10 GO' if @OVERRIDE=1 set @move=@move+'ALTER DATABASE ['+@databasename+'] SET MULTI_USER GO' truncate table #filelist truncate table #header FETCH NEXT FROM LIST INTO @temp3 END CLOSE LIST DEALLOCATE LIST -- ********************************************** -- ********************************************** -- ********************************************** print ' /* ***********************--' Print 'Total Size Needed in all disks '+ cast (@BkSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB' print ' --' Print 'Total Size Needed for DATA files '+ cast (@DATASizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB' print ' --' Print 'Total Size Needed for TLOG files '+ cast (@TLOGSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB' print ' --' print ' *********************** */' --Clean up drop table #CommandShell drop table #filelist drop table #header -- */ EOF: