Can I use a variable in a script to 'use' database

[bmacbmac]

Hello,

I am trying to move hundreds of databases from one server to another and trying to build a script to help along. Both servers are SQL 2000

I need to do one at a time over the next week after each database is ready. I am trying this:

declare @dbname varchar(100)
set @dbname = 'Batch1'

create database @dbname

restore database @dbname
from disk = '\\server\share\@dbname'
with init

use @dbname
sp_changedbowner USC

The variable will work on everything except for the 'create' and 'use' databases.

I get the error: Incorrect syntax near @dbname

I thought it might have to do something with my Varchar variable type and the use of single quotes, but I have tried without quotes and it hasn't helped.

Thanks!

 

[gmmastros]

You can do this with dynamic sql. It would look something like this:

declare @dbname varchar(100)
set @dbname = 'Batch1'

Declare @SQL VarChar(8000)

Set @SQL = '
create database @dbname

restore database @dbname
from disk = ''\\server\share\@dbname''
with init

use @dbname
sp_changedbowner USC
'

Set @SQL = Replace(@SQL, '@dbname', @dbname)

Print @SQL
--Exec (@SQL)

Once you get the code to print properly to the messages window, you can comment the print line and uncomment the exec line.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Wow that's a great idea! Seems to work great. My only problem now is that I can't:

create database mydb

restore database mydb

I get a message that the database doesn't exist. I think I would have to put a GO in between the two. But, when I put GO in between the two I get a bunch of errors (probably from losing my variable). Any idea around that?

 

[gmmastros]

Try doing it in steps, like this:

declare @dbname varchar(100)
set @dbname = 'Batch1'

Declare @SQL VarChar(8000)

-- Step 1, create the database
Set @SQL = 'create database @dbname'
Set @SQL = Replace(@SQL, '@dbname', @dbname)
Print @SQL
--Exec (@SQL)

-- Step 2, restore it.
Set @SQL = 'restore database @dbname
from disk = ''\\server\share\@dbname''
with init'
Set @SQL = Replace(@SQL, '@dbname', @dbname)
Print @SQL
--Exec (@SQL)

-- Step 3, change the owner
Set @SQL = 'use @dbname
sp_changedbowner USC'

Set @SQL = Replace(@SQL, '@dbname', @dbname)
Print @SQL
--Exec (@SQL)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Brilliant! Thanks everyone, doing in steps worked great.