10 methods of SQL Server Quick Backup _mssql

SQL Server Backup is a system engineering that is time-consuming. Because the database continues to grow during the run, the corresponding backup also takes more time. The 100G database is usually considered a very large database, now 100G is already very common, and now many databases have reached TB level. In this article, we will discuss how to make a quick backup of SQL Server in 10 ways.
1, the hard disk to back up the tape to archive
Backing up to a hard disk is much faster than backing up to tape, and most experienced DBAs are biased towards this approach. In addition to pursuing high speed I/O rates, you also need to have the latest backup to do the data recovery at hand. When you have finished your hard drive backup, you need to archive the data on tape for long-term storage.
2, the use of spare time for backup
It is best to take advantage of spare time to do backups because the database server has the fewest operations and the less performance impact. But keep in mind that sometimes running bulk jobs in your spare time may be more stressful than working on your system. It is therefore important to monitor server status and to carefully develop a time period for full backups.
3, the use of compression software
The best way to backup SQL Server is to have a hard disk backup and then tape archive. The disadvantage is that the backup file is usually the same size as the data file. Also because of this, if you have a 100G database, you will need 100G of hard disk space to backup. Unfortunately, SQL Server does not have an embedded compression tool. You can use compressed products, but this will take more time. Fortunately, there are three kinds of compression tools on the market, Idera, Quest Software Inc. and Red Gate Software LTD can help you create a compressed backup in your busy schedule. Using compression software increases the cost of backup, but the benefits you get are far greater than this cost.
4. Write Multiple Files
Another way is to write backups to multiple files, so you can use multithreading for backup. The tape manufacturer and the three companies mentioned above provide this service. Multitasking allows for faster backups, which do not compress backup files, but can significantly reduce the amount of time spent.
5. Write multiple Physical disk drives
Full backups are frequently performed on I/O devices. Each database file is read and then written to another file. With a multiple-physical hard drive, you can achieve high I/O rates and complete backups faster. In addition to writing multiple file methods, you can write multiple physical hard drives to handle I/O performance bottlenecks.
6. Run File or filegroup backup
SQL Server provides another backup option-file or filegroup backup. This method is determined by the database initial settings. If you create multiple files or multiple filegroups when you set up your database, you can back up only a subset of the databases without having to back up the entire database. This approach can increase work complexity and security risks, so make sure you plan your plans before using this method for backup.
7. Create Snapshots
Snapshots are another method of backup provided by SQL Server. As the name suggests, a snapshot is created at a point in time when the database is running. Third-party hardware and software can provide such tools but at a high cost. The advantage of using snapshots is that you can make backups at any time.
8, local hard disk backup vs. network backup
A network backup can cause a certain amount of pressure on network I/O devices. Like hard disk I/O devices, the use of a network for a large number of data transmission can also cause problems. When you consider a network backup, the time that you use to create the backup varies greatly depending on the situation. The best way to do this is to back up the hard drive that connects to the local server. The backup is completed and then copied to the tape for archiving.
9. Using Continuous data protection (CDP)
A new method of data backup is continuous data protection (CDP). This method can back up the transactions that occurred, and you can rebuild the. mdf and. ldf files on another server for fault tolerance, reporting, and any services you need. This avoids the case of a full backup on the primary server. Timespring software company provides this service.
10. Run Differential backup
This option allows you to make a partial backup only on the basis of the last full backup. A differential backup includes only those parts that have changed since the last full backup. A full backup runs once a week, and differential backups run more frequently. Differential backups are faster, but still take a long time to run a full backup. Depending on the change section, sometimes the differential backup may be the same size as the full backup.
Summary
As you can see, there are so many ways to make a quick backup. I always think you should back up to your hard disk before you copy it to 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. Depending on your situation, decide which method is best for you.