SET NOCOUNT ON
DECLARE
@foldername Varchar (255) = 'D:\MSSQL\MSSQLSERVER', --path where .bak files are placed
@DataFileLocation Varchar(255) = 'E:\MSSQL' , -- Optional, Specifies the folder for database data files. If not specified, data files are restored to the default data file location
@LogFileLocation Varchar(255) = 'E:\MSSQL' , -- Optional, Specifies the folder for all database log files. If not specified, log files are restored to the default log file location
@ExcludeDbs Varchar(255) = '(''master'' , ''model'' , ''msdb'')' , -- Databases do not wish to restore
@RestoreDbs Varchar(255) = '' -- Optional, by default restores all databases other than those mentioned in ExcludeDbs list, can mention any particular database needs to be restored like '(''Database1'', ''Database2'')'
DECLARE @keysValueToSearch NVARCHAR(4000) = @foldername +'\', --Nothing to change here
@untilThisCharAppears NVARCHAR(4000) = '_backup' --enter a word or character upto which DB name has to be picked from backup file name
DECLARE @dbname Varchar(100) ,
@physicalpath Varchar(500) ,
@BackupDate Date ,
@cmd nvarchar(max) ,
@logicalname Varchar(255) ,
@PhysicalFileName Varchar(max) ,
@type Varchar(5),
@diff nvarchar(max)
-- If data file location is not specified then data files will be restored to default data file location.
IF @DataFileLocation IS NULL
SELECT @DataFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 1
-- If log file location is not specified then log files will be restored to default log file location.
IF @LogFileLocation IS NULL
SELECT @LogFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'mastlog.ldf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 2
print @datafilelocation
print @LogFileLocation
BEGIN TRY
drop table #filelist
end try
begin catch
end catch
CREATE TABLE #filelist (
LogicalName VARCHAR(255),
PhysicalName VARCHAR(500),
[Type] VARCHAR(1),
FileGroupName VARCHAR(64),
Size DECIMAL(20, 0),
MaxSize DECIMAL(25,0),
FileID bigint,
CreateLSN DECIMAL(25,0),
DropLSN DECIMAL(25,0),
UniqueID UNIQUEIDENTIFIER,
ReadOnlyLSN DECIMAL(25,0),
ReadWriteLSN DECIMAL(25,0),
BackupSizeInBytes DECIMAL(25,0),
SourceBlockSize INT,
filegroupid INT,
loggroupguid UNIQUEIDENTIFIER,
differentialbaseLSN DECIMAL(25,0),
differentialbaseGUID UNIQUEIDENTIFIER,
isreadonly BIT,
ispresent BIT ,
TDEThumbprint Varchar(255),
SnapshotUrl Varchar (255))
--To get DB name from physicalfilepath
Declare @backuppathstring varchar (500) = @foldername
--Touch here
DECLARE @keysValueToSearchPattern NVARCHAR(4000) = '%' + @keysValueToSearch + '%'
BEGIN TRY
drop table #backuplist
end try
begin catch
end catch
CREATE TABLE #backuplist
(
DBname varchar(100),
physicalpath varchar (500)
)
BEGIN TRY
drop table #DirectoryTree
end try
begin catch
end catch
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @foldername,1,1;
With CTE AS
(
SELECT @foldername +'\' +subdirectory as physicalpath FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.BAK'
)
Insert into #backuplist (DBname,physicalpath)
SELECT SUBSTRING(
physicalpath,
PATINDEX(@keysValueToSearchPattern, physicalpath) + LEN(@keysValueToSearch),
CHARINDEX(
@untilThisCharAppears,
physicalpath,
PATINDEX(@keysValueToSearchPattern, physicalpath) + LEN(@keysValueToSearch)
) -(PATINDEX(@keysValueToSearchPattern, physicalpath) + LEN(@keysValueToSearch))
)
as DBName, physicalpath
FROM CTE
DECLARE restore_db CURSOR FOR
SELECT DBname, physicalpath
FROM #backuplist
OPEN restore_db
FETCH NEXT FROM restore_db INTO @dbname , @physicalpath
WHILE @@FETCH_STATUS = 0
BEGIN
 
--Check database to be restored is already there in this server, if yes then just restore with replace.
IF EXISTS (SELECT * FROM sys.databases WHERE name = @dbname)
BEGIN
-- If database is not already there then go through the filelist and move to appropriate locations.
SET @cmd = 'RESTORE FILELISTONLY FROM DISK= '''+ @physicalpath +''''
INSERT INTO #filelist
EXEC (@cmd)
--select * from #filelist
SET @cmd = 'RESTORE DATABASE ['+ @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 10 , Replace, '
print @cmd;
DECLARE file_list cursor for
SELECT LogicalName, PhysicalName, Type FROM #filelist ORDER BY type
OPEN file_list
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
WHILE @@fetch_status = 0
BEGIN
-- If it is data file move to data file location.
IF @type = 'D'
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @DataFileLocation +'\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''','
ELSE
-- Log files move to log file location.
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @LogFileLocation + '\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''''
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
END
CLOSE file_list
DEALLOCATE file_list
truncate table #filelist
Exec sp_executesql @cmd
print @cmd;
END
ELSE
BEGIN
-- If database is not already there then go through the filelist and move to appropriate locations.
SET @cmd = 'RESTORE FILELISTONLY FROM DISK= '''+ @physicalpath +''''
INSERT INTO #filelist
EXEC (@cmd)
--select * from #filelist
SET @cmd = 'RESTORE DATABASE ['+ @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 10 , '
print @cmd;
DECLARE file_list cursor for
SELECT LogicalName, PhysicalName, Type FROM #filelist ORDER BY type
OPEN file_list
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
WHILE @@fetch_status = 0
BEGIN
-- If it is data file move to data file location.
IF @type = 'D'
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @DataFileLocation +'\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''','
ELSE
-- Log files move to log file location.
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @LogFileLocation + '\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''''
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
END
CLOSE file_list
DEALLOCATE file_list
truncate table #filelist
Exec sp_executesql @cmd
print @cmd;
END
FETCH NEXT FROM restore_db INTO @dbname , @physicalpath
END
CLOSE restore_db
DEALLOCATE restore_db