Installing a database

Hi All,
I've been using SQL Server for about 6 months, but I'm totally new to
installing a database. That is, automated installing a database. I've done
it before myself, with the use of Andrea Montanari's great tool DbaMgr2k.
But now I'm looking for the best way to install a database without me being
physically present. As far as I know there are a couple of ways, which each
have their pro's and cons. The ones I know are:
- Restore a backup of an empty database with OSQL
- Create it using a script file ran by OSQL
- Create it by an 'home cooked' application
What is the BEST or ALMOST best way? I'm looking forward to your responses.
Thanks,
Willem
> What is the BEST or ALMOST best way? I'm looking forward to your
> responses.
What does "BEST" mean? What are your criteria?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||From the ones I mentioned:
- Restore a backup of an empty database with OSQL
- Create it using a script file ran by OSQL
- Create it by an 'home cooked' application
Seems the first one is the most easy one. I think creating/maintaining a
script, or program code, is more time consuming. But maybe some experts in
this newsgroup do not agree at all. That's why I'm asking for opinions.
And maybe there is another, even better way that I haven't discovered yet.
Willem
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> schreef in bericht
news:u%235FMqHLEHA.1612@.TK2MSFTNGP12.phx.gbl...
> What does "BEST" mean? What are your criteria?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
|||Having a .sql file is "BEST" in my opinion. You can run it multiple times,
you can store it in source control, it's easy to read, the syntax is
documented, etc etc. Everything else you've listed requires steps that you
can't store in source control, and you might not be able to easily transfer
from person to person.
And a word of caution: "most easy" should never be part of your criteria for
"best" unless you like doing work over, learning the hard way, starting from
scratch, etc.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Willem van den Broek" <willem@.chsd.nl> wrote in message
news:uuMAD7HLEHA.3712@.TK2MSFTNGP11.phx.gbl...
> From the ones I mentioned:
> - Restore a backup of an empty database with OSQL
> - Create it using a script file ran by OSQL
> - Create it by an 'home cooked' application
> Seems the first one is the most easy one. I think creating/maintaining a
> script, or program code, is more time consuming. But maybe some experts in
> this newsgroup do not agree at all. That's why I'm asking for opinions.
> And maybe there is another, even better way that I haven't discovered yet.
> Willem
>
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> schreef in bericht
> news:u%235FMqHLEHA.1612@.TK2MSFTNGP12.phx.gbl...
>
>
|||hi Willem,
"Willem van den Broek" <willem@.chsd.nl> ha scritto nel messaggio
news:uuMAD7HLEHA.3712@.TK2MSFTNGP11.phx.gbl...
> From the ones I mentioned:
> - Restore a backup of an empty database with OSQL
> - Create it using a script file ran by OSQL
> - Create it by an 'home cooked' application
> Seems the first one is the most easy one. I think creating/maintaining a
> script, or program code, is more time consuming. But maybe some experts in
> this newsgroup do not agree at all. That's why I'm asking for opinions.
> And maybe there is another, even better way that I haven't discovered yet.
I do agree with Aaron...
you can perform this action in several ways... ech of them has it's
drawback...
1) you can detach your dev database, copy it on the user's machine, and
reattach it ..
using sp_detach_db to detach it and sp_attach_db for reattach, see BOL for
further info...
2) you can backup your dev database and restore in on the user's machine...
see backup database and restore database in BOL for further info...
these 2 methods are quite easy to perform but suffer from some drawback..
- they can raise orphan users issue (please see
http://www.sqlservercentral.com/colu...okenlogins.asp for
further info and details on how to solve this)
- they do not make use of user's model database... every database inherits
settings, dbobjects, sometimes permissions, from the current model
database... if you deploy your database schema these way you simply export
YOUR settings and preferences..
- they inherits your settings regarding collation and sort order... this is
no more a problem with SQL Server 2000 becouse it supports different
collations, but was a major issue for SQL Server 7.0 deploy..
3) you can script out your DDL database schema with tools like Enterprise
Manager...
this method is the more elastic one, even if it require more deal...
I currently deploy my dbs this way...
I deploy sql DDL script to re-create the schema, sql permission scripts to
generate privileges and users, sql DML "Insert Into" script to pre-load some
application's system-tables and/or txt files to BCP in data...
I do use a companion application which parses a private defintion files that
includes settings as long as a list of files to be "executed" some way... I
do use ADO and/or SQL-DMO, but oSql.exe can be an alternative, even if it
does not set the OS error level status on Win9x boxes... I chose not to use
oSql for this reason...
even if more complicated, this method allows me to deploy database upgrades
with no pain, providing the sql DDL script to morphe databases to current
release.. several tools can help you for this
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks for this explanation. Aaron arguments didn't convince me at first.
But your statement "every database inherits settings, dbobjects, sometimes
permissions, from the current model database" did. That's quite an
interesting argument to choose for option 3.
Willem
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> schreef in bericht
news:c6nnq8$di72l$1@.ID-207518.news.uni-berlin.de...[vbcol=seagreen]
> hi Willem,
> "Willem van den Broek" <willem@.chsd.nl> ha scritto nel messaggio
> news:uuMAD7HLEHA.3712@.TK2MSFTNGP11.phx.gbl...
in[vbcol=seagreen]
yet.
> I do agree with Aaron...
> you can perform this action in several ways... ech of them has it's
> drawback...
> 1) you can detach your dev database, copy it on the user's machine, and
> reattach it ..
> using sp_detach_db to detach it and sp_attach_db for reattach, see BOL for
> further info...
> 2) you can backup your dev database and restore in on the user's
machine...
> see backup database and restore database in BOL for further info...
> these 2 methods are quite easy to perform but suffer from some drawback..
> - they can raise orphan users issue (please see
> http://www.sqlservercentral.com/colu...okenlogins.asp
for
> further info and details on how to solve this)
> - they do not make use of user's model database... every database inherits
> settings, dbobjects, sometimes permissions, from the current model
> database... if you deploy your database schema these way you simply export
> YOUR settings and preferences..
> - they inherits your settings regarding collation and sort order... this
is
> no more a problem with SQL Server 2000 becouse it supports different
> collations, but was a major issue for SQL Server 7.0 deploy..
> 3) you can script out your DDL database schema with tools like Enterprise
> Manager...
> this method is the more elastic one, even if it require more deal...
> I currently deploy my dbs this way...
> I deploy sql DDL script to re-create the schema, sql permission scripts to
> generate privileges and users, sql DML "Insert Into" script to pre-load
some
> application's system-tables and/or txt files to BCP in data...
> I do use a companion application which parses a private defintion files
that
> includes settings as long as a list of files to be "executed" some way...
I
> do use ADO and/or SQL-DMO, but oSql.exe can be an alternative, even if it
> does not set the OS error level status on Win9x boxes... I chose not to
use
> oSql for this reason...
> even if more complicated, this method allows me to deploy database
upgrades
> with no pain, providing the sql DDL script to morphe databases to current
> release.. several tools can help you for this
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>