SQL Server – How to Use Variables with RESTORE FILELISTONLY

restoresql server

Storing the result of RESTORE FILELISTONLY in a temporary table called #filepaths. 

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = 'Z:\BACKUPS\my_database_backup.bak'
END
GO

insert into #filepaths 
exec sp_restore

How can I get 'Z:\BACKUPS\my_database_backup.bak' into a variable? So that the script looks similar to this.

DECLARE @BACKUP_PATH as nvarchar(max) = 'Z:\BACKUPS\my_database_backup.bak'

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = @BACKUP_PATH
END
GO

insert into #filepaths 
exec sp_restore

Thanks,
Craig

Best Answer

I think you have things just a little messed up. How about:

CREATE PROCEDURE dbo.usp_restore
  @backup_path NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    RESTORE FILELISTONLY FROM DISK = @backup_path;
END
GO

Creating the procedure should be completely separate from any individual invocation. So in a different window, or two weeks later, you should be able to do this (again, completely separately from the creation of the stored procedure):

DECLARE @bp NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

INSERT #filepaths EXEC dbo.usp_restore @backup_path = @bp;

Some other commentary you should look into:

If you don't want a stored procedure to help with this (why not?), sure you can do this in a much messier way. Assuming you already have a #temp table, #t, created with the right columns and data types:

DECLARE @path NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

DECLARE @sql NVARCHAR(MAX) = N'RESTORE FILELISTONLY FROM DISK = @path;';

INSERT #t EXEC master.sys.sp_executesql @sql, N'@path NVARCHAR(MAX)', @path;

But still, why not just have a stored procedure ready when you need it, instead of porting around all this bulky code?

Related Question