Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Database deployment: script or backup

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)

like image 936
Jeremy Avatar asked Jan 28 '09 22:01

Jeremy


People also ask

What are the main 3 types of backups in SQL?

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).

What does database deployment mean?

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.


2 Answers

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..

like image 79
Miyagi Coder Avatar answered Sep 19 '22 02:09

Miyagi Coder


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

like image 36
ILKirk Avatar answered Sep 20 '22 02:09

ILKirk