How to Restore Databases from full backup files from a folder to SQL Server Instance - SQL Server

Scenario:

You are working as SQL Server DBA or developer, you need to prepare script that should read the backup files from a folder and then restore as database to SQL Server Instance.

Following things to consider for  below scripts.

  1. The backup files are in sitting in root folder, if there are sub folders you have to make change to script to include those files.
  2. The script consider that there is only one Data file for each database. If you have more than one Data File, you need to make a change to script.
  3. You need to provide Data File location and Log file location to restore database from backup file.
  4. If Database is already there, it will not overwrite. You can change Restore statement to include overwrite clause if you like.


You can comment out the EXEC(@SQLRestore) line, so it will not start restore. The print statement is there that will print out the sql scripts for you so you can run them manually if you like.


 --Provide the folder path where backups are present
 DECLARE @BackupFolder VARCHAR(128)
 SET @BackupFolder='C:\Backup\'

 --Provide the data and log files paths
 --where you would like to restore databases

Declare @DataFilesPath VARCHAR(128)
Declare @LogFilesPath VARCHAR(128)

SET @DataFilesPath='C:\Backup\Data\'
SET @LogFilesPath='C:\Backup\Log\'

--Get Backup files informaiton in temp table
  IF OBJECT_ID('tempdb..#BackupFileList') IS NOT NULL
    DROP TABLE #BackupFileList

--Drop temp table if exits
IF OBJECT_ID('tempdb..#RESTOREFILELISTONLY') IS NOT NULL
    DROP TABLE #RESTOREFILELISTONLY

 IF OBJECT_ID('tempdb..#RestoreHeaderOnly') IS NOT NULL
    DROP TABLE #RestoreHeaderOnly

  CREATE TABLE #BackupFileList (
    Id int identity(1,1),
    BackupFile nvarchar(255),
    Depth smallint,
    FileFlag bit)



--Store Backup information
Create Table #RestoreHeaderOnly(
BackupName nvarchar(128) ,
BackupDescription nvarchar(255) ,
BackupType smallint ,
ExpirationDate datetime ,
Compressed bit ,
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) NULL ,
RecoveryModel nvarchar(60) ,
DifferentialBaseLSN  numeric(25,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
BackupTypeDescription nvarchar(60) ,
BackupSetGUID uniqueidentifier NULL ,
CompressedBackupSize bigint ,
containment tinyint not NULL ,
KeyAlgorithm nvarchar(32) ,
EncryptorThumbprint varbinary(20) ,
EncryptorType nvarchar(32))


--Create Temp Table for DB files
Create table #RESTOREFILELISTONLY(
LogicalName NVARCHAR(128),
PhysicalName NVARCHAR(260),
Type CHAR(1),
FileGroupName NVARCHAR(128),
Size numeric(20,0),
MaxSize numeric(20,0),
Field bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueId uniqueidentifier,
ReadonlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes BigInt,
SourceBlockSize Int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumprint varbinary(32))

--Save backup files into temp table 
   INSERT INTO #BackupFileList (BackupFile,Depth,FileFlag)
   EXEC xp_dirtree @BackupFolder, 10, 1

   --Select * FROM #BackupFileList

   --Use Cursor to loop throught backups files and restore
Declare @BackupFile VARCHAR(500)

   DECLARE Cur CURSOR FOR
  SELECT BackupFile from #BackupFileList
  where fileflag=1

OPEN Cur
FETCH Next FROM Cur INTO @BackupFile
WHILE @@FETCH_STATUS = 0
  BEGIN

    Truncate table #RESTOREFILELISTONLY

--Insert data from RESTORE FILELISTONLY 
insert into #RESTOREFILELISTONLY
EXEC('RESTORE FILELISTONLY FROM DISK = '''+@BackupFolder+@BackupFile+'''')

 insert into #RestoreHeaderOnly
  EXEC('RESTORE HEADERONLY FROM DISK = '''+@BackupFolder+@BackupFile+'''')
--Select * From #RESTOREFILELISTONLY

Declare @LogFileName NVARCHAR(128)=NULL
Declare @DataFileName NVARCHAr(128)=NULL
Declare @DBName NVARCHAR(128)=NULL
--We are considering we have a log and single data file
SET @LogFileName=(Select logicalName from #RESTOREFILELISTONLY where type='L')
SET @DataFileName=(Select logicalName from #RESTOREFILELISTONLY where type='D')
SET @DBName=(Select DatabaseName from #RestoreHeaderOnly)


--Prepare Restore Statement
Declare @SQLRestore NVARCHAR(MAX)
 SET @SQLRestore='RESTORE DATABASE "' +@DBName+'"
  FROM DISK='''+@BackupFolder+@BackupFile+'''
WITH
MOVE '''+@DataFileName+''' TO '''+@DataFilesPath+@DataFileName+'.mdf'',
MOVE '''+@LogFileName+''' TO '''+@LogFilesPath+@LogFileName+'.ldf'''

--Execute SQL to Restore DBs
Print @SQLRestore
EXEC(@SQLRestore)

Truncate table #RestoreHeaderOnly
 FETCH Next FROM Cur INTO @BackupFile
  END
CLOSE Cur
DEALLOCATE Cur


I executed above code in my environment and it was able to restore backup files from a folder to my SQL Server Instance.

Note: Always test the scripts in Development environment before you execute in QA,UAT and Production.