Sql-server – Are there any problems with deploying an SQL Server database to a production server by taking a backup

deploymentsql serversql-server-2008-r2

This is a slightly loaded question in that I have already assumed that the described scenario is wrong.

A DBA is deploying an application I have written that includes an MS SQL Server 2008 database. He has asked me to take a database backup from my development machine so he can restore it to the production server, thus deploying it. This is a greenfield deployment so there is no existing data to be migrated. I was expecting to provide a DDL script, which I have diligently tested and ensured that it contains everything required. If I execute it in SSMS, the database is created in one click.

To me, using the backup facility for deployment does not seem right, but without being an expert in SQL server I can't think of a solid reason not to do it. I would have thought, for example, that there would be some 'contamination' of the database from the development machine – perhaps the computer name, directory structure or user names stored in there somewhere. Is this the case, or is backup and restore a valid deployment technique?

Best Answer

Everything is wrong with using backup files as deployment. But the burden is not on the DBA to provide the DDL, is on development. Your design and development artifacts should had been database installation and upgrade scripts. Never change anything in the database manually, everything should be modified using your app. Rails gets this in spades with he whole migrations infrastructure and you should try to adopt it too. I have for long advocated using similar techniques, see Version Control and your Database.

First let me make the case why source code based deployment/upgrade is superior to binary based deployment (.bak, or diff tools):

  • source code can be checked in into source control. This alone should settle the whole argument. Source control gives yo history, a future you can look back and read the check in notes, understand the reasoning behind current state.
  • source code can be quickly inspected at a glance. You look at it and read it. binary databases require to be attached and require extensive knowledge of the metadata catalogs to read even the basic properties
  • source code is clean. You see CREATE TABLE Foo (...), which clearly conveys the intent. Binary distribution, if you want to extract the object, showers you in a plethora of default properties. You loose the original intent.
  • source code can be peer reviewed at check in.
  • source code integrates in contiguous deployment

And I also have arguments why deploying by backup is bad (very bad):

  • you only postponed the issue. The very first update of the app will face the issue of deploying an update without loosing the data. This can occur the next day after deployment, when an issue is noticed in production, and you will be left facing the void: how to modify the production DB to match the dev DB?
  • Databases are not self contained. Deployment invokes objects outside DB (logins, SQL Agent jobs, maintenance plan etc) none of which can be deployed with a backup.
  • You never know what you deployed. Forgotten tables left over during dev? Test data? Is very difficult to cleanup a database, but keeping your source code up to date and correct is natural.
Related Question