Patching Databases

During development, blowing away your database and re-creating it from source becomes a daily occurrence.
But once that database goes live, you obviously need a different approach.

Each change to data or database structure will need to be written up into a patch script and handed over to your DBA for execution on the live server.

Using DBSourceTools makes testing these patch scripts simple, robust and repeatable.

Here's how:
  • Create a source code branch of your database.
    • Backup your Production database.
    • Restore it onto a developer machine, with a name that includes the version ( e.g. ProdDB_1.0 )
    • Script this database using DBSourceTools.

  • Create a Target Database that updates the version number in the database name:
  • i.e. ProdDB_1.0 ( from production) is deployed to ProdDB_1.1

  • Deploy this Target Database to create a copy of version 1.0 to version 1.1
  • Make modifications to ProdDB_1.1
    • Add a new Source Database, and point it to the new version ProdDB_1.1

    • Make your changes

    • Use Schema Compare to generate scripts that will make the changes in data or schema from 1.0 to 1.1

    • Save these scripts under the Patches directory of your Deployment Target Database ( i.e. ProdDB_1.0\DeploymentTargets\ProdDB_1.1\Patches)

    • Close and re-open DBSourceTools
      • Note that your patches are automatically picked up from the Patches directory.

    • Now right-click on your Deployment Targets, and select Write Targets
      • This will update your Run_Create_Scripts.bat file with all patches found in the Patches directory.

    • Deploy your database
      • This will essentially re-create version 1.0 from source, and then apply all patches to move to version 1.1


Have fun.

Last edited Feb 3, 2010 at 2:14 AM by rozentalsn, version 10


No comments yet.