Microsoft SQL Server 2014 -Restaurar Respaldo de 2 TB es muy lento

Question

Hola a todos:

Tengo instalado Microsoft SQL Server 2014 Ent, maquina de 8vCPU's y 46 GB Memoria RAM configurado para la instancia, dejando libre el 20% para el sistema operativo (IaaS).

El problema que tengo es que tarda en restaurar 24 hrs el respaldo, y no he encontrado alguna forma de agilizar el proceso.

Alguien tendrá alguna sugerencia que me ayude a como agilizar el procesos de restauración?

De antemano les agradezco.

Saludos.

Answer 1

Hi @Adrian Bautista ,

Here’s a list of things you can do to make restoring a full backup go faster:

• Ensure that instant file initialization is enabled on the SQL Server instance performing the restore operation, to avoid spending time zero-initializing any data files that must be created. This can save hours of downtime for very large data files.
• Consider backup compression, which can speed up both backup and restore operations, and save disk space and storage costs.
• Consider using multiple backup files, each on a separate volume. SQL Server will recognize this situation and use parallel write threads (one per volume) to write to the files during the backup, and to read from them during the restore – speeding things up. If you have multiple database data files, a similar I/O parallelism will occur – providing even more of a speed boost.
• Try to avoid having long-running transactions that will take time to roll back.
• Manage your transaction log to avoid having an excessive number of virtual log files, so if there are transactions to roll back, the roll back will go as fast as possible.

Please refer to the blog to get more detail information.

How did you backup and restore database? Perform an on-premises backup using compression, and then manually copy the backup file into the Azure VM, then restore the backup file? Or perform a backup to URL and then restore into the Azure VM from the URL?

For best data transfer performance, migrate the database files into the Azure VM using a compressed backup file is a good choice. If your backup file is larger than 1 TB, you must create a striped set because the maximum size of a VM disk is 1 TB. Please refer to the MS document Back up and restore to get detail steps.

If you use Backup to URL and then Restore from URL to the VM. SQL Server 2016 supports striped backup sets. They're recommended for performance and required to exceed the size limits per blob. For very large databases, the use of the Windows Import/Export Service is recommended. Please refer to Backup to URL and Restore from URL to get detail information.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

La lengua de este forum es inglese. Mi español no es suficiente bueno por un repuesto en español.

(The language of this forum is English. My Spanish is not good enough for an answer in Spanish.)

You say that it is IaaS. Is that an Azure VM, or a another cloud provider?

From where are you restoring the backup?

What disks do you have on your VM?

Answer 3

Hi ErlandSommarskog

Is that an Azure VM, or a another cloud provider?
Azure VM

From where are you restoring the backup?
From my virtual machine disks

What disks do you have on your VM?
Standard DS13 v2 (SSD Premium)