Some tips for using backup and restore in SQL Server 2016
Consider using the simple recovery model to minimize the backup/restore time and
minimize administrative overhead for the transaction log.
Under the simple recovery model the transaction log is not backed up. So, the data is
recoverable only to the most recent backup of the lost data and the point-in-time recovery
is not supported. Usually, the simple recovery model is used for test and development
databases or for databases containing mostly read-only data, such as a data warehouse.
Use differential backup instead of incremental backup when the users update the same
data many times.
Because a differential backup captures only those data pages that have changed after
the last database backup, you can eliminate much of the time the server spends rolling
transactions forward when recovering transaction logs from the incremental backups.
Using differential backup, in this case, can improve the recovery process in several times.
Consider storing the backup files on physical disks on another computer.
Storing the backup files on the same computer where the databases stores may cause
problem with restoring databases if the physical disks were damaged.
Use SQL Server 2016 backup encryption.
In SQL Server 2016 you can choose to encrypt the backup file during a backup operation.
It supports several encryption algorithms including AES 128, AES 192, AES 256, and
Triple DES. You must use either a certificate or an asymmetric key to perform encryption
during backup.
Consider using backup compression.
SQL Server 2016 supports compressing backups. By using backup compression, you can
reduce the total disk storage space.
Note. Keep in mind, that compression is available only in the SQL Server 2016 Enterprise,
Business Intelligence and Standard editions, but every edition of SQL Server 2016 can
restore a compressed backup.
Consider using the piecemeal restore.
The piecemeal restore scenario is used to restore and recover the database in stages at
the filegroup level, starting with the primary and all read/write secondary filegroups.
The piecemeal restore scenario is used only with the full or bulk-logged recovery models.
Use the full recovery model for the most production databases that are not read-only.
The full recovery model fully logs all transactions and retains the transaction log
records until after they are backed up. This recovery model allows a database to be
recovered to the point of failure and supports restoring individual data pages.
Perform backup on multiple backup devices.
Using multiple backup devices forces SQL Server to create a separate backup thread
for each backup device, so the backups will be written to all backup devices in parallel.
Use full backup to minimize the time to restore databases.
The full backups take the longest to perform in comparison with differential and
incremental backups, but are the fastest to restore.
Use incremental backup to minimize the time to backup databases.
The incremental backups take the fastest to perform in comparison with full and
differential backups, but are the longest to restore.
Consider using page restores.
SQL Server Management Studio supports page restores in SQL Server 2016. Now you can
check database pages for corruption and restore selected corrupt pages from a database
backup and subsequent log backups.
Use the Database Recovery Advisor to construct restore plans that implement optimal
correct restore sequences.
For example, the Database Recovery Advisor greatly simplifies restoring a database to a
given point in time. You can launch the Database Recovery Advisor from the SQL Server 2016
Management Studio.
Consider using SQL Server backup (restore) to (from) URL.
In SQL Server 2016 you can use SQL Server Management Studio to backup to or restore from
Windows Azure Blob storage service. This option is available both for the Backup task and
maintenance plans. In comparison with previous version, in SQL Server 2016, SQL Server
backup to URL using the Windows Azure Blob storage service now supports striped backups
sets using block blobs to support a maximum backup size of 12.8 TB.
Use SQL Server 2016 snapshot backups for the very large databases.
The SQL Server 2016 snapshot backup and restore technologies work in conjunction with third
party hardware and software vendors. The main advantages of snapshot backups and restores
are that they can be done in a very short time, typically measured in seconds, not hours,
and reduce the backup/restore impact on the overall server performance. The snapshot backups
accomplished by splitting a mirrored set of disks or creating a copy of a disk block when
it is written and required the special hardware and software.
Consider using the COPY_ONLY backups.
The copy-only backups do not affect the normal sequence of backups. These backups used in
situations in which a backup is taken for a special purpose, such as backing up the log
before an online file restore.
Note. Keep in mind, when DIFFERENTIAL and COPY_ONLY are used together, COPY_ONLY
is ignored, and a differential backup will be created.
Try to separate your database to different files and filegroups to backing up only
appropriate file/filegroup.
This can results in smaller backup operation’s time. The smaller backup operation’s
time is, the less impact there will be on the server when the backup occurs.
Perform backups during periods of low database access.
Because backup is very resource effective, try to schedule it during CPU idle time and slow
production periods.