How to restore a database then upgrade it in a single SQL script? I'm trying to write some SQL that will restore a backup if the database doesn't exist, then run SQL on the database. It is used for installing a database we received from a 3rd party in the form of a backup. For a new installation we restore the backup then apply our own patches to it. For an upgrade we just apply our own patches to the existing database.

I can get it to restore, and I can get it to patch, but I can't get it to do both from one script because it doesn't like to USE the database if it didn't exist when the script starts.

This is the outline of my script:

USE master

IF NOT EXISTS (SELECT NULL FROM sys.databases WHERE name = 'MyDatabase')
BEGIN
    RESTORE DATABASE MyDatabase ...
END
GO

USE MyDatabase -- The database exists by this point

-- Apply patches ...


The line USE MyDatabase throws an error:


Msg 911, Level 16, State 1, Line 2
Database 'MyDatabase' does not exist. Make sure that the name is entered correctly.


I guess it's parsing the whole script before it executes anything because by the time it gets to the USE the database either already existed or was restored, either way it now does exist.

Is there a way to RESTORE a database then USE it in the same script, or do I just have to separate the restore from the update?
You could use dynamic SQL

    Set @SQL = 
   'USE MyDatabase -- The database exists by this point

    -- Apply patches ...'

    EXEC SP_ExecuteSql @SQL


You may need to do a wait to allow the database to restore before executing SQL on it.

WAITFOR DELAY '00:00:02';