More about database maintenance

Backup

Note: please refer to SQL Server Books Online for more information regarding this topic.

The reason for taking backup will not be discussed here; it is obvious.

Backup schemes

Basically, all backup schemes look pretty much the same with variations depending on economics, amount of data and how much the daily usage of the database can be disturbed. Backup of SQL Server is online, but performance during backup will suffer (there were major improvements introduced in SQL Server 7.0, though).

Take backup as often as possible for your environment. This includes database, log and possibly differential backups.

Db Maint offers a great deal of flexibility. Below are some examples of backup schemes

  1. Entry level backup scheme

Daily database backup and hourly log backup (except for the master database). Backup is performed to disk and the files are picked up to tape at a daily schedule.

  1. Alternative (1) plus protect backup files between tape backup schedules

Add copying backup files to another machine. This way, you don’t risk losing up to 24 hours work if the backup files to where you do the backups are destroyed.

  1. Alternative (1) or (2) with also saving archive backup files

Add archive backup occasions (every month, quarter, half year or year). If a database backup occasion is scheduled for the first day of that period, then SQL Server will save an additional archive backup file. This will not be deleted. This is a good idea if you want the ability to go back longer in time to retrieve old data.

  1. Alternative (1), (2) or (3) with compression of the backup file

For either of the backup destinations you can specify a command line which is to be executed. This is designed to allow you to compress the backup files. If the compressed file is created, then the original file will be deleted. Since backup files are generally compressed very well, you can now have backup files available longer back in time (say 10 days instead of 2 days).

  1. Add differential backup to any of above alternatives

Say that you want to do log backup every 10 minutes and can only to database backup every day. This would leave you with restoring up to 144 log backups in case of failure (24 hours times 6 backups). It is probably a good idea to also do differential backup, say, every 2 hours. You will now restore the latest database backup, the latest differential backup and all subsequent log backups (a total of max 14; 1 plus 1 plus max 12 log backups).

  1. Add test-restore to any of above alternatives

Select to do a test restore each time a backup is performed. Db Maint will write error messages to its log if SQL Server generates any error messages. This way you are ensured that the backup is healthy. The test-restore is performed before the compression is performed (if specified).

  1. Add log shipping to alternative (1), (2), (3), (4) or (5)

Log shipping is essentially a test restore onto another SQL Server. This SQL Server can be used as a standby SQL Server is the primary machine fails. This is a very cost efficient alternative to clustering for machine redundancy.

  1. Backup to tape

Db Maint also supports having SQL Server backup directly to tape, with the same scheduling options as for backup to disk. Copy or archive backup, test restore or log shipping is not available when doing backup to tape, however. This is for the simple reason that the backups are never available as files.

Combining databases for which you can’t and for which you can do log backup in the same task (the scheduling using)

In almost all installations, you have both databases for which you can (most databases) and databases for which you can’t (the master database, for example) to log backup. In general, you want to create only one schedule, so you don’t have to think of when to execute this and when to execute that. With Db Maint, you simply create one job for the databases that you can do log backup and one for the databases that you can’t do log backup. For each job, you specify whether to perform log backup. You then schedule the task as desired, including when to do log backup. Db Maint will now only perform log backups for the databases included in the job(s) when log backup has been specified.