Backing up Microsoft SQL Server

There are at least three ways to backup Microsoft SQL Server – offline backups, online backups using the Backup Exec SQL agent, and online backups of SQL's own backups.

Offline backups are where you take SQL offline (i.e. dismount the databases), then back up the files. The advantage to this is that you require no extra software. The disadvantage is that SQL is unavailable for users, or for routine maintenance while the backup takes place.

Online backups using the Backup Exec SQL agent are where SQL remains online during the backup. This requires the purchase of Agent for Microsoft SQL. An advantage is that your restores are very flexible, as you can restore to a particular transaction if required. Disaster recovery is simple, as the restore of the master database can be performed quickly and easily.

Online backups of SQL's own backups are where you create an SQL maintenance plan, which includes a backup to a file. You then use Backup Exec to backup the resultant file. This has the disadvantages that you use up extra disk space, as you have two copies of the data, and restores are longwinded as you have to restore the file, then restore from the file to the SQL server. Additionally, in the event of a disaster, recovery of the SQL master database is time-consuming.

If you are in a small or medium environment, you will probably be OK with a Full SQL backup each night or week, followed by a transaction log backup.

In a large environment, or where you need a short Recovery Point Objective, you might wish to supplement the database backup with logfile backups at regular intervals (maybe hourly).

One unusual element of the Backup Exec SQL Agent is that a full backup does not truncate the log files. If you use SQL's own backup utility, a full backup will backup the database, then truncate the log files. With Backup Exec, you need to run a transaction log backup to truncate the log files. The easiest way to do this is to do a transaction log backup immediately after the full backup.