Database backups are an important means to restore important data in time to prevent data loss in the case of data loss. A reasonable database backup solution should be able to efficiently recover important data when data is lost, while considering technology implementation difficulty and efficient use of resources.
1.1. Preparation
Before developing a data backup program, we first make a specific investigation to the reality, so that the plan to formulate is reasonable and effective. General, we need to know the following:
l Allowance to data loss?
l Allowable fault processing time?
l The frequency of business processing?
l Workload of the server?
l Acceptable backup / recovery processing technology difficulty?
l The size of the database?
l The growth rate of database size?
l The data change in those tables is frequent, and the data in those tables is relatively fixed?
l The data in those tables is very important. Is not allowed to be lost, those in those tables are allowed to lose part?
l When you use a large number of databases, causing frequent insertion and update operations?
l What are the existing database backup resources (disks, tapes, discs)?
l Is it possible to put new devices or funds for database backup?
1.2. Backup method
After understanding the status quo, let's take a look at the backup method provided by SQL Server, and SQL Server has improved the way four database backups:
1.2.1. Complete backup
This backup will back up all the data in the database. Therefore, its generated backup file size and backup time are determined by the capacity of data by the database. When restoring, you can restore the status of the backup file directly to the backup, and you don't need other files, the restore process is the simplest;
1.2.2. Difference backup
Backup After the last complete backup, changes have occurred. Differential backups are data that have changed the backup, so there must be at least one complete backup before doing differential backups. When it is restored, it must also be restored to a complete backup of the previously backed up, in order, it can be restored on this basis. This backup file size and backup need for backup generation, depending on the data change in the database, relative to the full backup, the backup time is short, the backup time is short, the backup time is short, the backup time is short, the backup time is short, SQL Server service performance is also small; but its restore process is relatively troublesome, you must have a complete backup before it successfully restore;
1.2.3. Log Backup
Backup is a series of records for all transactions executed by the database after the last backup. This last backup can be full backup, difference backup, log backup, but at least once before log backup. When restoring, you must restore a full backup, restore differential backup (if any), follow the order of log backups, restore the contents of each log backup; this backup file is the smallest, required time The shortest, the impact on the performance of SQL Server is also minimally, is suitable for frequent backups. But obviously, its restore process is the most troublesome, not only to correspond to its full backup and difference backup (if any), pay attention to the order of restoration;
1.2.4. Files and File Group Bound
You can back up and restore individual files or file groups in the database. This method of backup methods is usually relatively small, often used to back up important data. It requires the design of the database to group the table that needs to be individually made separately, assigns different file groups (tables can only be placed on the file group, one file, one file The group can be a file or multiple files), so you can back up this data separately when you make a backup. This backup process technology is relatively high, not only to grasp the backup / restore method, but also must master the data in the database structure and the data in the database. 1.3. Backup program development strategy
With the above information, we can choose the right backup method according to our actual situation. In general, we can follow the following strategies when developing a backup solution:
l The database backup ensures that important data can be restored in the case of data loss, so after the data in the database changes, it is necessary to back up important data in time.
l Data backup, requires the normal operation of the business processing, so data backups are used in multiple backup methods, and the full backup of this kind of service resource is set in the free time period of the service processing, and back up the log backup The class occupies less service resources apply the peak of business processing, but it needs to be backed up in time;
l Take a full consideration of the failure, the business process acceptable shutdown, different backup methods, different restoration times, and therefore, while careling the backup to the business processing, consider the time of restore, can not be completely backed up The processing has a big impact, it will be done once a few months. In this case, it takes a long time when it is restored;
l Consider the company's technical force, try to avoid the backup processing method of using the technical procedures exceeding the company's master;
l Use the backup resource to use the backup resource to be backed up according to the current backup resource, and to consider the removal of the clearing of the expiration of the backup file and the reuse of the resource;
l It is necessary to consider the impact of catastrophic data loss. For important data, back up databases to multiple media and multiple places, such a backup is corrupted, and there are other backups available.
1.4. Implementation of the backup program
The database backup is a periodic job, so we should automatically complete a variety of backups in accordance with the backup solution we have developed, instead of our daily backup processing.
In SQL Server, you have to perform an action, which is done by the SQL Agent service, so let's make our backup solution automatically complete, first we have to set the SQL Agent service to automatically start (need to pay attention to it. WIN98 / WINME This type of operating system is unable to set SQLServerAgent automatically started), setting method:
My computer à control panel à management tool à service à mouse button SQLServerAgent
à Attribute à Start Type à Select "Auto Start" à determined
In SQL Server, we can define our database backups through the following methods:
1. Database Maintenance Plan: Here you can define the automatic backup job of full backup and log backup. Definition method:
Enterprise Manager à Administration à Right-click Database Maintenance Program à New Maintenance Plan
à [Next] à Choose the database you want to back up
à [Next] à until the "Specify Database Backup Plan"
à then follow the wizard prompts to set up
2. Database Backup: Here you can develop a variety of database backup jobs in our backup mode. Definition method: Enterprise Manager à mouse button you want to back up Database à All Tasks à Backup Database
à Select the database and backup mode in [Backup] item
à In [Purpose] à [Backup to] item, press the [Add] button to add a backup file
à In [Scheduling] item, check "Scheduling", point ... Button Settings Time Arrangement
à determined
3. Job: The above two methods, the result of the final setting is a job (JOB) scheduling, so we can also create automatic backup by calling the job by directly creating a job. Definition method:
Enterprise Manager àsql Server Agent à Right-click Job à New Job
à [General] Enter the job name
à [step] à à New à input step name
à [Type] Select "Transact-SQL Script (TSQL)"
à [Command] Enter the backup processing SQL statement
à [Dispatch] item, set your backup time plan
Specific SQL backup statements, refer to the relevant part of the SQL online series, which is not described here.
