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)
A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).
Database deployment automation is the practice of including databases in the DevOps process and automating it for the whole pipeline to run more smoothly. With organizations needing to do better and deliver faster than ever, deployment automation has become key to having a competitive advantage.
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..
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! ;)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With