C2mRMtra

1USE [msdb]
2GO
3
4/****** Object:  Job [TransferDB_Backup]    Script Date: 11/28/2016 1:07:49 PM ******/
5BEGIN TRANSACTION
6DECLARE @ReturnCode INT
7SELECT @ReturnCode = 0
8/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11/28/2016 1:07:49 PM ******/
9IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
10BEGIN
11EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
12IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
13
14END
15
16DECLARE @jobId BINARY(16)
17EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TransferDB_Backup', 
18        @enabled=1, 
19        @notify_level_eventlog=0, 
20        @notify_level_email=0, 
21        @notify_level_netsend=0, 
22        @notify_level_page=0, 
23        @delete_level=0, 
24        @description=N'No description available.', 
25        @category_name=N'[Uncategorized (Local)]', 
26        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
27IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
28/****** Object:  Step [Create Backup]    Script Date: 11/28/2016 1:07:49 PM ******/
29EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup', 
30        @step_id=1, 
31        @cmdexec_success_code=0, 
32        @on_success_action=3, 
33        @on_success_step_id=0, 
34        @on_fail_action=2, 
35        @on_fail_step_id=0, 
36        @retry_attempts=0, 
37        @retry_interval=0, 
38        @os_run_priority=0, @subsystem=N'TSQL', 
39        @command=N'USE [TRANSFER]
40GO
41USE MASTER
42GO
43
44BACKUP DATABASE [TRANSFER] TO  DISK = N''\locationTransfer.bak'' WITH NOFORMAT, NOINIT,  NAME = N''TRANSFER-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
45GO
46', 
47        @database_name=N'master', 
48        @flags=0
49IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
50/****** Object:  Step [Delete objectz]    Script Date: 11/28/2016 1:07:49 PM ******/
51EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete objectz', 
52        @step_id=2, 
53        @cmdexec_success_code=0, 
54        @on_success_action=3, 
55        @on_success_step_id=0, 
56        @on_fail_action=2, 
57        @on_fail_step_id=0, 
58        @retry_attempts=0, 
59        @retry_interval=0, 
60        @os_run_priority=0, @subsystem=N'TSQL', 
61        @command=N'USE TRANSFER
62GO
63
64declare @n char(1)
65set @n = char(10)
66
67declare @stmt nvarchar(max)
68
69-- procedures
70select @stmt = isnull( @stmt + @n, '''' ) +
71    ''drop procedure .['' + schema_name(schema_id) + ''].['' + name + '']''
72from sys.procedures
73
74
75-- check constraints
76select @stmt = isnull( @stmt + @n, '''' ) +
77''alter table [TRANSFER].['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + '']    drop constraint ['' + name + '']''
78from sys.check_constraints
79
80-- functions
81select @stmt = isnull( @stmt + @n, '''' ) +
82    ''drop function [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
83from sys.objects
84where type in ( ''FN'', ''IF'', ''TF'' )
85
86-- views
87select @stmt = isnull( @stmt + @n, '''' ) +
88    ''drop view [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
89from sys.views
90
91-- foreign keys
92select @stmt = isnull( @stmt + @n, '''' ) +
93    ''alter table [TRANSFER].['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + ''] drop constraint ['' + name + '']''
94from sys.foreign_keys
95
96-- tables
97select @stmt = isnull( @stmt + @n, '''' ) +
98    ''drop table [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
99from sys.tables
100
101-- user defined types
102select @stmt = isnull( @stmt + @n, '''' ) +
103    ''drop type [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
104from sys.types
105where is_user_defined = 1
106
107
108
109--print @stmt
110exec sp_executesql @stmt
111', 
112        @database_name=N'TRANSFER', 
113        @flags=0
114IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
115/****** Object:  Step [Cleate tliggelfile]    Script Date: 11/28/2016 1:07:49 PM ******/
116EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cleate tliggelfile', 
117        @step_id=3, 
118        @cmdexec_success_code=0, 
119        @on_success_action=1, 
120        @on_success_step_id=0, 
121        @on_fail_action=2, 
122        @on_fail_step_id=0, 
123        @retry_attempts=0, 
124        @retry_interval=0, 
125        @os_run_priority=0, @subsystem=N'CmdExec', 
126        @command=N'echo 1 > \locationTransfer.trig', 
127        @flags=0
128IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
129EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
130IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
131EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
132IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
133COMMIT TRANSACTION
134GOTO EndSave
135QuitWithRollback:
136    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
137EndSave:
138
139GO