Make changes to a production database!

[debian mojo]

Hello faculties,
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.

Recently we added some tables to our local database which is a
replica of the production database. Now we need to apply the same
changes at the production one aslo.

I've no clue about what steps should i implement.

Please guide me!

Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***

[Simon Hayes]

The ideal solution is that you take the scripts which you used to make
the changes in test from your source control system and simply run them
against the production database. For a large number of .sql files (or a
large number of target servers/databases), this can be automated using
osql.exe in batch files, or perhaps a script in your preferred
scripting language.

However, if you made the changes in some non-repeatable way (such as
using Enterprise Manager), then you would need another solution. One
possibility is to use a database compare tool of some sort to generate
a script for you, but you might still need to make other site-specific
changes such as updating data or creating new logins. So whatever you
do for now, it would be a good idea to plan on working with scripts in
a source control system in the future.

Simon

[David Portas]

Build a TSQL script to perform the upgrade and then test it out.

If you didn't save your change scripts when you did your own upgrade
then you'll have to re-create them (and remember to save the scripts
next time!). Enterprise Manager can help with this (right-click the
database, select All Tasks > Generate Script).

To script the data you can use this proc:
http://vyaskn.tripod.com/code.htm#inserts

or, if the data is particularly large you may find it easier to dump it
to a file and use BCP to perform the load.

It may help to drop or disable some constraints and indexes for the
duration of the upgrade - that's assuming the relevant tables aren't in
use at the time.

Also, you may want to check out some of the diff and synchronization
utlilities, such as:
http://www.red-gate.com/sql/summary.htm

Hope this helps.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

debian mojo (de*********@yahoo.com) writes:

Hello faculties,
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.

Recently we added some tables to our local database which is a
replica of the production database. Now we need to apply the same
changes at the production one aslo.

I've no clue about what steps should i implement.

In addition to what Simon and David said, I just like to underscore
that whatever you do, first run your upgrade scripts in a test environment.
Don't deploy until production, until everything is working OK in test.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp