Restore by SQL-Script / wait for restore to finish

Question

In my script for restoring databases, I came to another problem. After restoring a database I want to edit some tables of it. So after the common restore query:

RESTORE DATABASE @DBname
FROM DISK = @BackupFileLocation
GO

I want to edit the mentioned tables. The problem is that I need to use some variables for editing the tables after the restore, that I declare before it (I need them there to). And it seems that GO kind of resets all variables so I can't use them afterwards. But if I try it without GO, it jumps over the restore query and says that the databases I want to edit, don't exist.

---

My Question: Is there another way to wait for the restore to finish until it continues editing the tables?

Answer 1

Here’s one way: set up a temp table (#table, not @variable), populate it with your values, then set your variables to these values. For example:

--  This will drop the temp table if it already exists.
--  Essential for repetitive testing!
IF object_id('tempdb.dbo.#Foo') is not null
    DROP TABLE #Foo
GO

CREATE TABLE #Foo
 (
   DBName              varchar(100)   not null
  ,BackupFileLocation  varchar(1000)  not null
 )

INSERT #Foo values ('MyDatabase', 'C:\SQL_Backups\SampleDBs')

DECLARE
  @DBname              varchar(100)
 ,@BackupFileLocation  varchar(1000)

SELECT
   @DBname             = DBName
  ,@BackupFileLocation = BackupFileLocation
 from #Foo


PRINT '--  Before restore  --------------------'
PRINT @DBname
PRINT @BackupFileLocation


--  Note: I did not test this statement
RESTORE DATABASE @DBname
 FROM DISK = @BackupFileLocation

GO

DECLARE
  @DBname              varchar(100)
 ,@BackupFileLocation  varchar(1000)

SELECT
   @DBname             = DBName
  ,@BackupFileLocation = BackupFileLocation
 from #Foo

PRINT ''
PRINT '--  After restore  --------------------'
PRINT @DBname
PRINT @BackupFileLocation

GO