How to restore a database then upgrade it in a single SQL script?

Question

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?

Answer 1

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';

Answer 2

You could use dynamic SQL after the restore part.
Example:

DECLARE @sql NVARCHAR(2000) = '';

SET @sql = @sql + 'USE [MyDatabase]; ';
SET @sql = @sql + 'ALTER USER [SomeUser] WITH LOGIN = [SomeSQLlogin]; ';

--SELECT @sql
EXEC sp_executesql @sql;

Answer 3

I had the same problem, and it seems to be a problem if you restore a database, then try a use [myDatabase] statement, all in the same batch. Seems the parser is being overcautious in this case.

The trick is to use the Go statement to separate out a script into smaller pieces. You can use SQLCMD Mode to use variables for things like the database name which is available across all batches

I created this script to drop, restore, and immediately use the restored database;

-- 
-- Drop, Restore, and Use database in one script
-- Uses SQLCMD mode, accessed in Management Studio with Menu | Query | SQLCMD Mode
--

:setvar destinationDb "MYDATABASE"
:setvar restoreFile "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MYDATABASEFILENAME"

use master
IF EXISTS (SELECT *  FROM sysdatabases WHERE NAME = '$(destinationDb)')
BEGIN    

    ALTER DATABASE $(destinationDb)
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE    

    DROP DATABASE $(destinationDb)    

END
GO    

RESTORE DATABASE $(destinationDb) FROM DISK = '$(backupFile)' WITH 
    MOVE '$(dataFileName)' TO '$(restoreFile).mdb', 
    MOVE '$(logFileName)' TO '$(restoreFile).ldb',
    FILE = 1

GO
--
-- DO SOMETHING WITH THE NEW DB
-- 
use $(destinationDb)

-- your work here