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

  • 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

  • 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?

  • .

    • This reply was modified 3 weeks, 5 days ago by  VastSQL.
  • 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" 😉

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

  • 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

  • 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.

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

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