EXEC to USE Database

Question

I have written a script that generates a database backup file name, backs up the database, and then restores it as a copy with the new database name. The name is based on some date/time data.

I then need to USE that database, in the script, and then disable all triggers.

However, this doesn't work:

DECLARE @Use VARCHAR(50)
SET @Use = 'USE ' + @NewDatabaseName

EXEC(@Use)

Running it manually - the database doesn't get 'USED'.

How can I execute the USE statement with a variable?

I have tried the sp_executesql proc as well, with the same result. Database didn't change.

DECLARE @sqlCommand nvarchar(1000)
SET @sqlCommand = 'USE ' + @NewDatabaseName

EXECUTE sp_executesql @sqlCommand

Looks like I might need to go into sqlcmd mode? Really hoping not to, though.

Answer 1

Both exec and execute_sql run in their own scope. And the change in database will only affect their own scope. So you could:

set @sql = 'use ' + quotename(@new_db_name) + '; disable trigger t1;'
exec (@sql)

As far as I know, there is no way to change the database context of the current scope to a variable database name.