Here you are. Through some quirk of fate, you’re now responsible for your company’s databases. People are referring to you as the DBA. You’ve either volunteered or had the job thrust upon you. Either way, you’re expected to know what to do and, let’s face it, figuring out where to start is hard. Let me make a suggestion. The very first thing you should check on is the state of your backups. Yes, there are probably a million screaming issues and every single one of them is important, but if your database fails, which they do, and you have no backup…well, let’s just say that’s not a conversation you want to have with management.

So now you’re prepared to get started with backups, but you’re unsure of even where to begin. The good news is, this is a well-worn path. Database backups are not a mystery. Again, let me proffer several suggestions.

Start with the business

Getting your database back online and operational is first and foremost a business decision, not a technical one. Setting up the technical aspects of backup and recovery is relatively easy and extremely well documented, so your initial work needs to be with the business, understanding what you have to deal with there. This serves two purposes. First, you learn what you need to do and just how much infrastructure, planning, and work you have in front of you. Second, you can document what’s expected of you, so that if, at some later date, the business questions your technical decisions, you can point back to the discussions you had with them. Here are a set of suggested questions you might want to ask about any given database:

• How important is this database to the company?
• How much time and data can we afford to lose in this database?
• Are there any regulatory requirements regarding keeping backups of the data I need to meet?
• Are there any encryption requirements for data backups?

It’s the answers to these questions that begin to drive you down the path to setting up a backup and recovery plan for all the databases that you manage. The business will be able to answer some of these questions quickly and easily. Some they won’t. You may have to badger them to get the answers that you need, but you do need to get those answers. Also, you may not be able to work with some answers. For example, the most frequent answer to “How much data can we lose?” is “none.” While that may be true, attempting to set up a system with zero chance of data loss is extremely expensive and involves a lot more than simple backup and recovery. Usually you can get the business to agree that a day, or an hour, or 5 minutes would be reasonable expected losses.

Out of all this information you should create a set of documentation describing your backup processes in plain language. You may have a general process that’s applicable to all your systems and databases or you might have customized processes for individual databases or applications. Either way, write everything down.

Once you understand the task you have in front of you to satisfy the business, then you can begin to apply technology to the problem. Just remember, as your data expands, grows, and changes, so will the business. Plan on going back to them on a regular basis, at least once a year, to reassess and update your plans as needed.

Full Backups

All backup plans start with a full backup. A full backup is a low level copy of a database, not only the tables and data, but the indexes, their statistics, all the triggers and stored procedures, and everything that is stored in the database. There is no way for you to arbitrarily pick and choose objects to include in the backup, so the backup will always cover everything, and when you restore the backup, everything will be restored. There are lots of options for a full backup, but here are a few things to remember:

• For recovery to a point in time, the full backup is the base.
• Add CHECKSUM to partially validate backups as they occur.
  NOTE: This will have a noticeable performance impact on larger backup operations.
• You should only backup the database after a consistency check (DBCC CHECKDB).
  NOTE: An exception to this would be if you use the backup itself for a consistency check.
• The full backup does not truncate the log when the database is in FULL or BULK LOGGED recovery modes.

How often you run a full backup is very dependent on the needs of the business. In order to reduce the difficulty and time to recover a backup, for a full backup is recommended at least once a week, but only in conjunction with other backup processes. For small and medium sized databases, up to approximately 500gb, daily full backups might be a better plan.

Backups can be configured to stack multiple full backups into a single file or they can be configured to have each backup in an individual file. I recommend using individual files, preferably named by date as well as database name. It’s easier to see which backups are available and which are missing, if any. If a file becomes corrupt for some reason, you lose a single backup, not all of them.

Just remember, while there are all sorts of other types of backup operations, the foundation of most of them is the full backup. You will need to take that into account on most of your systems for most backup plans.

Log Backups

Some businesses are fine with only having a full backup and living with the possibility of data loss between whenever a problem occurs and the last full backup. Most businesses are reliant on much more up-to-date data. Because of this, you need to plan for the ability to recover a database to a point in time. This is possible through the use of the FULL recovery mode and a set of log backups. The database must first be set to either FULL or BULK LOGGED recovery. These modes of recovery cause the log to be kept intact until a log backup operation is performed. There are many different options around backing up logs, but a few should be noted:

• In order for the committed transactions to be cleared, a log backup operation must be performed.
• Log backups can use the CHECKSUM operation to partially validate the backup as it occurs.
  NOTE: This will have a noticeable impact on larger log backup operations.

The frequency with which log backups are taken must be determined by the business. Log backups are dependent on having a full backup in place as part of the recovery process. You must run regular log backups in order to truncate the committed transactions within the log. The minimum suggestion would be once per hour. Most businesses operate more in the neighborhood of once every fifteen minutes. I’ve even managed a system that ran a log backup once every five minutes. Less than that is counter-productive, because the amount of time needed to restore the data will be prohibitive.

Log backups, like full backups, can be configured to all go to a single file or to multiple individual files. Again, I recommend multiple individual files for the same reasons as before.

Automating the Process

The best way to automate your backup processes is to learn the T-SQL commands and go to work on setting up SQL Agent jobs yourself. Doing this gives you a very high level of control and flexibility. However, that requires a lot of time to develop a good set of knowledge, especially for the accidental DBA. Instead, you can take advantage of built-in automation processes called Maintenance Plans.

The Maintenance Plans have a wizard to walk you through the set-up. It will even schedule things for you. Here are a few notes about using Maintenance Plans:

• Choose “Separate Schedule for each task” on the opening screen of the wizard.
• Do not implement the Shrink Database step.
  NOTE: This is a long and involved discussion. For more, read this article.
• Separate the backups from the other operations in order to minimize resource impact.
• Enable “Verify Backup Integrity”.
  NOTE: This is the CHECKSUM option, so it may impact performance.

Once the wizard has created the Maintenance Plan, you can spend a lot of time and effort working with them directly through the GUI interface in SSMS. You have more control over these mechanisms than ever before. But, for extreme fine-grained control you will need to use T-SQL directly and work with SQL Agent to schedule the events.

SQL Agent can not only be scheduled to run any command you want, but it will retry the commands on an error. It can also be programmed to send you emails in the event of a failure. You can work with the scheduled items that you create yourself or even modify those created automatically for you through the Maintenance Plans wizard.

Full Restore

The most important part of setting up database backups is not the backup operation. The most important part of setting up database backups is being able to restore those backups. This means two things. First, that you verify that the backup files are valid , and can be restored. Second, that you know how to restore these files. Restoring databases is very much like being in a fight. If you practice boxing or some type of martial art you’ll know that you practice performing the moves in the air with other people slowly, and with other people at as close to full speed as possible without serious injury. This is all to learn how to move before you have to do it under stress. Restoring a database is exactly the same. There will be plenty of times that you may need to run a restore in a non-stressful situation, but you’ll remember the ones when the entire management team is standing in your cube waiting for you to save the company. That is not the time to be searching for a blog post on how to run restores.

Here are a few notes about restoring databases:

• Practice, practice, practice.
• Use the VERIFY ONLY command to partially validate backups.
  NOTE: This will only partially validate the file. The only true validation is a restore operation.
• RESTORE FILELISTONLY will allow you to see the definition of files for the database that was backed up.
• RESTORE HEADERONLY will give you information about the backup, other than the files.
• MOVE allows you to create a copy of a database by moving the database files to other locations or other files.

Just remember that the full restore is not selective. You will get everything that was in the backup. If a particular row, table, or procedure is missing, that’s because it wasn’t in the backup.

Point In Time Restore

Because data may have changed since the last full backup, you configure your database to support log backups. When the time comes to restore the logs, you can restore to a specific point in time. Even more practice is required with the point in time recovery because so many more steps are involved. Here are some key points to remember:

• Practice, practice, practice.
• You must first restore a full backup, but it must be left in the Recovering state.
• Until you reach the final log backup, each section of the log that is restored must be left in a Recovery state.
• You must restore the logs in the order in which they were taken. You cannot skip sections of time.

Compression

One of the most important additions in recent years is the ability to compress backups. Compression helps conserve disk space but more importantly, it sacrifices some memory and CPU cycles to actually arrive at faster backup execution speeds. This is because less data is written to the disk and disks are the slowest part of the system. Because of this, unless your system is suffering extreme CPU or memory loads, it’s worth using compression on all of your backup operations.

The only problem with this is that backup compression is only available for certain versions of SQL Server. Compression was introduced with SQL Server 2008 Enterprise. When Service Pack #2 was released, they made compression available for SQL Server 2008 Standard. SQL Server 2008 R2 has the same restrictions, compression on Enterprise and Standard only. The Development version, since it’s effectively the Enterprise version, also has compression available. No other versions of SQL Server can create compressed backups or restore compressed backups.

Additional Backup Considerations

This introduction only covers the bare bones of standard backup and restore operations. There are an extremely large number of additional topics that you can add to the mix. This is a small sampling of the more important aspects:

Differential Backups

Full backups can take a lot of time, and log backups only involved transactions that have yet to be backed up. Differential backups provide a middle ground between the two. A differential backup retrieves all the committed data that has changed since the last full backup, and creates it in a backup, representing the difference between the current moment and the last full backup. Differential backups can then be used in restore operations to either bring the database up to the moment of the differential, like applying a log backup, or in conjunction with log backups. Like log backups, the full backup must be left in a recovering state in order to apply differential backups. For larger systems, these backup types can add additional flexibility to your disaster recovery planning.

File and File Group Backups

While it’s not possible to backup individual pieces of a database, such as just one table, there is a way to sort of get around this. It is possible to back up each file or file group independently of the others, either through a full or differential backup. These backups will back up everything that is on a given file or filegroup. You can then restore just the file or filegroup to the database. If only a single table is stored on that file group, then you can restore just that table. There are a number of restrictions around it, but again, for larger systems, this provides additional flexibility and power for planning your backup schedules.

Snapshot

Snapshot backups are not so much a part of disaster recovery as they are a mechanism of safety during specialized operations on the database such as deployments. Essentially a snapshot creates a moment in time copy of the database, very quickly. You can then, just as quickly, restore the snapshot to the database. These are not traditionally used in disaster recovery scenarios since the snapshot only contains changes made to the database. There is no way a snapshot can be restored unless the underlying database is already in place, the opposite of a disaster recovery scenario.

Copy Only

Since a full backup is the basis on which point in time recovery is built, whenever you take a full backup you introduce a new starting point for all the log and differential backups. Sometimes you might just want to take a backup of the database without affecting this chain. Using the COPY ONLY command allows you to do this.

Striping

You are not forced to write your backups to a single file on a single disk drive. You can set them up to stripe across multiple files on multiple drives. This is a way for larger systems to get a full backup in place and a mechanism for somewhat, but not drastically, faster backups.

Encryption

Most businesses require you to password-protect your systems. It’s also possible they may require you encrypt backups. To do this using SQL Server you have to encrypt the database, but then the backups will be encrypted as well.

Conclusion

Backups are a very important part of the DBA’s duties, but the most important part of backups is actually the restore. You need to know that you have good backups in place and that you can restore them. You must also work with the business in order to arrive at a good backup plan. Once you have it in place, make sure you validate your backups to ensure they work, because it is all on your shoulders. You’re the DBA.

The Checklist

• Full backups running on a regular basis.
• Full backup to individual files.
• Use CHECKSUM with full backup.
• DBCC run as part of full backup processing.
• When database is in FULL or BULK LOGGED recovery, run log backups.
• Log backup to individual files.
• Use CHECKSUM with log backup.
• Minimum frequency on log backups is one per hour.
• Automate the backup process.
• Practice restore operations frequently.
• Validate backups by restoring database.
• Use VERIFYONLY for partial validation of backups.
• Use backup compression when available.
• Document backup and restore processes.