SQL Server maintenance plan for Database Backup

I. Preface SQL Server backup and restoration strategy, which includes through SSMS operation to restore a variety of backup file 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 the backup elements of the database.

I. Overview of SQL Server backup and restoration, which includes graphic guidance for restoring various backup files through SSMS, and the minimal downtime migration solution for SQL Server databases, the use of SQL script (T-SQL) to complete the full backup, differential backup, full recovery, differential recovery, etc.; with the above foundation, we joined the database backup elements, through the dimension

I. Preface

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 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 full strategy (Graphic operations) or SQL Server database minimal downtime migration solution (Code operations) 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)

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 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. Set the path of the complete backup file and the extension of the backup file; pay attention to the selection of specific databases;

()

4. enter the name of the complete backup job plan, select Sunday for execution interval, and set the execution time;

()

5. Set the path of the differential backup file and the extension of the backup file; pay attention to the selection of specific databases;

()

6. enter the name of the differential backup job plan, select the date except Sunday for the execution interval, and set the execution time;

()

7. This generates a sub-plan for the complete backup and differential backup,

()

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 the "Clear maintenance" control on the left to the right, and double-click the settings. here you need to set the path and file extension of the complete backup;

(Figure 9)

10. Add an arrow in the step to form a complete backup job 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 the "Clear maintenance" control on the left to the right, and double-click the settings. here you need to set the path and file extension of differential backup;

(2)

13. Add the arrow in the step to form the flowchart of the differential backup job;

(3)

14. Generate Ant_Backup under the Maintenance Plan, and generate full backup and differential backup jobs in the proxy;

(4)