Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve fast backup in SQLServer, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Hard disk backup tape archive backup to hard disk is much faster than backup to tape, and most experienced DBA prefer this method. In addition to pursuing a high-speed Imax O rate, you also need to have up-to-date backups on hand for data recovery. After making a hard disk backup, you need to archive the data on tape for long-term preservation.

2. When you make a backup in your spare time, you'd better make use of your spare time, because the least operations on the database server will have less impact on performance. Keep in mind, however, that sometimes running batch work in your spare time can put more pressure on the system than what you normally do. Therefore, it is very important to monitor the status of the server, and the time period for full backup should be carefully set.

3. The best way to use the compression software SQL Server to back up is to back up the hard disk and then archive it on tape. The disadvantage is that backup files are usually about the same size as data files. Also because of this, if you have a 100G database, you need 100G of hard disk space for backup. Unfortunately, SQL Server doesn't come with a built-in compression tool. You can use compression products, but it will take more time. Fortunately, there are three compression tools on the market, Idera and Quest Software Inc. And Red Gate Software Ltd. All of the products can help you create a compressed backup in the middle of your busy schedule. Using compression software will increase the backup cost, but the benefits you get far outweigh the cost.

4. Another way to write multiple files is to write backups to multiple files, so you can use multiple threads for backup. Tape manufacturers and the three companies mentioned above provide this service. Multitasking allows faster backups, which does not compress backup files, but greatly reduces the time spent.

5. Writing to multiple physical disk drives for full backup is very frequent for Istroke O devices. Each database file is read and then written to another file. With multiple physical hard drives, you can achieve a high I-O rate and complete backups faster. In addition to the write multi-file method, you can also write to multiple physical hard drives to deal with Icano performance bottlenecks.

6. Run file or filegroup backup SQL Server provides another backup option-file or filegroup backup. This method is determined by the initial setup of the database. If you created multiple files or filegroups when you set up the database, you can back up only part of the database without backing up the whole database. This approach may increase work complexity and security risks, so be sure to make a plan before using this method for backups.

7. Creating a snapshot is another backup method provided by SQL Server. As the name implies, a snapshot is created at some point in time when the database is running. Third-party hardware and software can provide such tools, but the cost is very high. The advantage of using snapshots is that you can back up at any time.

8. Local hard disk backup Vs. Network backup for network backup will cause some pressure on the network I / O devices. Like the hard disk I / O device, using the network to transfer a large amount of data will also cause some problems. When considering a network backup, the time it takes to create a backup varies greatly from situation to situation. The best way is to back up to the hard drive connected to the local server. After the backup is complete, it is copied to tape for archiving.

9. Using continuous data protection (CDP) A new data backup method is continuous data protection (CDP). This method can back up the transactions that occurred, and you can rebuild .MDF and .ldf files on another server for fault tolerance, reporting, and any other services you need. This avoids the need for a full backup on the primary server. TimeSpring Software provides this service.

10. Run the differential backup option so that you can only make a partial backup based on the last full backup. Differential backups include only those parts that have changed since the last full backup. Full backups can be run once a week, while differential backups run more frequently. Differential backups are faster, but it still takes a long time to run a full backup. Depending on the changes, sometimes the differential backup may be the same size as the full backup.

On how to achieve fast backup in SQLServer to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.