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')
    RESTORE DATABASE MyDatabase ...

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?

3 Answers 3


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

You could use dynamic SQL after the restore part.

DECLARE @sql NVARCHAR(2000) = '';

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

--SELECT @sql
EXEC sp_executesql @sql;

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

    ALTER DATABASE $(destinationDb)

    DROP DATABASE $(destinationDb)    


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

use $(destinationDb)

-- your work here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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