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

Query to List of backup's by Database

Below query gives the list of all the backup's taken on the server.


SELECT sysdb.name DBName, bkup.description, physical_device_name , bkup.backup_finish_date,

case

when type='D' then '** FULL **'

when type='I' then 'DIFFERENTIAL'

when type='L' then 'LOG'

end as Backup_Type,

(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',

ceiling(bkup.backup_size /1048576) as 'Size Meg' ,

cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',

server_name, sysdb.crdate

, first_lsn, last_lsn, checkpoint_lsn

FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name

left join msdb.dbo.backupmediafamily Fam on fam.media_set_id=bkup.media_set_id

where backup_finish_date > DATEADD(DAY, -5, (getdate())) -- Last 5 days

--AND sysdb.name = DB_NAME'

-- and bkup.backup_finish_date>='11/02/2017'

ORDER BY sysdb.name, bkup.backup_finish_date desc