SQL Server Database Backups

One of the most important tasks you will ever perform as a DBA is performing backups. Although backups are certainly not the most interesting part of the job, they are probably the most important. If something goes wrong it’s the DBA’s job to get the server back up and running as quickly as possible. Loss of productivity, or even worse loss of data, can be very expensive for a company. Let’s look at the most common question I get when talking about backups.

Why not just use a RAID configuration that has mirroring to provide protection? RAID is certainly the first line of prevention for data loss. Depending on the RAID configuration you use, one or even several hard drives can fail before the data is lost. Additionally, the use of hot swappable and hot standby drives can be used to allow the server to continue without ever having to be taken offline in the event a hard drive fails. The key thing to notice here is that RAID can protect you if a hard drive fails…but what happens if a fire or natural disaster occurs? What do you do if your database files become corrupted due to hardware or software errors? Or what happens if a user deletes data that they later need? A RAID configuration will not help you if any of these events occurs.

Remember…You can always replace the server but the data on that server is extremely difficult, if not impossible, to recover.

Database Backup Types

SQL Server has three main backup types: Full, Differential, and Log. In addition to the main three that backup the whole database, their are also a few additional types of backups that can be used to backup a single file or a filegroup.

Full Backups – a full backup copies all the extents from the database. If you need to restore your database and are using full backups, you only need to apply the last full backup. However, full backups are the slowest of all the backup types to make.

Differential Backups – a differential backup only backs up the extents that have changed from the last full backup. If you need to restore your database and are using differential backups, you will need to apply the last full backup and then the last differential backup you have made. Differential backups are faster to create, but they take longer to restore than a full backup because you have to apply the full backup and then the differential backup.

Log Backups – a log backup is used to backup the transaction log from the last full or transaction log backup. You may or may not be able to make log backups, it depends on the recovery model you are using. If you need to restore your database and you are using full and log backups, you would need to restore the last full backup and all (in order) the transaction log backups.

One thing to note about backups is that they are done with the database online. This is called a “fuzzy backup” because the backup is not from a single point in time. The backup copies extents from the database and if any modifications are made, the backup just continues copying. To keep consistency, full and differential backups capture the portion of the log file from the point the backup started to the end of the backup.

SQL Server can backup to a file on your hard drive, a file on a network drive, a tape drive, or a named pipe.