Database backup Scenario __ Database

Database backup is an important means to recover important data and prevent data loss in time of data loss. A reasonable database backup scheme should be able to recover important data effectively when data is lost, and it is necessary to consider technology implementation difficulty and utilize resources effectively. 1.1. Preparatory work before formulating a data backup scenario, we first make a specific survey of the actual situation, so that the plan developed is reasonable and effective. Generally, we need to understand the following: L          How much data loss is allowed. L          allowable fault handling time. L          the frequency of business processing. L          workload of the server. L          Acceptable backup/restore processing technology difficulty. L          size of database. L          growth of database size? L          The data in those tables are frequently changed, and the data in those tables are relatively fixed. L          The data in those tables are very important and are not allowed to be lost, the data in those tables is allowed to be lost part of it? L          When a large number of database use, resulting in frequent inserts and updates operations. L          Existing database backup resources (disk, tape, CD). L          It is possible to put new equipment or funds into database backup. 1.2. Backup Method once we get to the status quo, and then look at the backup methods that SQL Server provides us, SQL Server gives us a total of four ways to backup the database: 1.2.1. Full backup This backup backs up all the data in the database. Therefore, the size of the backup file it generates and the time it is backed up are determined by the capacity of the data in the database. Restore, you can restore directly from the backup file to the state of the backup, do not require additional file support, the most simple restore process; 1.2.2. Differential backup The data that has changed since the last full backup has been backed up. A differential backup is the data in which the backup has changed, so you must have at least one full backup before making a differential backup. When restoring, you must first restore a full backup of the differential backup before you can restore the differential backup data on this basis. The size of the backup file generated by this backup and the time required for the backup, depending on the data change of the database since the last full backup, it generates a smaller backup file than a full backup, a shorter backup time, and a smaller impact on the performance of SQL Server services, but its restore process is relatively cumbersome, It must correspond to a full backup before it can be restored successfully; 1.2.3. Log Backups a backup is a series of records of all transactions performed against the database since the last backup, which can be a full, differential, or log backup, with at least one full backup before the log backup. When you restore, you must first restore a full backup, restore the differential backups (if any), and then restore the contents of each log backup in the order in which they were backed up, which is the smallest backup file, takes the shortest time, has minimal impact on SQL Server service performance, and is appropriate for frequent backups. But obviously, its restore process is the most troublesome, not only to its previous full backup and differential backup (if any), but also pay attention to the Order of restore; 1.2.4. File and Filegroup Backups you can back up and restore individual files or filegroups in a database. This kind of backup method is usually used less frequently and is often used to back up important data. It requires that in the design of the database, consider the table that needs to do a special backup separately, assign them a different filegroup (a table can only be placed on a filegroup, not on a specific file, a filegroup can be a file or multiple files), so that you can back up the data separately when you make a backup. This kind of backup processing technology is relatively high difficulty, not only to master the backup/restore method, but also the database structure and the database of the table data in a better grasp. 1.3. Strategy development for backup scenarios with the above information, we can choose the appropriate backup method according to our actual situation. In general, when we make a backup plan, we can follow the following strategy: L database backup can ensure that in the case of data loss, can restore important data, therefore, the data in the database changes, to timely backup of important data. L data Backup, requirements do not affect the normal operation of business processing, as a result, data backups take a variety of backup methods and are set up in the idle time segment of the business process by using a full backup of such a service-resource-intensive backup, while the backup method of log backups, which consumes less service resources, is applied at the peak of the business process, But it needs to be backed up in time; L fully consider when the failure occurs, business processing can accept downtime, different backup methods, the need for different restore time, therefore, while caring for the impact of backup on business processing, to consider the time to restore, not because full backup on business processing impact, only a few months to do, In this case, the time spent on the restoration is very long; L consider the technical force of the company, try to avoid using the backup processing method of the technical program that exceeds the company's master; l Use the backup resources, according to the company's current backup resources, reasonable use of the above four backup methods for backup, Also consider the cleanup of expired backup files and the reuse of backup resources; L Consider the impact of catastrophic data loss. For important data, the database is backed up to a variety of media and multiple places, such that a backup is corrupted and other backups are available. 1.4. Implementation of backup Scenarios Database backup is a cyclical task, so we should have SQL Server automate all kinds of backups in accordance with our backup plan, rather than doing our daily backup processing manually. In SQL Server, it is done by the SQL Agent service to perform an operation at timed intervals, so to automate our backup solution, we first set the SQL Agent service to start automatically (note that win98/ WinME this kind of operating system is unable to set the SQLServerAgent automatic startup), set the method: My Computer à control Panel à management tools à services à right mouse button sqlserveragentà properties à start type à select autostart to determine in SQL Server , there are several ways we can define our database backup: 1.Database Maintenance Plan: Here you can define the automatic backup job for full and log backups. Definition method: Enterprise Manager à admin à right key database maintenance plan à new maintenance plan à[next]à Select the database you want to back up à[next]à until the "Specify database backup plan" and follow the wizard prompts to set the 2.Database Backup: Here you can set up a variety of database backup jobs based on our backup methods. Definition method: Enterprise Manager à right mouse button you want to back up the database à all tasks à back up the database à select a database and backup in a [backup] item à in the [destination]à[backup to] item, press the Add button to add a backup file à in the [dispatch] item, tick "Schedule", and click ... button to set the time schedule for the backup à determine 3.Job: These two methods, the result of the final setup is a job (job) scheduling, so we can also create a job directly by the job scheduled to call the backup processing statements to implement automatic backup. Definition method: Enterprise Manager àsql Server Agent à right key job à new job à[general] entry in Job name à[step] item à new à input step name à[type SELECT SQL statement in Transact-SQL script (TSQL) à[command to enter backup processing à[ Schedule item, set up your backup time plan specific SQL backup statements, refer to the relevant sections on the SQL online books, not described here.