Updating Email ID for Alerts and Mail Items

Recently I came across a situation where in I had to change the Email ID for all alerts and Mail Items on multiple servers. If you want to accomplish this through GUI it would be quite painful. After digging out I found out an easy way.

1. You can accomplish this by running the below set of query on individual servers.

2. You can accomplish this by running the below set of query on Central Management Server.

For how to configure Central Management Server(CMS) I will add a link soon or may in next post.

Query:

update msdb..sysoperators set email_address = ‘YourEmailID@YourDomain’ where name=’YourAlertName’

GO

update a SET a.recipients=‘YourEmailID@YourDomain’ 

from msdb..sysmail_mailitems a

inner join msdb..sysmail_profile b

on a.profile_id = b.profile_id

where b.description=’YourDescriptionName’

Enabling CDC while Restoring Database.

Restore the Source Database to Destination Database with parameter KEEP_CDC.

Only “KEEP_CDC” needs to be included in “WITH” Option while restoring database.

Ex: WITH KEEP_CDC

The above process will only work if the Source Database is CDC enabled and CDC jobs are already existing on Destination Server.

If the CDC Jobs for destination database are not exiting then two jobs <Databasename>_Capture and <Databasename>_Change needs to be created manually and needs to be added to msdb cdc jobs using below mentioned procedure.

use msdb

GO

exec sys.sp_cdc_add_job ‘change’

GO

exec sys.sp_cdc_add_job ‘cleanup’

GO

Once the above query is executed you will get message as jobs started successfully. After this the changes made to CDC Enabled table will be captured in CDC table.

If the CDC Jobs are already existing for destination database then restoring database with KEEP_CDC option is enough after which it will capture all the changed made to CDC Enabled tables to CDC tables as the Jobs are already existing and running

Technorati Tags: CDC,Restore,WITH CDC