How can you reduce backup times for databases with high transaction rates?

There has to be a balance between Full and Incremental Backups. Do not blindly accept a "Full Backup on weekend" and "Incremental Backup daily" strategy that your backup tool / administrator implements. Some databases may need more frequent full backups. Remember to consider your archive/transaction/wal log backups -- should they be "every half hour" or "every 4 hours". Not all database should use the same "template". Your backup frequency should be designed based on RTO/RPO and backup performance (see the other questions in this article).

In SQL Server we have 3 most known type of backups. Full, differential and TLog backups. Depending on the recovery model (Full, Simple and Bulk Logged) of the database we can plan the backups. These recovery models and backup strategies defines that the point in time recovery is possible or not.

Incremental Backups: Use incremental backups to only capture changes since the last backup. Parallel Processing: Employ backup tools that support parallel processing for simultaneous backups. Table Partitioning: Partition large tables to backup segments independently and efficiently. Faster Storage: Utilize faster storage like SSDs for quicker backup operations. Compression: Implement data compression to reduce the backup size. Off-Peak Scheduling: Schedule backups during low-activity times to leverage idle resources. Continuous Archiving: In PostgreSQL, use continuous archiving for ongoing change capture. Optimize Configurations: Tune backup settings to align with your system's capabilities.

Es la mejor opción para tener una copia de seguridad actualizada en un ambiente muy transaccional, además es la manera más rápida de restauración.

We can reduce the backup window by saving objects while they are still in use on the system or by conducting online backups. You can reduce the backup window by conducting backups from a second copy of the data. we can reduce your backup window by backing up smaller amounts of data at different times.

With the backup compression we can reduce the backup file size to a great extent depending on the SQL Server version the compression varies. As the file size will be so in case of file transfer via the network needs less network bandwidth via which the network will not choke. With the encrypted backup we protect the backup files from the unauthorized and malicious use.

La compression vise à diminuer l'espace disque occupé par les données de la table. Elle autorise le stockage d'un plus grand nombre de lignes d'informations sur un bloc de 8 ko.

If you are dealing with MS SQL (especially prior to MS SQL 2019) and performing compressed backup for a TDE-encrypted database, consider paying attention to and experimenting with the MAXTRANSFERSIZE and BUFFERCOUNT parameters.

Use parallel or multithread so that it reduce the time taken of the backup. Schedule the backup at the off-business hour. If there is standby server, please plan to take backup at the same server. Take the full backup weekly once followed by differential backup daily so that it takes less time.

* Via using parallel or multithreaded backup it reduced the time taken to take the backup. For example, split backup etc. It's reduced the IO and utilize less resources. * Complexity and chances of errors will increase.

If you have off-peak load time, use this window for your backup schedule. if you don't have one, consider the possibility of taking backup from standby databases. it could help you reduce the impact of the backup process on the live business process.

We can schedule backups during off peak business hours. As during the backup process, there will be performance hit. We can schedule the same via SQL Server Agent job. In case of database recovery those backups will play a vital role in recovery. We can also achieve RPO and RTO when we will have the proper backup and backup strategies.

Une autre stratégie consiste à déporter les backups sur un node passif dans le cadre d'une configuration HA. Cela permet de ne pas impacter le node principal et d'attribuer plus de ressource au backup diminuant ainsi le temps nécessaire.

This is only of the most critical step in the backup process. I have seen mostly DBAs ignore to verify and validate the backups. During the recovery process is if the backups files are not valid than recovery will fail and will lose the data. RESTORE VERIFYONLY FROM DISK = 'C:\AdventureWorks.BAK'

A good and useful way to validate the database backup is to use it for cloning production to a dev or test environment. By reading from the backup instead of from active database using RMAN you get a ticket that the backups are valid.

Planifiez des tests de restauration réguliers en extrayant une sauvegarde et en la restaurant dans un environnement isolé. Cela garantit que les sauvegardes peuvent être restaurées avec succès en cas de besoin réel.

Backups to local disk can be impaired by Disk I/O Backups to network locations can be impaired by Network Throughput Backup compression uses CPU cycles on the database server. You have to collect backup execution time (elapsed time) and identify bottlenecks by comparing backup sizes and durations. Even simple tools like "sar" can help. Databases themselves have tools (Wait Stats) that can provide information. Maybe the backup start time (configured by the backup administrator) conflicts with the application batch-job peak-load ? Possibly multiple concurrent backups to the same backup location (CommVault, Veeam, EMC/Dell Networker, HP) run slower than if they were serialized. These tools can also provide information.

RTO/RPO requirements are the major deciding factor. Few other important considerations would be - - Size of data - Backup storage - Performance expectations - compute capacity etc.

Infrastructure Optimization: - Utilize high-speed storage devices for both databases and backup locations, such as SSDs or NVMe drives, significantly reducing I/O bottlenecks and accelerating backup operations. - Implement a dedicated network for backups, isolating backup traffic from production workloads and preventing network congestion from impacting backup performance.

Backup optimization -have weekend full and weekdays incremental backup - compression could increase run time considerably so if the db size is small to medium go for without compression -incase of Oracle Rac dedicate the node for backup -backup job gets equal priority vs application job so schedule the window when application job execution is less -avoid any db, table maintenance job during backup - dedicated NIC for backup -periodic purge of historic data could reduce the run time..

Es importante segmentar en varios archivos la copia de seguridad full, ya que eso acelera el tiempo del bkp y de restore en caso se requiera.

Certains outils de backup propose également de la déduplication. La technologie de déduplication de sauvegarde aide à réduire les co?ts de stockage et l'utilisation de la bande passante du réseau en éliminant les blocs de données en double lorsque vous sauvegardez et transférez des données. La déduplication vous aidera à: Réduire l'utilisation de l'espace de stockage en ne stockant que des données uniques éliminer le besoin d'investir dans du matériel spécifique à la déduplication de données Réduire la charge réseau parce que moins de données sont transférées, ce qui laisse plus de bande passante pour vos taches de production.