Resources‎ > ‎

SQL Server backups

If you are responsible for a SQL Server database then arguably the most critical task that you absolutely MUST get right is your database backup procedures.
SQL Server backups are not that complicated but there is certainly a fair bit more to understand than with a simple file backup

SQL Server databases can be backed up whilst they are still online and in active use. You do not need to turf users off the database in order to undertake any of the backup procedures. This is unlike simpler file/server database such as MS Access where you DO need to be the only user attached to the database when backing it up (in practice you can often get away with having other users in the database when backing up an Access mdb file - however you may end up with a corrupt backup which you will only discover if you need to restore it). With SQL Server you can start a backup with hundreds of users in the database - and they probably won't even notice; all that happens is a slight performance drop whilst the backup is running.

Backups cannot be performed by copying SQL Server's database files directly. These files are open and cannot therefore be copied. Instead you need to use either the built in SQL Server backup options or one of the many third party backup tools. The main reasons for using a third party backup tool are:

  • Compressed/zipped backup files (native backup files are not compressed).
  • Faster backup speed (mainly due to compressed files hence less time is spent writing to disk).
  • Encrypted backup files (native backup files are in plain text). Where these are not a concern the inbuilt SQL Server backup procedures should be fine and are what the rest of this article assumes is being used.

You also need to understand a bit about Transaction Logs. Most Client/Server databases, such as SQL Server, use Transaction Logs as a means to increase reliability, scalability and performance. The Transaction Log comprises a physically separate file to the database file(s) and can be located on a different disk drive (but that's another topic!). When a new record is written to the database, or when an existing record is updated, the changed data is not initially applied to the database at all. Instead it is written away to the Transaction Log and it is kept there until the transaction has been completed. At this stage the system has a complete, self-contained change that can now be applied to the database. The Transaction Log therefore contains a record of all changes that have been applied to the database, each one being additionally stamped with the date and time that the transaction was applied.

When you go to back up a database there are three different backup types that you can select: Full, Differential and Transaction Log. A Full backup (not to be confused with the Full recovery model; see below) is a complete backup of the database and is what most people think of as a backup. A Differential backup contains the data that has changed in the database since the last Full backup. Differential backups are primarily used on very large databases where disk space is tight. In most situations we do not use differential backups, instead opting for full backups, so will not consider them further here. A Transaction Log backup works on the Transaction Log rather than on the database itself.

Recovery Models

Now that we have got some of the underlying concepts out of the way the first decision to make is which Recovery Model you are going to use for your database. There are three different options: Full, Simple and Bulk-Logged.

Simple Recovery Model

With the Simple Recovery model you only need to worry about backing up the database - indeed you cannot backup the Transaction Log. This keeps it nice and simple (hence the name....) but bear in mind that if you have a problem that you can only recover up to the time of your last full backup (typically the previous night). This is similar to the situation with simpler database systems.

The Simple Recovery model is most suitable for read-only databases, databases whose contents only change slowly, databases whose content does not need to be retained (such as the tempdb) and databases which are updated by discrete files which are themselves retained and can hence be re-applied if necessary (such as overnight batch runs). The system databases for example usually run under the Simple recovery model.

Full Recovery model

With the Full Recovery model you not only back up the database but also the Transaction Log. This is useful because, whilst the full database backup may take an appreciable time to run and can result in a large backup file, the Transaction Log backup is usually very quick (seconds as opposed to minutes) and only produces small files (okay, these are generalisations but they hold true in most situations). This means that you can schedule Transaction Log backups to occur very frequently, say every 10 minutes, and if you need to recover the database, you should therefore lose no more than the final ten minutes. When you recover a database that is in Full Recovery mode you first restore the last full database backup and then restore the subsequent Transaction Log backups which should bring you back to within a few minutes of when the database died (if you do have a problem then, before anything else, try to backup the Transaction Log manually - if you can do this then you should be able to get back to the point at which the database failed; only losing part completed transactions that had not yet been completed).

With the Full Recovery model you MUST back up the Transaction Log regularly as well as the database. If you fail to do this the Transaction Log will grow and grow; eventually eating up all of your disk space and causing your database to stop working. We have now seen this twice in large organisations who really should know better..

When you backup the Transaction Log it is 'truncated' at which point all completed transactions will be wiped from the log file and the space that they occupied will be made available for future transactions. The size of the Transaction Log is not changed by the backup process however, just the amount of 'free' space contained within it (in order to reduce the physical size of the Transaction Log you have to 'shrink' it).

The Transaction Log represents activity that is modifying data in the database. Thus its size will depend upon how many updates/inserts/deletes have been applied to the data since it was last backed up (the Transaction Log, not the database). If you decrease the frequency of the Transaction Log backups then the backup file size will increase. The more frequently you back up the Transaction Log then the smaller each individual TL backup file will be (on average and only up to a point).

The Transaction Log will therefore tend to grow up to a certain natural size and then stay there with the space freed up by the backup process being roughly equal to the space required by subsequent transactions, always assuming that the rate of change to the database remains at a roughly constant basis. The logical implication of this is that you do not generally have to shrink the Transaction Log in order to reduce it's size. If you do shrink the Transaction Log it will just grow back to roughly the same size. In fact there are a couple of downside to physically shrinking the log file.

Downsides to shrinking the Transaction Log

The process of increasing the size of the Transaction Log takes up resources and has a temporary impact on performance.
The physical Transaction Log file may become fragmented on disk and hence not work as fast as it should.
The time when it IS valid to shrink the Transaction Log is after you have undertaken a major one-off change that affects a large proportion of the database and hence causes the Transaction Log to grow significantly larger than normal.

Bulk-logged model

This is a more specialized option and one that most people can safely ignore. In essence it is a variation of the Full Recovery model that is intended for temporary use when certain bulk operations, such as index creation or bulk importing of data, occur. These operations are not logged under the Bulk-logged model and hence prevent the Transaction Log from growing excessively. One downside to be aware of is that, under the Bulk-logged model, you cannot perform a point-in-time restore.

You can switch between Bulk-logged and Full recovery models at any time (ie users can be on the system at the time). Both before switching to the Bulk-logged model and after switching back to the Full model it is a good idea to back up the Transaction Log.

NOTE: The Bulk-logged model is intended for occasional, specialised, use only; do not leave your database permanently set to this recovery model.

Recommended Practice

This will not cover all situations but should be suitable for most normal sized application databases.

  • Set your database to the Full recovery model.
  • Use the Maintenance Plan wizard to configure and schedule the backups.
  • Perform a Full backup of your database every night.
  • Perform a Transaction Log backup every 10 minutes during working hours.
  • Save the backup files to a different disk than that of the database's or the Transaction Log's.
  • Copy the backup files to another server or to tape as soon as possible. The former could be achieved with xp_cmdshell and xcopy but will have to be configured manually with SQL Agent running under a domain account.
  • Do not shrink your database regularly; only do this when it grows significantly above it's usual size.
  • Restore your database to a different database or server once a month to test that everything works okay.
  • Monitor backups, Transaction Log sizes, disk space, SQL Server Agent, etc. at least once a week.

Recovery of Your Database

This is another non-trivial topic so will have to wait for a future article!