This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!
The BACKUP command has a lot of different options so in today’s post I want to introduce a few of the most useful options to you.
COMPRESSION
Backup compression was added in SQL Server 2008 in Enterprise Edition, and then in Standard Edition as well from SQL Server 2008 R2 onward. Not only can backup compression make your backups smaller, it can also speed up your backup operations AND speed up your restore operations. In that way, backup compression can be thought of as an availability feature, as anything that reduces downtime makes the data more available. The trade-off is that compressing and decompressing backups uses more CPU (you don’t get anything for free!) but most SQL Server systems these days are I/O-bound rather than CPU-bound so this is usually not a problem.
You can read more about my thoughts on backup compression in this post, including whether to enable it by default for the entire SQL Server instance. If you’re going to use it, make sure that the backups you’re using it for actually get a decent compression ratio (e.g. 20% or above) otherwise the extra CPU being used isn’t worth it.
COPY_ONLY
Once you’ve figured out what backups you need to satisfy your downtime and data-loss requirements (see the post from day 8), you’ll either have manually created SQL Agent jobs to perform regular backups, used one of the free database maintenance solutions available online (like this one, which I like to recommend), or created a database maintenance plan using SQL Server Management Studio.
If at any time you want to take a one-off, out-of-band backup, you need to be very careful how you do it. If you perform a full backup, that full backup becomes the basis for subsequent differential backups. If you perform a log backup, that becomes part of the log backup chain. In both cases, these backups may be required during a disaster recovery operation.
To avoid having one-off backups affecting your differential backups or your log backup chain, you can use the COPY_ONLY option. For a full backup, this option makes the backup NOT become the basis for subsequent differential backups, and for a log backup, this option make the backup NOT become part of the log backup chain.
On any database where automated, regular backups are being performed, always use the COPY_ONLY option unless you have a very good reason not to. Better to be safe than sorry.
DESCRIPTION and File Names
It’s a really good practice to ensure your backup files a descriptively named – e.g. 20130609_1330_SalesDB_Log.bak – and that you set the DESCRIPTION option appropriately. Using the DESCRIPTION field is especially important if you perform a manual backup, so you have some idea of why the backup was taken and what it includes.
Note that my example file name ended with .bak. You can call your files anything you want, but .bak or .bck are commonly for data backups, and .trn is commonly used for log backups.
CHECKSUM
This option does two things:
- As the data file pages are being read from the data files, their page checksums are validated. If an invalid checksum is found, by default the backup will fail and report a message indicating which page is corrupt.
- Calculate a checksum over the entire backup and store it in the backup header.
As an aside, page checksums are a checksum written to each data file page just before it is written to disk and checked when a page is read from disk. It’s a way for SQL Server to determine that the I/O subsystem has corrupted a page.
It’s extremely important that your backups are valid and do not contain corruption, so this option should definitely be used on all backups you perform, manual or automated. By ensuring that the backup contains valid pages when the backup is performed, the backup can be verified later with more extensive checking. In tomorrow’s post I’ll discuss backup validation in more depth.
STATS
This option makes the BACKUP command print progress information as it executes. The default is to print every 10% through the operation and you can change that by using STATS = X, where X is the percentage interval between progress prints. This is especially useful when you’re performing manual backups of large databases and you want some indication that progress is being made. Incidentally, this option also applies to the RESTORE command too.
Summary
This post has just scratched the surface – you can get more information on all the BACKUP options from SQL Server Books Online here. Make sure that you know what your backup commands are doing, and especially make sure that each backup is going to a new file, which is named descriptively, and that existing files are not being overwritten using the INIT option.
Additionally, and we’re not covering it in this series but we do cover it in the Immersion Event, you should investigate the RESTORE command (see Books Online here) and make sure you’ve tried basic restore operations – that is the reason you’re taking backups after all!
3 thoughts on “The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options”
The last paragraph under COMPRESSION has a link to another post supposed to contain “whether to enable it by default for the entire SQL Server instance”. I couldn’t see that in the other post, and would interested in knowing what you think.
With the options of MEDIAPASSWORD and PASSWORD gone as options for backing up databases starting SQL 2012, what is it that you suggest we do to secure backups? Does turning on TDE affect the backup size, specially if I want to have the backups compressed as well?
Neither of those options actually secured the contents of the backup. The only way to secure backups is to encrypt the data – either with TDE and then backing up the database, or creating an encrypted backup of the database. TDE doesn’t affect backup size per se, but backup compression will not compress a backup of a TDE-encrypted database.