Clever Geek Handbook

SQL Server: Updating Databases

We have a product that uses a Microsoft SQL Server database. We have customers using various older versions of the product.

How do people write / build their database update scripts in this situation?

+1


source to share


2 answers


You need a set of fixes for release-release. On production systems with live data, this is a little more complicated, as you may also need to manipulate data in these scenarios.

Redgate SQL Compare Pro is a nice tool for creating these scripts or executing QA functions in scripts (obviously you shouldn't really use it for both roles). Since you have live customer data, you will have to regression test the updates.



I don't think there is an easy way to do this. You will need to develop a script and test any data manipulation it does. Run tests from version to version for some useful set of updates. The key here is to set up a production mirror test environment in which you can test patches before applying them to a live system.

PS don't forget to back up and test the restore on the production database before you finally run the patch script, but you already knew that; -}

+2


source


We have one SQL script that does both a clean install and an upgrade. After installing SQL Express or full SQL server, we run this script which does the following:

  • Creates tables if they don't exist with CREATE TABLE.
  • Delete and recreate all stored procedures
  • Make changes to tables such as changing fields, adding fields, etc.


We keep expanding this script so each release will only add this SQL script file. This means we can run this script on production systems to update them to the latest schema, using the script for new installations to create a schema from scratch.

This way we ensure that updating the script will install the oldest version to the latest version.

0


source






More articles:


All Articles