Ola Hallengren’s free maintenance solution is widely used as a replacement for SQL Server maintenance plans. It’s a more powerful, flexible tool for backups, corruption checking, and index & statistics updates.
If you’re using it for backups, there are two quick, easy changes that can dramatically reduce your nightly job runtimes.
First, set @NumberOfFiles = 4.
SQL Server has internal bottlenecks for single-file backups, and striping your backups across multiple files removes that bottleneck. I’ve written about testing the number of files before, and in a perfect world you’d have the time to do that testing, but for starters, just try writing your backups across 4 files.
You don’t need separate drive targets or network targets – even writing the 4 files to the same volume usually produces pretty dramatic performance improvements for free.
I only do this on my user databases because the system databases are generally so small that it doesn’t matter. To set it up, just right-click on the Agent job for user database full backups, go into the step properties, and add a line for @NumberOfFiles = 4, like this:
This does mean you need all 4 files at restore time, and it means your restore scripts will be a little bit more complicated. But who cares? You’re not writing those scripts by hand in the year 2024, are you? You’re using sp_DatabaseRestore from the First Responder Kit, like a boss, just pointing it at a folder and letting it grab the most recent full, diff, and log scripts:
|
1 2 3 4 5 6 7 8 |
EXEC [dbo].[sp_DatabaseRestore] @Database = 'StackOverflow', @BackupPathFull = 'Z:\MSSQL\BACKUP\SQL2022\StackOverflow\FULL\', @BackupPathLog = 'Z:\MSSQL\BACKUP\SQL2022\StackOverflow\LOG\', @TestRestore = 1, @RunCheckDB = 1, @RunRecovery = 1, @Execute = 'Y' |
Good job. Moving on – eagle-eyed folks will notice another config change in that screenshot. This next one’s going to be a little more controversial, but hear me out.
Next, set @Verify = ‘N’.
By default, Ola’s backup jobs have @Verify = ‘Y’, which means after the backup command finishes, SQL Server reads the whole backup file to check to make sure it’s okay. This can massively extend your backup job times.
I’m not saying you shouldn’t verify your backups! I’m saying don’t verify them from the production box. Instead:
- Write your backups to a network share, like a UNC path
- Use sp_DatabaseRestore to verify them from a separate server, like DR
- Bonus points for using sp_DatabaseRestore’s @RunCheckDB = 1 parameter, which is way better than @Verify = ‘Y’
This has a few important performance & reliability benefits:
- Your production server’s nightly jobs finish faster
- You really test the hell out of the backup, making sure it’s free of corruption
- You offload that resource-intensive check work to a secondary server, not production
- Hell, you don’t even have to pay licensing for that, because as long as you’ve got Software Assurance, you can do CHECKDB on secondaries for free, regardless of what SQL Server version you’re using
Presto: those two free, simple changes might cut your backup times by 1/3 or more. You’re welcome!
16 Comments. Leave new
Always use Ola Hallengren’s backup scripts. I like the option of using RunCheckDB = 1 parameter instead of @Verify = ‘N’. Love it
Love it! I always use 4 backups, I tested 1,2,4,8… 4 fastest hands down. We had to play around to get the optimal backup speed for NAS backups but maybe these numbers will get you closer to a speedy backup.
-BufferCount 40
-BlockSize 65536
-MaxTransferSize 2097152
-NumberOfFiles 4
@Adam,
Im ‘curious… How many TB is your largest database and how long does the backup take with those settings?
As a bit of a sidebar, how many file separate files (data files… not backup files) does the database have?
2TB database can be backed up to our NAS over the network in 45 minutes. (+/- 5 minutes)
Oh the database has 4 files per major file group to follow with the number 4 is the best method approach. I am pretty sure if it doesn’t help to improve performance but i like to think it does. (16 data files? 4 file groups?) yeah kind of overkill.
In Ola’s own online documentation it says that Verify defaults to N, not Y.
It defaults to N, but the Agent job creation scripts use Y as a parameter by default, so that overrides the proc’s default.
I use the Hallengren maintenance scripts because they are stable and easy to use. I even use them to backup Express databases from a Windows task.
If you don’t want to have multiple files for smaller dbs, you can use and set this Hallengren option:
@MinBackupSizeForMultipleFiles=[Some threshold you set]
Example:
@MinBackupSizeForMultipleFiles=20000
We’ve been looking into this casually, I did some of the count and buffer size comparison a d simply change to 4 files seemed easiest. A fellow DBA asked about smaller DBs being split, this will solve that. Thanks for the tip.
We use Ola’s backup to URL and found we needed 16 containers for large backups and speed. Also, don’t forget to turn on compression which for URL backups makes a huge difference.
Can someone please tell me how to code the Ola script to backup a large DB to multiple files spread out on two different backup directories? It’s an ancient system that’s being migrated “real soon.” The drives can not be expanded and the DB has grown too big for the compressed backup to fit on the designated backup volume.
Questions like that are really too big to handle here in the comments. You’d be best off contacting Ola if you want him to modify his scripts for you. Hope that helps.
Thanks Brent, I decided to do this backup with a maintenance plan backup. It was quick and easy to set up, and It overwrites the existing 8 backup files which get copied to a DR server as soon as the backup finishes.
To be more clear, this is what I am using in the script:
@Directory = N’P:\yadayada1\MSSQL\Backup’,N’F:\yadayada2\MSSQL\Backup’,
@NumberOfFiles=8,
I have also tried it withou the Ns in front of the drive.
When striping to multiple files are you able to specify which directories each file goes to? Especially if on directory has more space than another?
For support questions on Ola Hallengren’s scripts, head on over to his site.