Chapter 2: SQL ServerMaintenance and Troubleshooting <<previous|next>> :Resolve errors received when restoring a database in SQL Server
SQL Server Backup and Recovery
Don't use backup and restore to distribute database tools
Many database software tools are designed to work with SQL Server or they are packaged with a SQL Server database. That's all well and good, but you then have the thorny task of distributing the database to a client or end user. A parallel situation may involve creating a custom software package installation in a remote office, for which many of the same distribution issues apply.
The most obvious option is to make a backup of the whole database and simply send the backup file. This is easy enough and it does work -- but only in a one-time fashion. If in the future you need to update the database, you'll have a hard time doing that with a full backup unless you restore it as a separate database and manually merge in changes with a script, or use some kind of source-control system at both ends. At that point you're pretty much back where you started.
Given the level of hassle all of this involves, your best option is to generate scripts to create and populate the database, and to supply scripts that can be used to elegantly migrate from one version of the product to the next. Another approach -- and probably the cleanest although most expensive -- is to use third-party software packaging tools that give special attention to databases and are designed to do exactly this sort of thing. Red Gate's SQL Packager is one such tool, which takes care of deployments and upgrades automatically.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
03 Jan 2006
Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.