SQL Server Backup types
Full – a complete database backup which truncates the transaction log of inactive records
Differential – a backup of all of the changed data pages since the last full backup. Usually smaller than a full backup, assuming that not all pages have changed
Log– transaction log backup containing all transactions since the last transaction or full backup. Also truncates the log of all inactive log records
SQL Server Backup – which recovery model should you choose?
This is an important decision to make when setting up your databases. The recovery model determines whether you can perform point in time recovery or not. It also affects transaction log size.
There are 3 types of recovery model:
Full recovery model
If your database is enabled with a full recovery model, it means that the transaction log will only be truncated upon completion of a FULL backup or upon completion of a transaction log backup.
This recovery model is necessary for log shipping, database mirroring and transaction log backups to provide point in time recovery.
Point in time recovery is vital if you cannot tolerate much data loss. How often you backup your transaction log will determine how much data you can recover in the event that you have a disaster.
Backing up the transaction log is far superior than setting up differential backups every 30 minutes as a differential will backup all the changed data pages taken since the last full backup. This could take some time to complete and consume lots of disk space.
It is advised that if you implement the FULL recovery model, that you do also put in place a policy to backup your transaction logs regularly. Failing to do this on a busy system could mean that your transaction log disks run out of disk space very quickly!
Simple recovery model
With simple recovery, the transaction log is truncated on each checkpoint.
What this means is that if a transaction is committed to disk, the space used by that transaction inside of the transaction log, is emptied leaving space within the transaction log file.
If you are not concerned about point in time recovery or any of the other processes associated with the full recovery model, then this is a handy option to enable and requires less management of the transaction logs because the log is always truncating.
Bulk logged recovery model
Finally we have the bulk logged recovery model. For this option, it is still possible to backup the transaction log but it does not permit point in time recovery. The advantage with this recovery model is that it uses minimal logging in the transaction log.
This is not only faster because less is being written to the log but it also means that the transaction log will not grow as large compared with the full recovery model.
Scheduling and retention of your SQL Server backup
It’s possible to implement scheduling of your backups using the SQL Server Agent. What you choose to implement will depend on the data retention requirements for your business. A very simple example of a schedule might look like this:
- Take a weekly FULL backup
- Take daily DIFFERENTIALS
- Take regular transaction log backups, for example every 30 minutes.
This schedule might repeat each week to a point when the backups are a certain age, they would no longer be kept in storage and re-used. This will depend on your business requirements.
In SQL Server, backup schedules can be put in place which are customized to your needs and available backup capacity. The available options provide great flexibility as everything can be done at the database level.
You may have databases which are critical to the business which need frequent backups and you may also have databases which do not change, for example read-only databases. You may only back these up very rarely in this instance but retain those backups for long periods of time.
Backup storage – what to use
- Backup to local disk
- Backup to network share or SAN
- Backup to tape
- Combination of disk/network/SAN plus tape – you may keep aged backups on tape and recent backups on disk for example.
- Backup to cloud – there are companies now which provide this service.
- Backup to FTP location
Ensure that whatever option or options to decide upon that there is redundancy there to help protect your backups.