Automate database restore procedure SQL Server

Automate database restore procedure SQL Server

Automate database restore sql server

Make sure that the stored procedure “[dbo].[DB_Restore]” is available at Target Instance master database.

 Connect to target server instance and open a new query window from SSMS

Why Database Restore Automation?

  • It reduce the manual efforts in gathering file details.
  • Mostly restores happens in cross environments in this case this procedure can handle the file locations and naming conventions.
  • We can see the complete error details and also it verify the space details before starting the actual operation.

Get the backup file location and database name to which the backup needs to be restored as and execute the stored procedure as below:



Check database to make sure that it is online.

Stored Procedure Code: Restore Automation Procedure code:



Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , , , , | 1 Comment

Leave a Reply

1 Comment on "Automate database restore procedure SQL Server"

Notify of
Sort by:   newest | oldest | most voted
/*creating a procedure in master database to restore the required database*/ create procedure dba_restore @backupFile varchar(max),@dbName varchar(256) as declare @headers table ( LogicalName nvarchar(128) ,PhysicalName nvarchar(260) ,Type char(1) ,FileGroupName nvarchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0), FileId tinyint, CreateLSN numeric(25,0), DropLSN numeric(25, 0), UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0), ReadWriteLSN numeric(25,0), BackupSizeInBytes bigint, SourceBlockSize int, FileGroupId int, LogGroupGUID uniqueidentifier, DifferentialBaseLSN numeric(25,0), DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit, TDEThumbprint varbinary(32) ) insert into @headers exec(‘restore filelistonly from disk = ”’+ @backupFile +””); declare @h1count int=(select COUNT(* ) from @headers ) declare @h2count int=(select COUNT(*) from sys.master_files where database_id=DB_ID(@dbName)) if (@h1count=@h2count) begin declare @temp table(query varchar… Read more »