With the above Foundation, we have added database backup elements to generate database backup files through maintenance plans. These include two types of files: full backup and differential backup of the database, with these two files, we can use
SQL Server
Backup and restoration(Graphic operation) or
SQL Server
Minimum database downtime migration solution(Code operation) method to restore our data;
II,
Scenario Design
Assume that our database is not very large, but our data is important. One backup every day can meet our business needs. How should we design our backup solution?
Perform a full database backup at on Sunday, and perform a differential backup at from Monday to Saturday at other times. The basic logic is shown in the following figure:
(Figure: database backup file generation logic)
Tips:
1. if your database is large and has a large amount of stream data (that is, some recorded stream data, our practice is to separate these large tables into a new database (vertical cutting database), so that the following problems can be solved in this article;
2. if the stream data cannot be separated from the database, you can consider Table partitions and then back up the data of other tables (primary partition backup) after Partitioning. These backups cannot use differential backup, only full backup can be used each time, and Table partitions cannot be used during restoration. You can only change the table name and create a new table. For details, refer:SQL Server maintenance plan backup primary partition
3. if one-day backup does not meet business requirements, you can consider the publish and subscribe solution to perform Transaction replication on two machines, which can adjust the replication frequency and enhance data security, this will be explained in future articles;
III,
Practical operations
1. Database-> [management]-> [maintenance plan]-> [new maintenance plan ];
(Figure 1)
2. Select [backup database (complete)] and [backup database (difference )];
(Figure 2)
3. Set
Full BackupFile path and backup file extension; pay attention to the selection of specific databases;
(Figure 3)
4. Enter
Full BackupThe name of the job plan. Select Sunday for execution interval and set the execution time;
(Figure 4)
5. Set
Differential backupFile path and backup file extension; pay attention to the selection of specific databases;
(Figure 5)
6. Enter
Differential backupName of the job plan, select a date other than Sunday for execution interval, and set the execution time;
(Figure 6)
7. This generates
Full Backup and differential backupThe sub-plan,
(Figure 7)
8. In the complete backup design interface, drag a "Clear History" control on the left to the right, and double-click to set the following figure;
(Figure 8)
9. Drag the "clear maintenance" control on the left to the right, and double-click the Settings shown in the following figure.
Full BackupPath and file extension;
(Figure 9)
10. Add arrows to the step
Full BackupJob flowchart;
(Figure 10)
11. In the differential backup design interface, drag a "Clear History" control on the left to the right, and double-click to set the following figure;
(Figure 11)
12. Drag the "clear maintenance" control on the left to the right, and double-click the Settings shown in the following figure.
Differential backupPath and file extension;
(Figure 12)
13. Add arrows to the step
Differential backupJob flowchart;
(Figure 13)
14. Generate Ant_Backup in the maintenance plan and generate it in the agent
Full Backup and differential backup;
