Deploying DBA General Jobs

The following tests deploy DBA general jobs on a new environment
1. Get the original backup file
Backing up a database from a deployed instance

--backing up the databaseBACKUP DATABASE [Dba_monitor]  to  DISK =N'D:\Perfmon\DBA_Monitor_F_20160308.bak'  withNoformat, Noinit, NAME=N'dba_monitor-full Database backup', SKIP, Norewind, Nounload, COMPRESSION, STATS= TenGOBACKUP DATABASE [msdb]  to  DISK =N'D:\Perfmon\msdb_F_20160308.bak'  withNoformat, Noinit, NAME=N'msdb-full Database backup', SKIP, Norewind, Nounload, COMPRESSION, STATS= TenGO

View Code

2. Restore the database
Restore the Dba_monitor library and the msdb library

--monitoring the tables used, the database in which the stored procedures resideRestoreHeaderonly from DISK =N'F:\TroubleShooting\DBA_Monitor_F_20160308.bak' RESTORE DATABASE [Dba_monitor]  from  DISK='F:\TroubleShooting\DBA_Monitor_F_20160308.bak'  with  FILE=1, MOVE'Dba_monitor'  to 'D:\Program Files\Microsoft SQL Server\mssql10_50.sql08r2\mssql\data\dba_monitor.mdf', MOVE'Dba_monitor_log'  to 'D:\Program Files\Microsoft SQL Server\mssql10_50.sql08r2\mssql\data\dba_monitor_log.ldf', Nounload,REPLACE, STATS=TenGO--msdb library with monitoring jobs restored to msdbnewRESTORE DATABASE [msdbnew]  from  DISK =N'F:\TroubleShooting\msdb_F_20160308.bak'  with  FILE = 1, MOVE N'Msdbdata'  toN'D:\Program Files\Microsoft SQL Server\mssql10_50.sql08r2\mssql\data\msdbnew.mdf', MOVE N'Msdblog'  toN'D:\Program Files\Microsoft SQL Server\mssql10_50.sql08r2\mssql\data\msdbnew_log.ldf', Nounload,REPLACE, STATS= TenGO

View Code

3. Migrate the required jobs using the job migration script
In the Where condition, increase the filter to migrate the related jobs. Sometimes you may need to modify the job step, click Edit Job Error

Reference link Modification

" D:\Program Files\Microsoft SQL Server\100\dts\binn\dts.dll "

Dts.dll you want to specify the installation path to SQL Server, and later to see if the edit job will also error.
You can also refer to the job stored procedure in "writing job script as" to modify the job

--Modify the database for the job stepSELECT *  fromMsdb.dbo.sysjobsWHERENAME like '% table size%'EXECMsdb.dbo.sp_update_jobstep@job_id = '2c4d4932-e019-43be-8bde-0a13b2cd158f',@step_id=1,@database_name=N'Dba_monitor'EXECMsdb.dbo.sp_update_jobstep@job_id = '2c4d4932-e019-43be-8bde-0a13b2cd158f',@step_id=2,@database_name=N'Dba_monitor'

4, perform each migration job, check whether the operation is normal
The original data can be emptied before the job is executed

 Use Dba_monitor GO  @command1= "TRUNCATETABLE ?"

In the 4th step of the process, found that one of the job error, has been implemented as a user NT Authority\System identity. The collation violation between "SQL_Latin1_General_CP1_CI_AS" and "chinese_prc_ci_as" in the equal to operation cannot be resolved. [SQLSTATE 42000]  (Error 468). The step failed.

Check that the corresponding stored procedure found is in the execution of the following statement times wrong

 from #temp  aINNERJOIN  CTE b on a.jobname= B.jobname  and a.step_id=b.step_id

#temp是通过create table, no special settings are created. The CTE is the job information obtained from Sysjobs, sysjobhistory. Previously, it was also done in such a way that no errors were reported for the collation.
Check msdb, dba_monitor database sorting, find msdb corresponds to sql_latin1_general_cp1_ci_as,dba_monitor corresponding chinese_prc_ci_as, instance level corresponds CHINESE_PRC_ Ci_as collation. If the collation of the relevant database server is chinese_prc_ci_as, how does your local msdb collation become Latin1? Later remembered is a test migration job, the msdb database with x\mssql\binn\templates\ under the Msdbdata, Msdblog covered! The collation of the msdb library is inconsistent with the installation!
Resolution: 1, modify the database collation (invalid)

 Use [master] GO ALTER DATABASE [msdb] COLLATE chinese_prc_ci_as GO

Cannot perform changes to database ' msdb ' because it is a system database.
2. Specify the collation of the JobName field when creating the #temp table (valid)

Create Table #JobRunLongtime (    --jobname varchar (+) NOT NULL,    varchar(128  notnull,

3. Add a collation after the associated field (valid)

 from #temp  aINNERJOIN  CTE b on A.jobname collate sql_ Latin1_general_cp1_ci_as=b.jobname and a.step_id=b.step_id

The field step_id is of type int and does not need to be specified as a Latin1 collation. After the modification of Method 2 or Method 3, this problem is resolved, but the job is still error, and it has been executed in the identity of the user NT Authority\System. The Service Broker messaging feature is not enabled in this database. Use the ALTER DATABASE statement to enable the Service Broker message delivery feature. [SQLSTATE 42000]  (Error 14650). The step failed.

According to the prompt to know that the message is sent error, can be referenced Database Mail, other Database Mail errors refer to the proxy family to configure Database Mail.

Deploying DBA General Jobs