SQL Server 数据库快照妙用

chinaab

　　USE [msdb]　　GO
　　/****** Object:　Job [Database snapshot]　　Script Date: 01/29/2008 16:39:31 ******/
　　BEGIN TRANSACTION
　　DECLARE @ReturnCode INT
　　SELECT @ReturnCode = 0
　　/****** Object:　JobCategory [[Uncategorized (Local)]]]　　Script Date: 01/29/2008 16:39:31 ******/
　　IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
　　BEGIN
　　EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
　　IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
　　END
　　DECLARE @jobId BINARY(16)
　　EXEC @ReturnCode =　msdb.dbo.sp_add_job @job_name=N'Database snapshot',
　　@enabled=1,
　　@notify_level_eventlog=0,
　　@notify_level_email=0,
　　@notify_level_netsend=0,
　　@notify_level_page=0,
　　@delete_level=0,
　　@description=N'No description available.',
　　@category_name=N'[Uncategorized (Local)]',
　　@owner_login_name=N'Dinesh-Mobdinesh', @job_id = @jobId OUTPUT
　　IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
　　/****** Object:　Step [drop database snapshot]　　Script Date: 01/29/2008 16:39:32 ******/
　　EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'drop database snapshot',
　　@step_id=1,
　　@cmdexec_success_code=0,
　　@on_success_action=1,
　　@on_success_step_id=0,
　　@on_fail_action=3,
　　@on_fail_step_id=0,
　　@retry_attempts=0,
　　@retry_interval=0,
　　@os_run_priority=0, @subsystem=N'TSQL',
　　@command=N'DROP DATABASE [ssAdventureWork_dbss2230]',
　　@database_name=N'master',
　　@flags=0
　　IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
　　/****** Object:　Step [Create Snapshot]　　Script Date: 01/29/2008 16:39:32 ******/
　　EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Snapshot',
　　@step_id=2,
　　@cmdexec_success_code=0,
　　@on_success_action=1,
　　@on_success_step_id=0,
　　@on_fail_action=2,
　　@on_fail_step_id=0,
　　@retry_attempts=0,
　　@retry_interval=0,
　　@os_run_priority=0, @subsystem=N'TSQL',
　　@command=N'CREATE DATABASE ssAdventureWorks_dbss2230 ON
　　( NAME = AdventureWorks_Data, FILENAME =
　　''C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss'' )
　　AS SNAPSHOT OF AdventureWorks;
　　',
　　@database_name=N'master',
　　@flags=0
　　IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
　　EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 2
　　IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
　　EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule',
　　@enabled=1,
　　@freq_type=4,
　　@freq_interval=1,
　　@freq_subday_type=8,
　　@freq_subday_interval=1,
　　@freq_relative_interval=0,
　　@freq_recurrence_factor=0,
　　@active_start_date=20080129,
　　@active_end_date=99991231,
　　@active_start_time=0,
　　@active_end_time=235959
　　IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
　　EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
　　IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
　　COMMIT TRANSACTION
　　GOTO EndSave
　　QuitWithRollback:
　　IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
　　EndSave: