Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Don't use backup and restore to distribute database tools

You may be tempted to use a full backup to distribute database-packaged tools to end users, but contributor Serdar Yegulap advises against it. Find out why in this tip.

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

  • Dig Deeper on SQL Server Backup and Recovery

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.