"mark" <m***@discussions.microsoft.com> wrote in message
news:E42500F3-E3C9-41D5-AF40-21A001C61DBF@microsoft.com...
> Does anyone know if it is possible to script the creation of a new
> database
> and tables passing a variable that is set as the db name?  I have a fairly
> large script that creates db, tables, users etc but to move from dev to
> testing where I would like to have a new name and users, I need to update
> the
> script everywhere the db name was.  Anyone use any tools that can move a
> db
> from one environment to another but allow the db name to be changed.
> Replication is not an option as the two systems are not connected.
>

"Aaron Bertrand [SQL Server MVP]" wrote:

> If you use management studio you can turn on the sqlcmd mode under the query
> menu, and you can say things like:
>
>
>
> :setvar dbname mydatabase
>
> create database $(dbname);
> use $(dbname);
> go
>
>
>
> Now you can take this script to another server / database and just change
> the :setvar line(s).
>
> Though I'm not sure why you need to reference the db name explicitly other
> than by issuing a USE command at the beginning of the script, assuming all
> objects are being created inside that database.  Where are you hard-coding
> the database name?  If you are using SQL Server 2005, you may consider using
> synonyms to abstract the actual heirarchy from your database-level
> objects...
>
>
>
>
>
> "mark" <m***@discussions.microsoft.com> wrote in message
> news:E42500F3-E3C9-41D5-AF40-21A001C61DBF@microsoft.com...
> > Does anyone know if it is possible to script the creation of a new
> > database
> > and tables passing a variable that is set as the db name?  I have a fairly
> > large script that creates db, tables, users etc but to move from dev to
> > testing where I would like to have a new name and users, I need to update
> > the
> > script everywhere the db name was.  Anyone use any tools that can move a
> > db
> > from one environment to another but allow the db name to be changed.
> > Replication is not an option as the two systems are not connected.
> >
>
>
>

"mark" <m***@discussions.microsoft.com> wrote in message
news:AE6A0B58-D5A5-41BA-A689-7933CED5D780@microsoft.com...
> Thanks for the response Aaron.  I haev the db name in the script where I
> set
> the db options, Auto close, auto shrink etc.  Perhaps I should not be
> doing
> it this way?  I was also hoping to create my tables, stored procs, logins
> in
> the same script which is where I am using the USE command.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> If you use management studio you can turn on the sqlcmd mode under the
>> query
>> menu, and you can say things like:
>>
>>
>>
>> :setvar dbname mydatabase
>>
>> create database $(dbname);
>> use $(dbname);
>> go
>>
>>
>>
>> Now you can take this script to another server / database and just change
>> the :setvar line(s).
>>
>> Though I'm not sure why you need to reference the db name explicitly
>> other
>> than by issuing a USE command at the beginning of the script, assuming
>> all
>> objects are being created inside that database.  Where are you
>> hard-coding
>> the database name?  If you are using SQL Server 2005, you may consider
>> using
>> synonyms to abstract the actual heirarchy from your database-level
>> objects...
>>
>>
>>
>>
>>
>> "mark" <m***@discussions.microsoft.com> wrote in message
>> news:E42500F3-E3C9-41D5-AF40-21A001C61DBF@microsoft.com...
>> > Does anyone know if it is possible to script the creation of a new
>> > database
>> > and tables passing a variable that is set as the db name?  I have a
>> > fairly
>> > large script that creates db, tables, users etc but to move from dev to
>> > testing where I would like to have a new name and users, I need to
>> > update
>> > the
>> > script everywhere the db name was.  Anyone use any tools that can move
>> > a
>> > db
>> > from one environment to another but allow the db name to be changed.
>> > Replication is not an option as the two systems are not connected.
>> >
>>
>>
>>

"Aaron Bertrand [SQL Server MVP]" wrote:

> Can't you set options in the CREATE DATABASE statement?  Also, you shouldn't
> use auto close or auto shrink in my opinion, so leaving them out will keep
> them at the default (both disabled).
>
>
> For more info on my opinion, see:
>
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=238888
>
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
>
> Also, if you do this (only two hard-coded name references to change):
>
> CREATE DATABASE foo WITH (<options>);
> GO
> USE foo;
> GO
>
> -- where do you need the database name after this point?
> CREATE TABLE dbo.bar
>
>
>
>
>
>
> "mark" <m***@discussions.microsoft.com> wrote in message
> news:AE6A0B58-D5A5-41BA-A689-7933CED5D780@microsoft.com...
> > Thanks for the response Aaron.  I haev the db name in the script where I
> > set
> > the db options, Auto close, auto shrink etc.  Perhaps I should not be
> > doing
> > it this way?  I was also hoping to create my tables, stored procs, logins
> > in
> > the same script which is where I am using the USE command.
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> If you use management studio you can turn on the sqlcmd mode under the
> >> query
> >> menu, and you can say things like:
> >>
> >>
> >>
> >> :setvar dbname mydatabase
> >>
> >> create database $(dbname);
> >> use $(dbname);
> >> go
> >>
> >>
> >>
> >> Now you can take this script to another server / database and just change
> >> the :setvar line(s).
> >>
> >> Though I'm not sure why you need to reference the db name explicitly
> >> other
> >> than by issuing a USE command at the beginning of the script, assuming
> >> all
> >> objects are being created inside that database.  Where are you
> >> hard-coding
> >> the database name?  If you are using SQL Server 2005, you may consider
> >> using
> >> synonyms to abstract the actual heirarchy from your database-level
> >> objects...
> >>
> >>
> >>
> >>
> >>
> >> "mark" <m***@discussions.microsoft.com> wrote in message
> >> news:E42500F3-E3C9-41D5-AF40-21A001C61DBF@microsoft.com...
> >> > Does anyone know if it is possible to script the creation of a new
> >> > database
> >> > and tables passing a variable that is set as the db name?  I have a
> >> > fairly
> >> > large script that creates db, tables, users etc but to move from dev to
> >> > testing where I would like to have a new name and users, I need to
> >> > update
> >> > the
> >> > script everywhere the db name was.  Anyone use any tools that can move
> >> > a
> >> > db
> >> > from one environment to another but allow the db name to be changed.
> >> > Replication is not an option as the two systems are not connected.
> >> >
> >>
> >>
> >>
>
>
>