Home » Scripts » Look for new databases (…with a pinch of DPM)


Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Azure infrastructure and system management. I am a senior engineer in the FastTrack for Azure team, part of Azure Engineering, at Microsoft.  Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.

Anders Bengtsson

MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator Unleashed
Orchestrator 2012 Unleashed
Inside the Microsoft Operations Management Suite

Look for new databases (…with a pinch of DPM)

With SQL Server Audit, SQL Server 2008 introduces an important new feature that provides a true auditing solution for enterprise customers. While SQL Trace can be used to satisfy many auditing needs, SQL Server Audit offers a number of attractive advantages that may help DBAs more easily achieve their goals such as meeting regulatory compliance requirements. These include the ability to provide centralized storage of audit logs and integration with System Center, as well as noticeably better performance. Perhaps most significantly, SQL Server Audit permits fine-grained auditing whereby an audit can be targeted to specific actions by a principal against a particular object. This paper provides a comprehensive description of the new feature along with usage guidance and then provides some practical examples. Source MSDN

If you want to get an alert when a new database is created in SQL 2008 you will first need to configure auditing on SQL side, and then a rule in Operations Manager to generate an alert. Configure a new audit with audit destination equals Application log or security log. If you select security log you might need to configure some extra security permissions. Create a new server audit policy, configure audit action type equals DATABASE_CHANGE_GROUP.


Next step is to create the rule that will pickup the SQL event and generate an alert. Create a new event based rule, target it to for example SQL Servers, to monitor all your SQL machines. Configure the rule to look for event ID 33205 including CREATE and DATABASE in the event description.


In the expression of the rule, we use “.” to tell Operations Manager “any character before, around or after the two keywords, CREATE and DATABASE.



Another step that you might want to do is to verify that you backup the new SQL database. I modified a power shell script and the result is that it will connect to your DPM server and a SQL box, it will then ask you if you want to add any of the unprotected databases on that server to a protection group in DPM. 

param([string] $ProductionServer, [string] $PGName)
$ProductionServer = read-host "Enter the production server name (a SQL server protected by DPM)"
$PGName = read-host "Enter the name of your existing SQL protection group name"

$dpmservername = read-host “Enter the name of your DPM server”

connect-dpmserver $dpmservername

$PGList = @(Get-ProtectionGroup $dpmservername)

foreach ($PG in $PGList)
if($PG.FriendlyName -eq $PGName)
write-host “Found protection group $PGName”
$MPG = Get-ModifiableProtectionGroup $PG

write-host “Protection Group $PGName does not exist”
exit 1

$PSList=@(Get-ProductionServer $dpmservername)
$DsList = @()

foreach ($PS in $PSList)
if($PS.NetBiosName -eq $ProductionServer)
write-host “Running Inquiry on” $PS.NetbiosName
$DSlist += Get-Datasource -ProductionServer $PS -Inquire

“Production Server $PS does not exist”
exit 1

$protectedDsList = @()
foreach ($ds in $dslist)
if($ds.ToString(“T”, $null) -match “SQL” -and !$ds.Protected)
$toadd = read-host “Do you want to protect the” $ds.Name “database? (y/n)?”
If ($toadd -eq “y”)
$protectedDsList += $ds
Add-ChildDatasource -ProtectionGroup $MPG -ChildDatasource $ds
$x=Get-DatasourceDiskAllocation -Datasource $ds
Set-DatasourceDiskAllocation -Datasource $x -ProtectionGroup $MPG

Set-ReplicaCreationMethod -ProtectionGroup $MPG -Now

write-host “Adding new SQL DBs to” $MPG.FriendlyName
Set-protectiongroup $MPG

disconnect-dpmserver $dpmservername
“Exiting from script”


(tested in a sandbox, so I am aware that the ops mgr databases are not protected and all the test databases) If you want to integrate the script into Ops Mgr you should read this post from David Allen.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.