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: