Ten Methods of SQL Server quick backup

SQL Server backup is a system project that takes a lot of time. As the database continues to grow during running, the corresponding backup will take more time. Generally, a database of GB is regarded as a very large database. Today, 100gb is very common, and many databases have reached the TB level. In this article, we will discuss how to quickly back up SQL Server in ten ways.
1. Back up tapes on hard disks for archiving
Backing up to a hard disk is much faster than backing up to a tape. Most experienced DBAs prefer this method. In addition to pursuing high-speed I/O rates, you also need to have the latest backup at hand for data recovery. After hard disk backup, you need to archive the data to the tape for long-term storage.
2. Back up data in your spare time
It is best to use spare time for backup. Because the database server has the least operations, the smaller the impact on performance will be. But remember, sometimes running batch jobs in your spare time may cause more pressure on the system than normal jobs. Therefore, it is very important to monitor the status of the server. Therefore, you must carefully set the time period for full backup.
3. Use compression software
The best way to back up SQL Server is to back up the hard disk and archive the tape. The disadvantage is that the backup file is usually the same size as the data file. This is also because, if you have a GB database, you need a GB hard disk space for backup. Unfortunately, SQL Server does not contain embedded compression tools. You can use the compression product, but this will consume more time. Fortunately, there are three compression tools on the market, Idera, Quest Software Inc. And Red Gate Software Ltd. Can help you create compression backups in your busy schedule. Using compression software will increase backup costs, but the benefits you get are far greater than this cost.
4. Write multiple files
Another method is to write the backup to multiple files, so that you can use multiple threads for backup. Tape manufacturers and the three companies mentioned above provide this service. Multi-task backup can be performed faster. It does not compress the backup files, but greatly reduces the time used.
5. Write Data to multiple physical disk drives
I/O devices are frequently backed up. Each database file must be read and written to another file. With multiple physical hard disks, you can achieve high I/O rates and complete backup faster. In addition to writing multiple files, you can also write multiple physical hard disks to handle I/O performance bottlenecks.
6. Run file or File Group Backup
SQL Server provides another backup option-file or file group backup. This method is determined by the initial settings of the database. If you have created multiple files or multiple file groups when you set up a database, you can back up only some databases without backing up the entire database. This method may increase complexity and security risks. Therefore, you must make a plan before using this method for backup.
7. Create a snapshot
Snapshot is another backup method provided by SQL Server. As the name implies, a snapshot is created at a certain point in time when the database is running. Third-party software and hardware can provide such tools, but the cost is high. The advantage of using snapshots is that you can back up data at any time.
8. Local hard disk backup Vs. Network Backup
Network Backup may put pressure on network I/O devices. Like hard disk I/O devices, using a network to transmit a large amount of data can also cause some problems. When network backup is considered, the backup creation time varies depending on different situations. The best way is to back up the hard disk to the local server. After the backup is complete, copy it to the tape for archiving.
9. Use Continuous Data Protection (CDP)
A new data backup method is continuous data protection (CDP ). This method backs up the transactions that occur. You can recreate the. mdf and. ldf files on another server for fault tolerance, reporting, and other services you need. This avoids full backup on the master server. TimeSpring Software provides this service.
10. Run differential backup
This option enables you to perform partial backup only on the basis of the last full backup. Differential backup only includes the parts that have changed since the last full backup. Full backup runs once a week, and differential backup runs more frequently. Differential backup is faster, but it takes a long time to complete the backup. Different backups may be the same size as full backups.
Summary
As you can see, there are so many ways to perform quick backup. I always think that you should first back up the disk and then copy it to the tape for archiving. According to this method, the introduction of third-party backup compression software is the simplest method, but the cost is quite high. Decide which method is best for you based on your own situation.