SQL Server Maintenance plan to implement database backup

First, the preface

SQL Server Backup and restore strategy, which includes a graphical guide to restoring various backup files through the SSMs operation, a minimal downtime migration solution for SQL Server databases, which uses SQL scripts (t) to complete full backups, differential backups, full restores, differential restores, and so on;

With the above foundation, we have joined the database backup elements, through the maintenance plan to generate the database backup files, this includes two kinds of files, database full backup and differential backup, with these two files, we can through SQL Server backup and restore full Raiders (graphics operations) or SQL Server database minimal Downtime migration scheme (code operation) method to restore our data;

Second, scene design

Suppose our database is not very large, but our data will be more important, about one day a backup can meet our business needs, then how should we design our backup plan?

Sunday Night 02:00 make a full backup of the database, the other time from Monday to Saturday night 02:00 to do a differential backup, the basic logic as shown in the following figure:

(Figure: Database backup file generation logic)

Tips:

1. If your database is large, and the flow of data (that is, some of the recorded water data) More, our approach is to separate these large tables a new database (vertical cutting database), so the next question, this article can be resolved;

2. If the streaming data cannot be separated from the database, you can consider table partitioning, partitioning, and then data backups of other tables (primary partition backups), which cannot be backed up with differential backups, only full backups at a time, and table partitions are not available for restore, and new tables can be created only after the table name has been modified. Please refer to: SQL Server maintenance plan Backup Primary partition

3. If a single day of backup can not meet the needs of the business, then you may consider publishing subscription scheme, in two machines for transactional replication, you can adjust the frequency of replication, enhance the security of data, this will be explained in the future article;

Third, the actual operation

1. Database-> "Management"-> "maintenance Plan"-> "new maintenance plan";

(Figure 1)

2. Check "BACKUP Database (complete)" and "Backup Database (difference)";

(Figure 2)

3. Set the path of the full backup file and the extension of the backup file, and pay attention to the selection of the specific database;

(Figure 3)

4. Fill in the name of the full backup job plan, the execution interval check Sunday, set execution time;

(Figure 4)

5. Set the path of the differential backup file and the extension of the backup file; Note the selection of the specific database;

(Figure 5)

6. Fill in the name of the differential backup job schedule, tick the date except Sunday, and set the execution time;

(Figure 6)

7. This generates a child plan for full backups and differential backups,

(Figure 7)

8. In the full backup design interface, drag a "clear history" control from the left to the right, and double-click to set the following figure;

(Figure 8)

9. Drag a "Clean maintenance" control from the left to the right, and double-click to set the following figure, where you need to set the full backup path and file extension;

(Figure 9)

10. Add arrows on the steps to form a flowchart of the full backup job;

(Figure 10)

11. In the design interface of the differential backup, drag a "purge history" control from the left to the right, and double-click to set the following figure;

(Figure 11)

12. Drag a "Clean maintenance" control from the left to the right, and double-click to set the following figure, where you need to set the path for the differential backup and the file extension;

(Figure 12)

13. Add arrows on the steps to form a flowchart of the differential backup job;

(Figure 13)

14. Generate Ant_backup under maintenance plan, generate full backup and differential backup in the agent;

(Figure 14)