SQL Server backup

-- full backup
backup database news to disk = 'C: \ news Bak' with name = 'news backup all ', description = 'full backup of new'
-- Differential backup
backup database aspx1_oom to disk = 'C: \ aspx1_oom. bak 'with differential, noinit, name = 'aspx1_oom backup DIF', description = 'differential backup of aspx1_ange'

Select *
From MSDB. DBO. sysjobs
Where (name = n'aspx1_image backup test ')
-- Script generated on 2005-10-11/15: 19
-- By: TSW \ TSW
-- Server: (local)
Create procedure databasebackup
As
Begin transaction
Declare @ jobid binary (16)
Declare @ errnum int
Declare @ returncode int
Set @ errnum = 0
Select @ returncode = 0
If (select count (*) from MSDB. DBO. syscategories where name = n' [uncategorized (local)] ') <1
Execute MSDB. DBO. sp_add_category @ name = n' [uncategorized (local)]'

-- Delete an alert with the same name (if any ).
Select @ jobid = job_id
From MSDB. DBO. sysjobs
Where (name = n'aspx1_image backup test ')
If (@ jobid is not null)
Begin
-- Check whether this job is a multi-server job
If (exists (select *
From MSDB. DBO. sysjobservers
Where (job_id = @ jobid) and (server_id <> 0 )))
Begin
-- The script is terminated because it already exists.
Raiserror (n' unable to import the job "aspx1_oom backup test" because multiple server jobs with the same name already exist. ', 16, 1)
Goto quitwithrollback
End
Else
-- Delete a [local] job
Execute MSDB. DBO. sp_delete_job @ job_name = n 'aspx1_oom backup Test'
Select @ jobid = NULL
End

Begin

-- Add a job
Execute @ returncode = MSDB. DBO. sp_add_job @ job_id = @ jobid output, @ job_name = n'aspx1_image backup test', @ owner_login_name = n' TSW \ tsw', @ description = n' no available description. ', @ Category_name = n' [uncategorized (local)]', @ enabled = 1, @ yy_level_email = 0, @ yy_level_page = 0, @ resolve = 0, @ yy_level_eventlog = 2, @ delete_level = 0
Begin
Set @ errnum = 1
If (@ error <> 0 or @ returncode <> 0) goto quitwithrollback
End
-- Add job steps
Execute @ returncode = MSDB. DBO. sp_add_jobstep @ job_id = @ jobid, @ step_id = 1, @ step_name = n 'step 2 ', @ command = n' backup database [aspx1_oom] to disk = n' C: \ aspx1_oom. bak ''with noinit, nounload, name = n'' aspx1_oom backup test'', Skip, stats = 10, noformat', @ database_name = n'master ', @ Server = n', @ database_user_name = n', @ subsystem = n' tsql', @ cmdexec_success_code = 0, @ flags = 0, @ retry_attempts = 0, @ retry_interval = 0, @ output_file_name = n'', @ on_success_step_id = 0, @ on_success_action = 1, @ on_fail_step_id = 0, @ on_fail_action = 2
If (@ error <> 0 or @ returncode <> 0)
Begin
Set @ errnum = 2
Goto quitwithrollback
End
Execute @ returncode = MSDB. DBO. sp_update_job @ job_id = @ jobid, @ start_step_id = 1
Begin
Set @ errnum = 3
If (@ error <> 0 or @ returncode <> 0) goto quitwithrollback
End
-- Add Job Scheduling
Execute @ returncode = MSDB. DBO. sp_add_jobschedule @ job_id = @ jobid, @ name = n '1st scheduling', @ enabled = 1, @ freq_type = 8, @ active_start_date = 20051011, @ active_start_time = 0, @ freq_interval = 1, @ freq_subday_type = 1, @ freq_subday_interval = 0, @ blank = 0, @ freq_recurrence_factor = 1, @ active_end_date = 99991231, @ active_end_time = 235959
Begin
Set @ errnum = 4
If (@ error <> 0 or @ returncode <> 0) goto quitwithrollback
End
-- Add the target server
Execute @ returncode = MSDB. DBO. sp_add_jobserver @ job_id = @ jobid, @ SERVER_NAME = n' (local )'
Begin
Set @ errnum = 5
If (@ error <> 0 or @ returncode <> 0) goto quitwithrollback
End
End
Commit transaction
Goto endsave
Quitwithrollback:
If (@ trancount> 0) rollback transaction
Print 'error step' + @ errnum
Endsave:
Print 'execution finished! '

Exec databasebackup

Drop proc databasebackup

Begin transaction
Delete MSDB. DBO. sysjobs
Where (name = n'aspx1_image backup test ')
Commit transaction