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:

 

Output:

Check database to make sure that it is online.

Stored Procedure Code: Restore Automation Procedure code:

restore_automation_sql-server

 

Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , , , , | 3 Comments

Leave a Reply

3 Comments on "Automate database restore procedure SQL Server"

Notify of
avatar
Sort by:   newest | oldest | most voted
pruthvi
Guest
/*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 »
Seshatheri
Guest
Hi Udayarumilli, I am getting a below error when i am, trying to create a stores procedure. Can you please help me Msg 102, Level 15, State 1, Procedure DB_Restore, Line 31 Incorrect syntax near ‘ ‘. Msg 102, Level 15, State 1, Procedure DB_Restore, Line 32 Incorrect syntax near ‘ ‘. Msg 102, Level 15, State 1, Procedure DB_Restore, Line 83 Incorrect syntax near ‘ ‘. Msg 102, Level 15, State 1, Procedure DB_Restore, Line 135 Incorrect syntax near ‘ ‘. Msg 102, Level 15, State 1, Procedure DB_Restore, Line 160 Incorrect syntax near ‘ ‘. Msg 102, Level 15, State 1, Procedure DB_Restore,… Read more »
wpDiscuz