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

%d bloggers like this: