SQL server implements database backup maintenance plan

SQL Server backup and restoration raiders, which includes through SSMS operation to restore a variety of backup files graphics guide, SQL Server database minimum downtime migration solution, which uses SQL script (T-SQL) complete full backup, differential backup, full recovery, and differential recovery;

SQL Server backup and restoration raiders, which includes through SSMS operation to restore a variety of backup files graphics guide, SQL Server database minimum downtime migration solution, which uses SQL script (T-SQL) complete full backup, differential backup, full recovery, and differential recovery;

With the above foundation, we have added backup elements to generate backup files for the database through the maintenance plan. These include two types of files: Full backup and differential backup for the database, with these two files, we can useSQL ServerBackup and Restoration(Graphic operation) orSQL ServerMinimum 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:

(Figure: database backup file generation logic)

Ti:

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 to: SQL Server maintenance plan to back up the 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 ];

()

2. Select [backup database (complete)] and [backup database (difference )];

()

3. SetFull backupFile Path and backup file extension; pay attention to the selection of specific databases;

()

4. EnterFull backupThe name of the job plan. Select Sunday for execution interval and set the execution time;

()

5. SetDifferential backupFile Path and backup file extension; pay attention to the selection of specific databases;

()

6. EnterDifferential backupName of the job plan, select a date other than Sunday for execution interval, and set the execution time;

()

7. This generatesFull backup and differential backupThe sub-plan,

()

8. On the complete backup design page, drag the "Clear history" control on the left to the right and double-click the settings;

(Figure 8)

9. Drag a [clear maintenance] control from the left to the right, and double-click the settings. You need to setFull backupPath and file extension;

(Figure 9)

10. Add arrows to the stepFull backupJob flowchart;

(0)

11. On the differential backup design page, drag the "Clear history" control on the left to the right and double-click the settings;

(1)

12. Drag a [clear maintenance] control from the left to the right, and double-click the settings. You need to setDifferential backupPath and file extension;

(2)

13. Add arrows to the stepDifferential backupJob flowchart;

(3)

14. Generate Ant_Backup in the maintenance plan and generate it in the agentFull backup and differential backup;