"great_googley_moogley" <po*********@yahoo.com> wrote in message
news:dd**************************@posting.google.c om...
Greetings,
I am in the process of installing a SQL database at a customer
location. I have determined that there are 3 ways to do this, and I
wanted to know which is the best of the 3.
1 Install From Script.
In this method I create the database and its objects in scripts that
are run via osql utility on the SQL server machine. For loading any
initial data that I need in the database I also run bcp commands.
2 Install from a backup
In this method I created an empty database on the SQL server, and then
restore over it the database from a backup of the database that I need
to deploy. Then I add or re-attach the users for the database. I
perform all of these operations using osql as well.
3 Install by attaching the data files.
In this method I created an empty database on the SQL server, and then
I attach the data files to the database using the sp_attach procedure.
Then I add or re-attach the users for the database. I perform all of
these operations using osql as well.
Although it is no problem for me to use any of these methods, I wanted
to know from you veterans out there what the best practices are. And
also if there are any unseen hazards for each method above. Or if I
am totally off-the-target, and there is another method that is the
preferred way.
Thanks in Advance,
roger
All three options can give the same results, as you say, but there are some
differences in functionality.
Option 1 is good if you need to load different data for different clients -
with other methods you'd need a copy of the database for every possible data
set you need to provide, but with scripts the object scripts are the same,
and only the data (BCP) files change. Or from the database object
perspective, you can provide different subsets and/or versions of stored
procedures etc. to different clients. In addition, this option doesn't
require you to have any access to the filesystem, which can be useful in
environments with limited bandwidth or tight security requirements. The
downside - if you consider it one - is that you need to manage the scripts,
but presumably you're using some sort of source control and deployment
scripting already.
Options 2 and 3 are essentially the same, in that you're creating the
database from files which you need to copy to a filesystem first. The major
difference is that you can restore from a network drive, but you can't
attach a database on a network drive (usually). So if you have no access to
the server's local filesystem, but you do have access to a network share
accessible to the server, you could only use the restore option. The big
advantage of these options is obviously simplicity..
Personally, I would consider option 1 the best in the sense that it's the
most flexible - you have complete control over which objects and data are
loaded, and the layout of the files and filegroups can be decided together
with the DBA before you create the database. But if you're always installing
identical databases in very similar environments (eg. inside a large
company), then the simplicity of one of the other options is likely to be
better for you - less time and effort required.
Simon
