Restore Script

A DBA’s main responsibility is to protect the organization’s databases and minimize data loss from any kind of disaster(nature\human\corruption). While there are several ways to achieve this,one common solution is database backups.Database backups are crucial to any organization and it is very important to have a defined RTO and RPO objectives before scheduling backups.It is equally important to verify that backup plan meets the RTO/RPO objectives and this can be done by restoring the backups. This also ensures the backups are,in fact,restoreable. We can validate backups by doing Restore VerifyOnly From Disk = 'BackupFilePath.bak'. Unfortunately, this will not provide complete guarantee on backup restorabality. The only way,we can make sure is by actually restoring the backups and this way, we validate not only the restorability of the backups but also confirm whether the backup\restore plan satisfies RTO/RPO objectives.

So,In order to accomplish this, I made a stored procedure that takes InstanceName, DatabaseName, DataFilePath, LogFilePath,BackupFromDate and BackupToDate as inputs and restores the database.The script can:
1.Restore only database level backups(Full/Diff/TLog).
2.Restore striped backups.
3.Restore latest full/diff/Tlog backups,if dates are supplied,it will restore backups taken within that timeframe.
4.Restores database by appending the Instance Name to the database name[InstanceName_DBName].
5.Restores all the files to one directory.
6.Restores database with multiple filegroups/files.
7.check if all the backup files exist,if not,it will restore only up to the available files.
8.Restore by replacing existing db,if @replace parameter is set to 1.

Scope for Future Improvements:
1.Ability to Restore filegroups and files.
2.Better Error Handling
3.Ability to restore on to multiple drives, as needed.
Syntax:
1.This replaces the existing restored database:
Exec usp_restoredb @instanceName = 'Anulu',@DbName= 'TlImports',@DbDataFilePath='G:\Data\',
@DbLogFilePath = 'F:\Log\',@BackupFromDate = null,@BackupToDate = null,@Replace=1
2.This simply restores the database(does not replace db)
Exec usp_restoredb @instanceName = 'Anulu',@DbName= 'SQL_DW',@DbDataFilePath='G:\Data\',
@DbLogFilePath = 'F:\Log\',@BackupFromDate = null,@BackupToDate = null,@Replace=0

1.The main procedure requires the below scalar function to check whether the backup file exists or not:
Create Function [dbo].[fnc_CheckFilePath](@FileName varchar(300))
RETURNS int
AS
BEGIN
Declare @File_Exists int
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
RETURN @File_Exists
END

2. Below is the main procedure that restores the database.
Create Procedure [dbo].[usp_RestoreDB] (@InstanceName varchar(200),@DbName varchar(200),@DbDataFilePath varchar(20),@DbLogFilePath varchar(20),
@BackupFromDate Datetime,@BackupToDate Datetime,@Replace bit)
AS
BEGIN
SET XACT_ABORT ON
/***Set the restored database name**/
Declare @RestoreDB varchar(200)
Set @RestoreDB = replace(@InstanceName,'\','_')+'_'+@DBNAME
IF (Select 1 FROM Master.dbo.Sysdatabases where name = @RestoreDB)=1 and (@Replace=0)
BEGIN
RAISERROR('Database already exists on the server.If you want to replace it,set @Replace parameter',20,1)WITH LOG
END
/***This Code below gets the backup history of the database from it's latest Full Backup and puts in a table variable************/
DECLARE @Temp TABLE(BackupSetID BIGINT,DatabaseName VARCHAR(200),PathDir VARCHAR(200),Media_set_id bigint,
BackupName varchar(200),BackupFinishDate datetime,[Type] char(1),Backup_Size bigint,Recovery_Model varchar(20),Position int,FileExists int)

DECLARE @Query NVARCHAR(max),@sqlParm nvarchar(max)
SET @sqlParm=N'@DbName varchar(200),@BackupFromDate datetime,@BackupToDate datetime'

SET @Query = 'select A.backup_set_id,A.database_Name,B.Physical_device_name,A.media_set_id,A.name,A.backup_finish_date,
A.type,A.backup_size,A.recovery_model,position'+
+' from '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupset A INNER JOIN '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupmediafamily B on A.Media_set_id = B.Media_set_id'+
+' where database_name =@DbName and is_copy_only = 0 and backup_set_id >='+
+' (select max(backup_set_id) from '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupset where database_name =@DbName and type =''D'' and is_copy_only=0'
+' and Backup_finish_Date between coalesce(@BackupFromDate,''01/01/1900'') and coalesce(@BackupToDate,getdate()))'

Insert into @Temp(BackupSetID,DatabaseName,PathDir,Media_set_id,BackupName,BackupFinishDate,[Type],Backup_Size,Recovery_Model,Position)
EXECUTE SP_ExecuteSQL @Query,@sqlParm,@dbname=@dbname,@BackupFromDate=@BackupFromDate,@BackupToDate=@BackupToDate
Update @Temp SET FileExists=dbo.fnc_CheckFilePath(PathDir)
/***DELETING THE BACKUPS NOT REQUIRED FOR RESTORE**/
DELETE FROM @TEMP WHERE FileExists=0 and Type='I'
DELETE FROM @TEMP WHERE BACKUPSETID =(SELECT min(BackupSetID) FROM @TEMP WHERE FileExists=0 and Type='L') and Type='L'
DELETE FROM @TEMP WHERE BACKUPSETID <(SELECT MAX(BACKUPSETID) FROM @TEMP WHERE TYPE = 'I') AND TYPE = 'L'
/********* This code sets the logical name and filepath for both data and log files*********************/
DECLARE @DataFile_LogicalName NVARCHAR(200),@LogFile_LogicalName NVARCHAR(200),@FullBackupID1 BIGINT,@RestoreLocations VARCHAR(4000)
DECLARE @SQL1 NVARCHAR(MAX),@SQL2 NVARCHAR(MAX),@ParmDefinition NVARCHAR(200),@ParmDefinition1 NVARCHAR(200)
DECLARE @BackupLocation VARCHAR(500),@FullBackupId VARCHAR(20),@FULLRecoveryStatus VARCHAR(20),@FullPosition VARCHAR(4)
SELECT @FullBackupId = BackupSetId,@FullPosition=Position FROM @Temp WHERE TYPE = 'D' ORDER BY BackupSetId DESC

DECLARE @Temp2 TABLE (sno INT,pathdir VARCHAR(300))
SET @SQL2 ='select 1,''MOVE N''''''+Logical_Name+'''''' TO N''''''+
case when file_type = ''L'' then '''+@DbLogFilePath +'''else '''+@DbDataFilePath +''' END
+'''+REPLACE(@InstanceName,'\','_')+ '''+Reverse(Substring(REVERSE(Physical_name),1,charindex(''\'',
REVERSE(Physical_name),1)-1))+''''''''
from '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupfile where backup_set_id ='+ @FullBackupID

INSERT INTO @Temp2
EXEC(@SQL2)

SELECT DISTINCT @RestoreLocations =
STUFF((SELECT ','+PathDir
FROM @Temp2 T1
WHERE T1.sno=T2.sno
FOR XML PATH('')),1,1,'') FROM @Temp2 T2
/***********************Code below does the FULL backup Restoration*************************************************************/
IF (@FullBackupID IS NOT NULL)
BEGIN TRY
-- this is to concatenate split backups into one string---
SELECT @BackupLocation =
STUFF((SELECT ',Disk ='''+pathDir+''''
FROM @Temp T1 where T1.BackupSetId=T2.BackupSetId and Type = 'D' for xml path('')),1,1,'') FRom @Temp T2 where Type = 'D'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'RESTORE DATABASE '+replace(@InstanceName,'\','_')+'_'+@DBNAME+ ' FROM '+@BackupLocation+
' WITH FILE ='+@FullPosition+','+@RestoreLocations+',NORECOVERY,NOUNLOAD'+case when @Replace=1 then ',REPLACE' end
EXECUTE sp_ExecuteSQL @SQL
END TRY

BEGIN CATCH
IF @@Error<>0
RAISERROR('The backup was not able to restore. Please check and run again.',20,1)WITH LOG
END CATCH
-------------------------------------Differential Restores----------------------------------------------------------------------------------------------------------
IF (Select max(backupsetID) from @Temp A where Type = 'I') is not null
BEGIN
/* This Code will restore the Latest Differential Backup taken after restored Full Backup for the database*/
DECLARE @DiffBackupLocation VARCHAR(MAX),@DiffBackupID BIGINT,@DiffSQL NVARCHAR(MAX),@DiffPosition VARCHAR(2)
-- Gets the Latest Differential Backup(taken after the latest Full Backup).
SELECT TOP 1 @DiffBackupId = A.BackupSetId,@DiffPosition =Position From @Temp A Where Type = 'I'

BEGIN TRY
-- this is to concatenate split backups into one string---
SELECT DISTINCT @DiffBackupLocation =
STUFF((SELECT ',Disk ='''+pathDir+''''
FROM @Temp T1 where T1.BackupSetId=T2.BackupSetId and Type = 'I' for xml path('')),1,1,'') FRom @Temp T2 where Type = 'I'

SET @DiffSQL = 'RESTORE DATABASE '+@RestoreDB+ ' FROM '+@DiffBackUpLocation+' WITH FILE ='+@DiffPosition+',NORECOVERY,NOUNLOAD'
EXECUTE Sp_ExecuteSQL @DiffSQL
END TRY

BEGIN CATCH
IF @@Error<>0
RAISERROR('The backup was not able to restored. Please check and run again.',20,1)WITH LOG
END CATCH
END
--------------------------------------Transaction Log Backups--------------------------------------------------------------
-- checks to see if there are any Tlog backups to be restored(taken after the latest Full and/or Differential Backups).
IF (Select Max(backupsetID) from @Temp A where Type = 'L') is not null
Begin
/*This code belows restores all the transaction logs taken after the lastest FULL and differential backups. */
Declare @LogBackupLocation varchar(200),@LogBackupID bigint,@LogSQL nvarchar(max),@LogRecoveryStatus varchar(20),@LogPosition varchar(4)
--Using Cursor to get the list of all the Tlogs to be restored(taken after the latest Full and/or Differential Backups).
Declare LogBackupID_Cursor CURSOR FOR
Select A.BackupSetId
from @Temp A where Type = 'L' order by BackupSetId asc

OPEN LogBackupID_Cursor
Fetch NEXT FROM LogBackupID_Cursor intO @LogBackupID

WHILE @@Fetch_status = 0
Begin

Select @LogPosition = Position From @Temp where BackupSetID=@LogBackupID
-- this is to concatenate split backups into one string---
SELECT DISTINCT @LogBackupLocation =
STUFF((SELECT ',Disk ='''+pathDir+''''
FROM @Temp T1 where T1.BackupSetId=T2.BackupSetId and Type = 'L' for xml path('')),1,1,'') FRom @Temp T2 where Type = 'L' and BackupSetID=@LogBackupID

BEGIN TRY
SET @LogSQL = 'RESTORE DATABASE '+@RestoreDB+ ' FROM '+@LogBackUpLocation+
' WITH FILE ='+@LogPosition+',NORECOVERY,NOUNLOAD'
EXECUTE Sp_Executesql @LogSQL
END TRY

BEGIN CATCH
IF @@Error<>0
RAISERROR('The backup was not able to restored. Please check and run again.',20,1)WITH LOG
END CATCH
Fetch Next from LogBackupId_Cursor into @LogBackupID
End
Close LogBackupID_Cursor
Deallocate LogBackupID_Cursor
End
/*********Apply Final Recovery on the Database******************/
IF @@ERROR=0 and (@FullBackupId is not null)
BEGIN
DECLARE @FinalSQL NVARCHAR(200)
SET @FinalSQL = 'RESTORE database '+@RestoreDB +' with recovery'
EXECUTE sp_executesql @FinalSQL
END

IF (Select min(BackupSetID) from @Temp where Type = 'D') is null and (@BackupFromDate is not null and @BackupToDate is not null)
BEGIN
RAISERROR('No full backup is available for the time range selected to restore.',20,1)WITH LOG
END

IF (Select min(BackupSetID) from @Temp where Type = 'D') is null and (@BackupFromDate is null and @BackupToDate is null)
BEGIN
RAISERROR('No full backup is available to restore.',20,1)WITH LOG
END
END