Copy Code code as follows:
Database Backup Instance/**
* * Database Backup instance
* * Zhu Yi May 2004
* * Backup strategy:
* * Database name: Test
* * The path to the backup file E:\backup
* * Make a full backup every Sunday 1 o'clock in the morning and, for insurance purposes, back up to two identical full backup files Test_full_a.bak and Test_full_b.bak
* * 1 points per day (except Sunday) to do a differential backup, backed up to two files Test_df_a.bak and Test_df_b.bak (with the way attached to the original backup)
* * Do a transaction log backup every one hours, backed up to two files Test_log_a.bak and Test_log_b.bak (in the same way that you attach to the original backup)
**/
--The first full backup part
--New jobs
EXEC sp_add_job @job_name = ' Full backup '
--New job step 1, backup database to Test_full_backup_a.bak
EXEC sp_add_jobstep @job_name = ' full backup ',
@step_name = ' SETP1 ',
@subsystem = ' TSQL ',
@command = ' BACKUP DATABASE Test to disk= ' E:\backup\test_full_A.bak ' with INIT ',
@on_fail_action = 3,--go to next step after failure
@on_success_action =3,--succeed and go to the next step
@retry_attempts = 5,
@retry_interval = 5
--New job step 2, backup database to Test_full_b.bak
EXEC sp_add_jobstep @job_name = ' full backup ',
@step_name = ' SETP2 ',
@subsystem = ' TSQL ',
@command = ' BACKUP DATABASE Test to disk= ' E:\backup\test_full_B.bak ' with INIT ',
@retry_attempts = 5,
@retry_interval = 5
--Dispatch
EXEC sp_add_jobschedule @job_name = ' full backup ',
@name = ' Schedule_1 ',
@freq_type = 8,--by week
@freq_interval = 1,--Every Sunday
@freq_recurrence_factor = 1,
@active_start_time = 10000--Start time: 1 o'clock in the morning
--Second difference backup part
--New jobs
EXEC sp_add_job @job_name = ' differential backup '
--New job step 1, backup database to Test_df_a.bak
EXEC sp_add_jobstep @job_name = ' differential backup ',
@step_name = ' SETP1 ',
@subsystem = ' TSQL ',
@command = ' BACKUP DATABASE Test to disk= ' E:\backup\test_df_A.bak ' with differential ',
@on_fail_action = 3,--go to next step after failure
@on_success_action =3,--succeed and go to the next step
@retry_attempts = 5,
@retry_interval = 5
--New job step 2, backup database to Test_df_b.bak
EXEC sp_add_jobstep @job_name = ' differential backup ',
@step_name = ' SETP2 ',
@subsystem = ' TSQL ',
@command = ' BACKUP DATABASE Test to disk= ' E:\backup\test_df_B.bak ' with differential ',
@retry_attempts = 5,
@retry_interval = 5
--Dispatch
EXEC sp_add_jobschedule @job_name = ' differential backup ',
@name = ' Schedule_1 ',
@freq_type = 8,--by week
@freq_interval = 126--Monday to Saturday
@freq_recurrence_factor = 1,
@active_start_time = 10000--Start time: 1 o'clock in the morning
--Second Transaction backup section
--New jobs
EXEC sp_add_job @job_name = ' Transaction backup '
--New job step 1, backup database to Test_log_a.bak
EXEC sp_add_jobstep @job_name = ' Transaction backup ',
@step_name = ' SETP1 ',
@subsystem = ' TSQL ',
@command = ' BACKUP LOG Test to disk= ' E:\backup\test_log_A.bak ',
@on_fail_action = 3,--go to next step after failure
@on_success_action =3,--succeed and go to the next step
@retry_attempts = 5,
@retry_interval = 5
--New job step 2, backup database to Test_log_b.bak
EXEC sp_add_jobstep @job_name = ' Transaction backup ',
@step_name = ' SETP2 ',
@subsystem = ' TSQL ',
@command = ' BACKUP LOG Test to disk= ' E:\backup\test_log_B.bak ',
@retry_attempts = 5,
@retry_interval = 5
--Dispatch
EXEC sp_add_jobschedule @job_name = ' Transaction backup ',
@name = ' Schedule_1 ',
@freq_type = 4,--by day
@freq_interval =1,--every day
@freq_subday_type = 8--by the hour
@freq_subday_interval = 1--Every one hours
