Robin Tucker (id*************************@reallyidont.com) writes:
I have a database on my test machine that will need to be installed on
users machines. I would like to create the database with the given
schema on the users machine and also with some suitable default values
in the tables. I note that although I can script the schema so that
re-creating the structure of the database is simple on the users
machine, I cannot script the contents of the tables also
(automatically). What I would like to do is take some kind of
"snapshot", save it as a script and then run this script in my
installer. Are there any tools available to do this?
SQL Server MVP Vyas Kondreddi has a tool that generates INSERT statements
from a table: http://vyaskn.tripod.com/code.htm#inserts
Secondly and related to the above: if I subsequently make changes to the
database schema (adding or removing columns, altering, adding or
removing stored procedures etc.), how do I roll out those changes to a
customer? Do I need to hand code an "upgrade" script, or is there a
tool that will produce a "difference between" script I can run on the
customers machine?
There are several paths to take. Many people use a third-party tool that
compares two databases and then generates a script. Very popular is
SQL Compare from Red Gate. I have not used this tool myself, though.
A better approach in my opinion, is to have all code under source control.
In this case, your development database is not your master, but the
version-control system is. A basic version-control system will not
provide any update scripts for you, as a version-control system is a
general container for all sorts of code. What you do is that when you
ship, you set a label, and then you can later inquire the VCS for
changes since that label. There are plenty of VCS on the market. Very
popular among Microsoft customers is Visual SourceSafe which is part
of Visual Studio. VSS is not a very good for serious configuraton
management, but it's easy to get started with, and works perfectly OK for
smaller groups.
There are a couple of third-party tools that are specialized for doing
version control on SQL Server. Typically, they sit on top of SourceSafe
or some other generic VCS. Unfortunately, I don't recall any names right
now.
In our shop we use VSS, together with a toolset that includes tools for
loading stored procedures (with a lot of bells and whistles, like
automatic insert of SET NOCOUNT ON, WITH ENCRYPTION (on request),
automatic GRANT, a preprocessor). We also have a tool that builds
update scripts from the checkins in SourceSafe. The tool also maintains
its own tables in the target databases, so that we know in which state
each database is in. This toolset is available as freeware on
http://www.abaris.se/abaperls/. (I uploaded the latest version of it,
just the other day, by the way.)
Judging from your question, you may be best off with something like
SQL Compare in the short run. But if more people get involved with
your work, you should definitely consider to move to a version-
control system. There are a few things to keep in mind with updating
from a model database:
o A development may contain junk code and junk tables from tests and
experiments.
o A table change may be as simple as adding a nullable column, but it
can also be very complex if you are making a major restructiring. A
tool probably needs some help in this case.
o If you have preloaded data that you want to deploy, you need a tool
where you selectively can migrate data.
The way we handle pre-loaded data by the way, is to enter the data in
Excel books, and then we have a tool that generates INSERT files from
the the Excel files. The files does not contain any INSERT statements,
but calls to stored procedures which inserts or updates. The reason we
use Excel is that some of our files are quite complex. and we have
different settings for different customers.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
