5

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

8

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?

| improve this answer | |
  • Hello Aaron, I'm only using a CREATE PROCEDURE because I don't know how to get the contents of RESTORE FILELISTONLY into a table without an SP. Is there a way to avoid using a stored procedure all together? – Craig Efrein Oct 31 '14 at 8:42
-1

When your script hits that first GO, your batch ends and your variable goes out of scope.

From the GO docs:

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

So when the second batch is sent over (where you use RESTORE FILELISTONLY), those variables and their values are not included.

| improve this answer | |
  • 3
    A small link to the documentation would be very nice. – dezso Oct 25 '16 at 21:18
  • @From Review – Philᵀᴹ I presented an answer. It may be right or wrong, but it was definitely an answer. Can you explain? – Ed Norris Oct 26 '16 at 12:35
-3

I think you can only do this with dynamic SQL. Replace the body of your SP with the following:

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

  DECLARE @SQL NVARCHAR(MAX)

  SQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @BACKUP_PATH  + '''

  EXEC SP_ExecuteSql @SQLToExecute

END
GO

insert into #filepaths 
exec sp_restore
| improve this answer | |
  • 2
    No, dynamic SQL is not necessary, and this still doesn't work (that variable is not in scope inside the procedure, and not just because of the go). – Aaron Bertrand Oct 30 '14 at 17:59
  • + it has a bad practice of avoiding the schema prefix. I am not downvoting though .. just highlighting obvious stuff. – Kin Shah Oct 30 '14 at 19:33
  • I need to stop resorting to dynamic SQL by default – James Anderson Oct 31 '14 at 8:18
  • As it turns out, dynamic SQL might be required here, but not for the reason you thought. :-) – Aaron Bertrand Oct 31 '14 at 14:20
  • Is that for the 2nd question? – James Anderson Oct 31 '14 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.