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
Learning Center: Backing up SQL Server
Ask the Experts: Is it possible to restore tables without a backup?
Topic: Research SQL Server backup and recovery issues
