CODEWITHSUNDEEP
sql-serversql-server-2008ssms
Nugs
Nugs

Reputation: 5783

Deploying latest changes to production DB - without lose of data

I have been doing some research on the correct procedure to follow when working with both a development and live production database. The best article i have found was this one: Strategies for Database Development and Deployment, but i can not accept the idea that i have to maintain a Word document, manually, of every change i make to the DB. That seems ridiculous to me...

I use SQL Server Management Studio to manage my SQL databases both in dev and in prod. Is there a way to deploy latest changes to production WITHOUT destroying tables and data. Can someone please point me to a good procedural article on how this is done in SSMS.

Thanks

Upvotes: 1

Views: 1787

Answers (3)

HLGEM
HLGEM

Reputation: 96572

It is irresponsible to make changes to a database design without creating change scripts that are put into source control.

However, if you are already in this curcumstance, I suggest buying red_gate's SQLCompare. It will look at the two databases and script the differnces. You still can't run this willy-nilly though - sometimes you have made changes to the dev database that are not yet part of the curent version being pushed to prod and SQLCompare has no way to know this. It is far better to create the scripts as you go (Using alter table when the table currently exists so as not to disrupt existing data) and keep them in source control with the rest of code that you will be pushing at the same time.

Upvotes: 3

erikxiv
erikxiv

Reputation: 4075

A common strategy is to keep an ordered set of change scripts, e.g. prefixed with date or database version, which can easily be tested on the development database by starting with a fresh backup from production. The change scripts can often be generated from SQL Server Management Studio when making changes, or could be crafted manually in case of more complex changes.

Another suggestion would be to version control the database definitions (tables, procedures etc). This can be easily achieved by using SQL Server Management Studio to generate create scripts for all objects after each update. This way you can easily compare changes made over time, or between different environments.

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21766

The only right way to do it in prodiction - with or without Management Studio - prepare, check, test and run the scripts manually.

WITH FRESH BACKUP!

Upvotes: 1

Related Questions