Anybody have recommendations for backing up a large number of databases?

Question

Anybody have recommendations for backing up a large number of databases?

steal

SSC Eights!

Points: 827
More actions
October 13, 2023 at 12:11 am

#4307050
We have in the neighborhood of 10k databases, and currently use Rubrik to take our backups (full and trans).
We recently had to restore multiple dates for some of our dbs, over 10k dbs in total, to recover data (thankfully the dbs are small). Luckily it wasn't an urgent situation- the restores took three weeks (running via powershell essentially 24/7). I didn't realize prior to this exercise that Rubrik can only restore 3 dbs at a time.
To others out there also managing a large number of databases, does anyone out there have a backup/restore solution that you're happy with? While Rubrik works well for our VM-level backups, it's not a good fit for our SQL needs.
- This topic was modified 1 year, 2 months ago by steal.
October 13, 2023 at 4:14 am

This was removed by the editor as SPAM
October 13, 2023 at 6:23 pm

This was removed by the editor as SPAM
November 10, 2023 at 8:08 am

This was removed by the editor as SPAM
November 10, 2023 at 8:15 am

This was removed by the editor as SPAM
November 10, 2023 at 8:18 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

Yes. Ola Hallengren's backup solution. It's good and it's free. I'm going to recommend that you do what a lot of people seem to not do... STUDY HIS DOCUMENTATION BEFORE USING IT! 😀

https://ola.hallengren.com/sql-server-backup.html

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 2

Ola Hallengren's scripts alone won't address the restore speed of 10k databases.

Even if your databases are on the megabyte scale and you use PowerShell to loop through your backups to restore one DB at a time, it could easily take an hour with the overhead of iterating through each backup and starting each restore.

Adding differential and log backups to each restore can add drastically more time to recovery.

You will need to tune your backup schedules and routines, and restore procedures to achieve your RTO goals and continually revisit them to ensure the plan still works as you add more databases.

I haven't had a need to have a restore plan for that many databases, but I have needed to create a restore plan to restore 4 databases with 1 minute log backups in a period of 30 minutes. It wasn't possible with a weekly full, a daily differential and 1440 log backups per database per day. You may need to add more full backups and differentials to reduce the number of backup sets needed for recovery in order to achieve what you need.

As an aside for Rubrik - I have only had felt good about the management of SQL backups being totally turned over to a third-party backup with CommVault which is just an orchestrator for native SQL backups (VDI or VSS). Provided the snapshot backups are good, I always felt there were more options and control over doing the fulls with the third-party backup and then continuing to do differentials and logs in SQL

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 3

Ola's scripts will give you files. You can use PoSh to to restores in parallel with Start-Process or something similar, but you'd need a way to parse and script each set of dbs to restore.

What I'd recommend.

Move to file based backups, something like Ola's
Build a PoSh script that can restore up to 10 dbs, given a list of names. This should know how to find the location of files, handle full+diff_log, new location (instance), etc.
When you need faster restores, have a way to get a list of dbs to restore, parse those into separate groups of 10, then run your process from #1 in parallel from different machines.

Keep in mind that if all your backups are in the same place, then you might have network/disk bottlenecks that limit the totals, but I'd suspect most modern disk/networks can keep up with multiple restores at a time, certainly more than 3.