Home > PASS, SQL Server, T-SQL > sp_AutomateDBRestore – Now With Table-Valued Parameters!

sp_AutomateDBRestore – Now With Table-Valued Parameters!

After several weeks of procrastination, I have finished the latest version of sp_AutomateDBRestore. The previous versions of the procedure built the restore statements using sys.database_files for the restored database, msdb.dbp.backupset, and msdb.dbo.backupmediafamily. This process works great if the instance is up and running, but if the instance is unavailable it won’t work.

To handle this circumstance, I have added a table-valued parameter to the procedure allowing a list of backup files and backup file types to be passed in. This way, you can generate a restore statement as long as you have the backup files available. The proc uses RESTORE FILELISTONLY and RESTORE HEADERONLY statements to retrieve the physical and logical file names. Additionally, I’ve added parameters @backupfilepath and @logfilepath to allow a restore of the database to a different physical file than the source database. Here is the code to create a user-defined table type, followed by a call using the new parameters:

USE [master]
GO

CREATE TYPE [dbo].[backupfiletype] AS TABLE(
	[backupfilename] [varchar](255) NULL,
	[backupfiletype] [varchar](30) NULL
)
GO
DECLARE @backuptvp AS backupfiletype

INSERT INTO @backuptvp
VALUES
('C:\Backup\WIN-MK86UPCRGFF\multifile\FULL\WIN-MK86UPCRGFF_multifile_FULL_20140513_091120.bak','FULL'),
('C:\Backup\WIN-MK86UPCRGFF\multifile\LOG\WIN-MK86UPCRGFF_multifile_LOG_20140513_091135.trn','LOG'),
('C:\Backup\WIN-MK86UPCRGFF\multifile\LOG\WIN-MK86UPCRGFF_multifile_LOG_20140513_091203.trn','LOG'),
('C:\Backup\WIN-MK86UPCRGFF\multifile\LOG\WIN-MK86UPCRGFF_multifile_LOG_20140513_091354.trn','LOG')


DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_automateDBRestores]
		@sourcedbname = multifile,
		@restoredbname = multifile,
		@droprestoredb = 0,
		@noexec = 1,
		@datafilepath = 'C:\testrestore\',
		@logfilepath = 'C:\testrestore\',
		@TVP = @backuptvp

SELECT	'Return Value' = @return_value

GO

You can find the code for the updated procedure at my Presentation Slides and Scripts page under Wisconsin SQL Server User Group. As usual, feel free to leave any questions or comments in the comments section for the post.

Categories: PASS, SQL Server, T-SQL Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: