How to improve the performance of database backup?

Today I am going to show you some examples of backuping databases. You will see four examples with different parameters.

I chose sample database "Pigeons", size of DB is 236 GB. So, Here we go!

First of all, I would like to show you fundamental backup via T-SQL.

With parameter "NO_COMPRESSION". As you can see below, the size of .bak file is the same like the size of the database, time is about 75 minutes.

And "STATS = 5" means that in the message window you can see duration backup after five percent.

In the second example. I want to show you how long it will take backup with parameters "COMPRESSION"! Here si Query:

In front of "COMPRESSION" is parameter "COPY_ONLY".

COPY_ONLY - is a very convenient parameter. If you have implemented a backup plan and you need to do ad-hoc backup for example for testing environment you have to use this parameter, because violation may occur consistency your backup plan.

The result of a backup with compression looks like this:

It is a good to see the acceleration and reduction of the backup size. The backup file has almost 43 GB and was completed in 37 minutes.

It is five times smaller and took hlaf the time the previous backup did without compression!

The third option is to split the backup file into several backup files. Perhaps to 10 BAK files. T-SQL is the following:

Of course with "COMPRESSION" "COPY_ONLY".

And the result? Even better than the previous example!

When we count all 10 files size it is 40,8 GB and the duration of the backup is 32 minutes. Which is a little bit better then the backup with 1 .bak file.

In the last example I am going to introduce three parameters is which you can further improve the performance of backups.

BLOCKSIZE - choose size 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536. (in KB)

BUFFERCOUNT - specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process. - from MSDN

MAXTRNASFERSIZE - it's from 65536 bytes (64 KB) to 4194304 bytes (4 MB)

You have to experiment with the sizes of parameters. It will depend on the type of your storage.

Thank you Mr. Chmel for the excelent lecture!

I hope you learn something new.