Database deployment: script or backup

Question

If you had a DBA who was responsible for deploying databases in a live environment, what would you choose to give him? A database creation script or a backup which he could restore onto an existing database? What kinds of advantages/disadvantages are there? (We're using MSSQL2000 and MSSQL2005)

Answer 1

I prefer a script for deployments, they are much less intrusive. A restore will overwrite the whole database, data and all, which probably is not a good idea on your production enviroment..

Answer 2

While I agree with John's first deployment suggestion of using a database backup, I prefer the idea of at least having the script available for review to allow for checking for coding standards, performance evaluation, etc.

If you're looking to turnkey something that has already been reviewed, then the backup/restore is a perfect option for the first deployment, even more so if there is configuration data that has been "staged" in that database. This help to avoid lengthy or complex insert statements.

Its a given that releases beyond the first are going to be scripted. In our environment we require release notes to specify the server, instance, and path to the scripts. The scripts should be provided in numeric order, separated into folders by target database, with a second set of rollback scripts. We even require that each script has a USE statement at the top so we don't have to worry about creating new objects in the master database! ;)