MSSQL Server : Automatically Restore DBs from backup files

Problem Statement:Many a times we need to restore multiple dbs from the backfiles on disk. We must write scripts to restore each DB. Rather use the shared script to do the automatic restore.
Solution: 

Note:Change the DB name,data file path,log file path and backup file path before using this scirpt.


BEGIN try
DECLARE curDBNames CURSOR FOR
SELECT dbname
FROM   (
--Change DB1 and Db2 with actual db names and more dbs as per you requirement
SELECT 'DB1' AS dbname
UNION
SELECT 'DB2' AS dbname) a
LEFT JOIN sys.databases d
ON a.dbname = d.NAME
WHERE  d.NAME IS NULL
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @v_DBName SYSNAME

OPEN curDBNames

FETCH NEXT FROM curDBNames INTO @v_DBName

WHILE @@Fetch_Status = 0
BEGIN
BEGIN try
SET nocount ON

DECLARE @p_strDBNameFrom           SYSNAME =@v_DBName,
@p_strDBNameTo             SYSNAME =@v_DBName,

–Change the data file,log file and backup file locations
@p_strDataFileMoveToFolder VARCHAR(255)=‘H:\MSSQL\DATA’,
@p_strLogFileMoveToFolder  VARCHAR(255)=‘O:\MSSQL\log’,
@p_strFQNBackupFileNames   NVARCHAR(max)=
‘E:\backupFileLocation\’ + @v_DBName +
‘.bak’,
@v_strDBFilename           VARCHAR(100),
@v_strDBLogFilename        VARCHAR(100),
@v_strDBDataFile           VARCHAR(100),
@v_strDBLogFile            VARCHAR(100),
@v_strExecSQL              NVARCHAR(1000),
@v_strKillConSQL           NVARCHAR(1000),
@v_strExecSQL1             NVARCHAR(1000),
@v_strMoveSQL              NVARCHAR(4000),
@v_strREPLACE              NVARCHAR(50),
@v_strTEMP                 NVARCHAR(1000),
@v_strListSQL              NVARCHAR(4000),
@v_strServerVersion        NVARCHAR(20),
@v_strFromDiskSQL          NVARCHAR(max)

SET @v_strREPLACE = ”

IF EXISTS (SELECT NAME
FROM   sys.databases
WHERE  NAME = @p_strDBNameTo)
SET @v_strREPLACE = ‘, REPLACE’

IF Object_id(‘tempdb..#Backupfiles’) IS NOT NULL
DROP TABLE #Backupfiles

SELECT *
INTO   #Backupfiles
FROM   dbo.splitstring(@p_strFQNBackupFileNames)

IF Object_id (‘tempdb..#FILE_LIST’) IS NOT NULL
BEGIN
DROP TABLE #FILE_LIST
END

CREATE TABLE #FILE_LIST
(
LogicalName          VARCHAR(64),
PhysicalName         VARCHAR(130),
[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,
TDEThumbpr           DECIMAL
)

———————–create from disk query ————————-
DECLARE curFileLIst CURSOR FOR
SELECT NAME
FROM   #Backupfiles –where [Type]=’D’
SET @v_strFromDiskSQL = ”

OPEN curFileList

FETCH NEXT FROM curFileList INTO @v_strTEMP

WHILE @@Fetch_Status = 0
BEGIN
SET @v_strFromDiskSQL = @v_strFromDiskSQL + ‘,Disk=”’ +
@v_strTEMP
+ ””

FETCH NEXT FROM curFileList INTO @v_strTEMP

PRINT @v_strFromDiskSQL
END

CLOSE curFileList

DEALLOCATE curFileList

SET @v_strFromDiskSQL= Stuff(@v_strFromDiskSQL, 1, 1, ”)

—————–create  with move query——————————
INSERT INTO #FILE_LIST
EXEC (‘RESTORE FILELISTONLY FROM  ‘ + @v_strFromDiskSQL + ”)

DECLARE curFileLIst CURSOR FOR
SELECT ‘MOVE N”’ + LogicalName + ”’ TO N”’
+ iif(type=‘L’, @p_strLogFileMoveToFolder,
@p_strDataFileMoveToFolder)
+ Replace(RIGHT(PhysicalName, Patindex(‘%\%’, Reverse(
PhysicalName)
)),
@p_strDBNameFrom, @p_strDBNameTo)
+ ””
FROM   #FILE_LIST –where [Type]=’D’
SET @v_strMoveSQL = ”

OPEN curFileList

FETCH NEXT FROM curFileList INTO @v_strTEMP

WHILE @@Fetch_Status = 0
BEGIN
SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ‘, ‘

FETCH NEXT FROM curFileList INTO @v_strTEMP
END

CLOSE curFileList

DEALLOCATE curFileList

PRINT
‘———————————————————————————————————————————–‘

PRINT ‘Restoration Started for”‘ + @p_strDBNameTo
+ ‘” database from “‘
+ @p_strFQNBackupFileNames + ‘ at ‘
+ Cast(Getdate() AS VARCHAR(30))

PRINT
‘———————————————————————————————————————————–‘

SET @v_strExecSQL = ‘RESTORE DATABASE [‘ + @p_strDBNameTo + ‘]’
SET @v_strExecSQL = @v_strExecSQL + ‘ FROM  ‘ + @v_strFromDiskSQL
+ ”
SET @v_strExecSQL = @v_strExecSQL + ‘ WITH FILE = 1,’
SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
SET @v_strExecSQL = @v_strExecSQL + ‘ NOREWIND, STATS=10,’
SET @v_strExecSQL = @v_strExecSQL + ‘ NOUNLOAD ‘
SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE

PRINT @v_strExecSQL

PRINT ‘Killing active connections to the “‘
+ @p_strDBNameTo + ‘” database’

— Create the sql to kill the active database connections
SET @v_strKillConSQL = ”

SELECT @v_strKillConSQL = @v_strKillConSQL + ‘kill ‘
+ CONVERT(CHAR(10), spid) + ‘ ‘
FROM   master.dbo.sysprocesses
WHERE  Db_name(dbid) = @p_strDBNameTo
AND DBID <> 0
AND spid <> @@spid

EXEC (@v_strKillConSQL)

EXEC sp_executesql
@v_strExecSQL

PRINT
‘———————————————————————————————————————————‘

PRINT ‘Restoration Completed FOR “‘
+ @p_strDBNameTo + ‘” database from “‘
+ @p_strFQNBackupFileNames + ‘ at ‘
+ Cast(Getdate() AS VARCHAR(30))

PRINT
‘———————————————————————————————————————————‘
END try

BEGIN catch
SELECT 1

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage,— Message text.  
1,@ErrorState — State.  
);

SELECT 2
END catch

SELECT @v_DBName

FETCH NEXT FROM curDBNames INTO @v_DBName
END
END try

BEGIN catch
SELECT 3

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— Use RAISERROR inside the CATCH block to return error  
— information about the original error that caused  
— execution to jump to the CATCH block.  
RAISERROR (@ErrorMessage,— Message text.  
1,— Severity.  
@ErrorState — State.  
);

SELECT 4
END catch

CLOSE curDBNames

DEALLOCATE curDBNames