Backing up and Restoring a 3 terabyte database takes over 5 hours

Question

Backing up and Restoring a 3 terabyte database takes over 5 hours

ericwenger1

Hall of Fame

Points: 3436
More actions
April 22, 2024 at 11:23 am

#4414028

I have a 3 terabyte database that is taking over 5 hours to backup and restore. I'm utilizing multiple files to do the backup. We are on an Azure VM and backing up to Azure Storage. Just wondering if there is anything else I can do to speed up the backup restore of this database.

Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

ericwenger1 wrote:

I have a 3 terabyte database that is taking over 5 hours to backup and restore. I'm utilizing multiple files to do the backup. We are on an Azure VM and backing up to Azure Storage. Just wondering if there is anything else I can do to speed up the backup restore of this database.
Thanks in advance

Hi,

Total of 5 hours is for backup & restore or you are concerned about the restore taking 5 hours? Whats the spec of VM?

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

.

This reply was modified 7 months ago by VastSQL.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

ericwenger1 wrote:

I have a 3 terabyte database that is taking over 5 hours to backup and restore. I'm utilizing multiple files to do the backup. We are on an Azure VM and backing up to Azure Storage. Just wondering if there is anything else I can do to speed up the backup restore of this database.
Thanks in advance

has the database been configured with any filegroups\files

is it 5 hours for backup and restore

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

I can't speak for the cloud but I'm backing up a 4.7 TB database over NAS on-prem in 1 hour and 13 minutes. It's a single file backup but the database has several large filegroups/files in it. The backups are compressed and they use the following hints...

, BUFFERCOUNT=17, MAXTRANSFERSIZE=1048576

Of course, our wonderful NetOps team built a network that can handle the throughput, as well.

--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)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Provided you have a fairly good sized VM I am curious if instant file initialization is enabled? I know this can make a HUGE difference in how long it takes to restore, especially very large databases.

You can look with this code:

SELECT *
FROM   master.sys.dm_server_services

Look for your service in the list and the last field is "instant_file_initialization_enabled", this should be "Y", otherwise you need to get that enabled which requires a SQL restart.

The backups and restores to azure blobs is fairly fast and works great BUT big restores without that feature set requires a certain amount of prep work for every page of data it lays down before it even writes the data..

CEWII

TL SSC-Addicted Points: 469 More actions · Answer 6

If your cloud is Azure.....

Another thing to look at is weather you are exceeding the maximum I/O throughput threshold for either the VM or the destination disk for your backups. Once you exceed it for a period of time your I/O will get throttled by Azure. We ran into this issue often after moving to the cloud. It can be an elusive problem especially if you don't have access to the Azure console for the subscription where the VM lives. If that's the situation about the only way you can see it at the VM level is to look at the Disk tab in Resource Monitor. When disk level throttling happens your Active Time % will be at or near 100%, and you'll have a sustained Disk Queue Length of over 1. Since Azure doesn't let you just turn up a dial to increase maximum throughput, you will need to either up your VM type, or disk I/O (size or disk type) depending on if it is a disk or VM bottleneck.