SQL Server maintenance plan for database backup (step by Step) (EXT)

SQL Server maintenance plan for database backup (step by Step)

First, Preface

SQL Server Backup and restore all -in-a-kind, including the use of SSMS operation to restore a variety of backup files graphical guidance,SQL Server Database Minimum outage migration scenario , which uses SQL script (T) to complete a full backup, Differential backup, full restore, differential restore, etc.;

with the above foundation, we joined the database backup elements, through the maintenance plan to generate the database backup files, which includes two kinds of files, the database full backup and differential backup, with these two files, we can through the SQL Server Backup and restore all -in-A-way (graphics operations) or SQL Server Database Minimum outage migration scheme (Code Operations) 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 solution?

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

(Figure: Database backup file generation logic)

Tips:

1. If your database is larger, and streaming data (that is, some of the recorded data) is more, we would like to separate these large tables out of a new database (vertical cutting database), so the next question, this article can be solved;

2. If the stream data cannot be separated from the database, then the table partition can be considered, partition and then the other tables for data backup (primary partition Backup), these backups are unable to use differential backup, each time can only use a full backup, and restore the table partition is not available, can only modify the table name and then create a new table. Please refer to: SQL Server maintenance plan Backup Primary partition

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

Third, actual Operation

1. "Maintenance plan", "Management", "Database", "New maintenance plan";

(Fig. 1)

2. Tick "BACKUP Database (Full)" and "Backup Database (diff)";

(Fig. 2)

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

(Fig. 3)

4. Fill in the name of the full backup job schedule, tick Sunday, set the execution time;

(Fig. 4)

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

(Fig. 5)

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

(Fig. 6)

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

(Fig. 7)

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

(Fig. 8)

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

(Fig. 9)

10. Add arrows to the step to form a flowchart for a full backup job;

(Fig. 10)

11. In the differential backup design interface, drag a "clear history" control from the left to the right, and double-click on the settings;

(Fig. 11)

12. Drag a "clear maintenance" control from the left to the right, and double-click on the settings, where you need to set the path of the differential backup and the extension of the file;

(Fig. 12)

13. Add arrows on the step to form a flowchart for the differential backup job;

(Fig. 13)

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

(Fig. 14)

Four, Reference Documents

SQL Server 2005 Automatic backup

SQL Server 2005 automatic Backup Maintenance plan

SQL Server 2005 Backup maintenance plan

Use SQL Server maintenance plan to implement scheduled automatic backups of the database

SQL Server maintenance plan for database backup (step by Step) (EXT)