SQL Server Data Backup Process

SQL Server Data Backup Process

Microsoft SQL Server provides a database administrator program that allows you to develop data backup and repair plans based on your business and technical needs ......

Compared with the Personal Edition, one of the main advantages enterprise-level databases can provide is a powerful combination of backup and repair functions. Microsoft SQL Server provides a database administrator program that allows you to develop data backup and repair plans based on your business and technical needs.

Next we will discuss the data backup process of Microsoft SQL Server. When creating a Backup plan, you may need to create a suitable Backup set with different Backup Scopes and Backup Types ), it can meet enterprises' recovery goals and apply to your technical environment.

Backup range

The backup scope defines the partitions covered by database backup, and determines the database, files, and/or file groups to be backed up by SQL Server. There are three different backup ranges available for Microsoft SQL Server:

· Database backup: covers the entire database, including all the architecture information, all the data content in the database, and the transaction logs required to restore the database from the beginning to the backup status. When a disaster occurs, the simplest way to restore data is to back up the database, but it takes a lot of hard disk space and time to complete the entire process.

· Partial backup: When you need to back up a large database that contains a large amount of read-only data, it is best to select partial backup. When your database has a read-only file group, frequent backup is meaningless, because the existing data will not change. Therefore, the scope of some backups includes all the files in the master file group, all the read/write file groups, and any read-only file groups you have selected.

· File backup: allows you to back up some files and/or certain file groups separately from the database. When you create a one-time backup for a read-only file group, you can use file backup as a supplement to local backup. It can also be used as an integral part of the complex backup model.

Backup Type

When creating an SQL Server database backup plan, the second thing to be determined is the type of each backup in the plan. The backup type is used to describe the time range for database backup. SQL Server supports two different backup types:

· Full backup: includes all data within the backup range. Full database backup contains all the data in the database, no matter when the data is created or modified. Similarly, a full backup will include all files and file groups within the backup range.

· Differential backup: contains only the data that has been changed since the last full backup. For example, you performed a full backup on the database on Monday morning and a differential backup on the evening of that day. Therefore, the differential backup file will be very small (and only a small amount of creation time is required), because it only contains the changed data in the day.

Remember, when creating a backup plan, the backup range and backup type are mutually independent. As mentioned above, each Backup Type and each backup range allow users to select the data volume for backup independently, therefore, it indirectly determines how long it takes for a disaster to complete database backup and restoration. So far, all the tables under the db_zgxt user have been successfully converted from tablespace A to tablespace B.