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:
IF NOT EXISTS (SELECT NULL FROM sys.databases WHERE name = 'MyDatabase')
RESTORE DATABASE MyDatabase ...
USE MyDatabase -- The database exists by this point
-- Apply patches ...
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?