Script to restore the databases from backup folder

Below script is to restore the databases from backup folder. Backup folder has subfolder for each database and subfolder contans the backup file.

DECLARE @BackupDirectory SYSNAME = 'G:\SQLBackups\'  -- Backup folder

IF OBJECT_ID('tempdb..#DirTree') IS NOT NULL

DROP TABLE #DirTree

CREATE TABLE #DirTree (

Id int identity(1,1),

SubDirectory nvarchar(255),

Depth smallint,

FileFlag bit,

ParentDirectoryID int

)

INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)

EXEC master..xp_dirtree @BackupDirectory, 10, 1

UPDATE #DirTree

SET ParentDirectoryID = (

SELECT MAX(Id) FROM #DirTree d2

WHERE Depth = d.Depth - 1 AND d2.Id < d.Id

)

FROM #DirTree d

DECLARE

@ID INT,

@BackupFile VARCHAR(MAX),

@Depth TINYINT,

@FileFlag BIT,

@ParentDirectoryID INT,

@wkSubParentDirectoryID INT,

@wkSubDirectory VARCHAR(MAX)

DECLARE @BackupFiles TABLE

(

FileNamePath VARCHAR(MAX),

TransLogFlag BIT,

BackupFile VARCHAR(MAX),

DatabaseName VARCHAR(MAX)

)

DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY FOR

SELECT * FROM #DirTree WHERE FileFlag = 1

OPEN FileCursor

FETCH NEXT FROM FileCursor INTO

@ID,

@BackupFile,

@Depth,

@FileFlag,


@ParentDirectoryID
SET @wkSubParentDirectoryID = @ParentDirectoryID

WHILE @@FETCH_STATUS = 0

BEGIN

--loop to generate path in reverse, starting with backup file then prefixing subfolders in a loop

WHILE @wkSubParentDirectoryID IS NOT NULL

BEGIN

SELECT @wkSubDirectory = SubDirectory, @wkSubParentDirectoryID = ParentDirectoryID

FROM #DirTree

WHERE ID = @wkSubParentDirectoryID

SELECT @BackupFile = @wkSubDirectory + '\' + @BackupFile

END

--no more subfolders in loop so now prefix the root backup folder

SELECT @BackupFile = @BackupDirectory + @BackupFile

--put backupfile into a table and then later work out which ones are log and full backups

INSERT INTO @BackupFiles (FileNamePath,DatabaseName) VALUES(@BackupFile,@wkSubDirectory)



declare @cmd nvarchar(max)

declare @PhysicalDBfolder varchar(100)

declare @LogDBfolder varchar(100)

declare @PhysicalDB varchar(100)

declare @LogDB varchar(100)

set @PhysicalDBfolder='E:\SQLData\'+@wkSubDirectory+'.mdf'

set @LogDBfolder='F:\SQLLogs\'+@wkSubDirectory+'_log.ldf'

set @PhysicalDB=@wkSubDirectory

set @LogDB=@wkSubDirectory+'_log'

--Restore Command

SET @cmd = 'RESTORE DATABASE ['+ @wkSubDirectory +'] FROM DISK = '''+ @BackupFile +''' WITH '

SET @cmd = @cmd + 'MOVE ''' + @PhysicalDB + '''' + ' TO '''+@PhysicalDBfolder+''''

SET @cmd = @cmd + ' ,MOVE ''' + @LogDB + '''' + ' TO '''+@LogDBfolder+''''



--select @cmd
--executing restore commandExec sp_executesql @cmd

FETCH NEXT FROM FileCursor INTO

@ID,

@BackupFile,

@Depth,

@FileFlag,


@ParentDirectoryID
SET @wkSubParentDirectoryID = @ParentDirectoryID

END



--select * from @BackupFiles
  CLOSE FileCursor

DEALLOCATE FileCursor