This post is part of a series called “TITLO – Things I learned teaching others“

Have you ever wondered if your database backups are taking too long to back up and that, perhaps, there is a way to make them complete quicker?

You haven’t? Firstly, consider why you would want backups to complete faster.

Why you want faster backups

• A faster backup can often mean a shorter maintenance window. In some cases, you may simply not have a choice – if your off-peak hours are only 12-4 AM, you need to find a way to complete your backup in those 4 hours.
• A faster backup can some times put more strain on the server during off-peak hours, but the trade-off is that it means less strain during working hours
• There is a reduced risk of data-loss. If your backup takes 12 hours to complete, remember that you are relatively unprotected for those 12 hours until the backup finishes.

Now that we’ve established that you want a faster backup, let’s look at some ways of accomplishing this.

Basic ways to make backups go faster

Turn on backup compression

This option has been available even in Standard edition since SQL Server 2008 R2. It allow SQL Server to compress the data as it writes the backup. This gives you a smaller and more manageable backup file. But more importantly, a compressed backup actually finishes faster. Though this may sound counter-intuitive, think of it this way: a smaller file means less I/O. SQL Server uses the CPU for compression, while nearly all backups are usually bottle-necked at either disk or network. Because there are fewer bytes to write, there is less contention at the disk or network endpoint and this leads to a shorter completion time!

Since compression requires significant CPU usage, make sure you have enough spare CPU capacity during this period.

Read more about it on MSDN here: https://msdn.microsoft.com/en-us/library/bb964719.aspx

Striping backups across multiple files

Many people aren’t aware that you can specify multiple files as your backup destination. SQL Server will split your backup across these files, which can often lead to much faster backups. Depending on your RAID setup, you may see a significant boost with multiple files even on the same drive; but for best performance, use one file per physical volume or LUN. If you are backing up across the network, use one file per NIC.

Keep in mind that you will need all the files from that backup in order to restore your database.

For more information, read this excellent guide by Thomas LaRock: https://www.mssqltips.com/sqlservertip/1941/striping-sql-server-database-backups/

Advanced options

If you’ve exhausted the basic options, then you can try changing advanced settings such as MaxTransferSize, BufferCount and BlockSize. These are truly YMMV settings, so no single configuration is known to improve performance for every one. Take your time to figure out how these work and what will work best in your own environment.

A word of warning here: MaxTransferSize and BufferCount determine how much memory is required for the backup operation. Don’t go crazy with these settings or you could run out of memory!

Another tip: these options are also available for the RESTORE command. Test them to see if you can improve restore performance; after all, that is exactly when you want things to go as fast as possible!

Here’s a slightly dated but still relevant post on these settings: http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

This post is part of a series called “TITLO – Things I learned teaching others“

I recently gave a talk at the UAE SQL Server on SQL Server backups. The subtitle for the presentation sums it up pretty accurately:

“Save your career. Understand how backups work.”

One of the most interesting questions that came up during the presentation was “what data is in my backup?”; specifically relating to full backups.

It is common for backups to take a few hours to complete. Take a simple example. If your backup starts at 2 AM and finishes at 6 AM, what data is included in the backup? Remember we are talking about a full database backup (as opposed to a differential, log or file backup). Your options are:

1. Data as of 2 AM
2. Data as of 6 AM
3. Differs from table to table: each table was backed up at a different time between 2 AM and 6 AM, so it will contain data for that table as of that time.

In other words, if you restore this backup, what data can you expect to see in the newly-restored database? Data from 2 AM, 6 AM or something in between?

Surprisingly, most people got the answer wrong. If you answered option 2, pat yourself on the back – you got it right.

A full backup is consistent to the point when the backup finished. It does this by tracking the LSN of the transaction log when it began and comparing it with the LSN when it is nearly done. It then replays those transaction log records into the backup copy before declaring the backup complete.

This means that the transaction log cannot be cleared while a full backup is in progress, even if you take a transaction log backup during this period!

For years, I’ve been itching to start a blog so that people around the world can hear me rant. Jokes aside, there is a serious purpose to this blog, I assure you.

I’ve been closely involved with the IT community in the UAE for nearly two decades now. For the longest time, I was simply a passive consumer, religiously attending seminars, conferences and bootcamps, but one thing always bothered me. Why was user engagement so low? Why were there so few people in these events? Why weren’t there enough community-led events?

Eventually, I realised that the answer to all those questions was ME. If nobody else was going to fix these problems, I had no right to complain until I had tried to fix them myself. So, I became an active evangelist for the things I truly loved: ASP.net, cloud computing, SQL Server. I took over leadership of the UAE SQL Server group and it has been a humbling experience to realise how much work goes into running a community.

I’ve now been running the UAE SSUG for nearly a year and I’ve learned so much! There’s a perpetual shortage of willing speakers here and I often found myself as the only available speaker for a lot of sessions. This turned out to be a great experience  – I’ve learned a lot of things trying to teach them to others.

Now I want to use this blog to spread this knowledge as far as I can. I’m starting with a series of blog posts called “Things I learned teaching others” or TILTO for short (mmmmm…catchy)!  These are short posts covering useful things I have learnt from preparing these sessions or presenting them.

If you found any of the information useful, please leave a comment and share it with other people.