SQL Server 2005 Backup and Restore Architecture

SQL Server 2005 contains a fully integrated backup/restore API that can be driven programmatically from T-SQL. Microsoft has also provided a built-in interactive user interface to the API in Management Studio that can get you up and backing up as soon as you start doing business or finish configuring (actually you never finish configuring, but you know what I mean).

The backup utility is fully functional and flexible enough to configure mission-critical backup/restore requirements or to meet the demands of an SLA demanding a 0/0 backup/restore acuity index. With the functionality you have now, you can plan an adequate and cost-effective backup/restore plan to deal with the possible disasters earlier discussed.

Using the internal backup facilities of SQL Server 2005 for backups is far better and cheaper than using an external, third-party backup solution. Even the smart new Backup program that ships with Windows Server 2003 will not be able to do the job. The reason is simple: the open files dilemma. When a database and its log files are in use, no external application will be able to back up the files, because they are essentially in use by the DBMS.

The only time you would be able to make an external backup of the database is when everybody is out of the database and the DBMS is shut down (all services have been stopped). There are third-party products that are able to back up open database files, but they are expensive and not as integrated as SQL Server’s internal backup utilities. Products like NetBackup can back up online databases. They work well for one or a few servers, but they become very expensive and clunky when dealing with many servers and hundreds of databases. In short, SQL Server Backup provides as advanced an active backup solution as you’ll need, possibly the most advanced in the DBMS industry.

SQL Server’s backup allows you to back up databases while they are in use. Granted, you will see a drop in system performance, which you might choose to improve with more hardware, and faster tape drives, and some other techniques I will later discuss. Naturally, SQL Server will prevent any backups from going ahead while database alterations are taking place. Such activities include the following:

• Automatic or manual database size changing

• Object creation or deletion

• Index creation or deletion

• While performing nonlogged operations

If you start a backup and one of the preceding processes takes place, the process bows out with an error. If the process starts before the backup or restore, the backup or restore process bows out with an error.

That you can back up a database while it is in use means you have a lot more flexibility in your backup schedules and a lot more possibilities of performing backups on a continuous basis as opposed to off-peak times, or some ungodly hour in the middle of the night.