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

DB creation script is better — you can always open a notepad and fix it.

Answer 2

I'm not sure about the backup option but with a db creation script the dba can set up the database as he wants (has to, permissions etc) then just create the tables etc with the script.

Answer 3

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 4

For the first deployment; a database backup ... it's just so fast and easy.

If the database is already live; it's got to be a series of numbered scripts. Personally, I number my scripts and only make database changes via script. This ensures all databases are in synch, or can be made to be in synch really easilly.

Some people think this is kind of anal ... but I will never have a production deployment problem.

Answer 5

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! ;)

Answer 6

I prefer a script because you can put it into a repository and track it. You can also run it on a remote server pretty easily.

Answer 7

Use scripts under source control - a lovely side-benefit is that you build up a searchable history of every object in the database and it's human-readable. Backups are black boxes - you don't know what they are going to do to your db.

Answer 8

I'd go with the scripts -- the combination of human-readability, compatibility through source code control and the potential to easily handle upgrades to your database engine make this the way to go in our organization.

Answer 9

Definately go with SQL scripts as they can be version controlled. Preferably make them small and have some sort of a batch to run them in a known order (incrementally) so that it's easy to run them in a database that is already in production.

An alternative to SQL scripts is to use a migration framework. You specify the database changes in small steps in source code. The benefit of a migration framework is that a progammer that doesn't know SQL can simply make changes to the database (that doesn't however take away the need to know some database basics).

They are rather explicit about how you build up and tear down a database in the incremental steps. This makes it easier to test and recreate bugs in different versions of a database system or application.

Some examples of migration frameworks that I know of are:

• Migratordotnet for .NET which are run in build scripts such as msbuild.
• Ruby on Rails uses migration which are run through Rake.

Backup is meant to actually back-up the database. As the database grows the portability decreases. So a programmer only needs a subset of the data. For security reasons they should deal with test data instead of real data as well. It is a good practice to have some test-data generator in hand so that developers and testers can test through their implementation without damaging data in production.