Remove-SQLDatabaseSafely My First Contribution to DBATools

What is DBA Tools?

A collection of modules for SQL Server DBAs. It initially started out as ‘sqlmigration’, but has now grown into a collection of various commands that help automate DBA tasks and encourage best practices.

You can read more about here and it is freely available for download on GitHub I thoroughly recommend that you watch this quick video to see just how easy it is to migrate an entire SQL instance in one command (Longer session here )

Installing it is as easy as

Install-Module dbatools

which will get you over 80 commands . Visit https://dbatools.io/functions/ to find out more information about them

cmdlets

The journey to Remove-SQLDatabaseSafely started with William Durkin b | t who presented to the SQL South West User Group  (You can get his slides here)

Following that session  I wrote a Powershell Script to gather information about the last used date for databases which I blogged about here and then a T-SQL script to take a final backup and create a SQL Agent Job to restore from that back up which I blogged about here The team have used this solution (updated to load the DBA Database and a report instead of using Excel) ever since and it proved invaluable when a read-only database was dropped and could quickly and easily be restored with no fuss.

I was chatting with Chrissy LeMaire who founded DBATools b | t about this process and when she asked for contributions in the SQL Server Community Slack I offered my help and she suggested I write this command. I have learnt so much. I thoroughly enjoyed and highly recommend working on projects collaboratively to improve your skills. It is amazing to work with such incredible professional PowerShell people.

I went back to the basics and thought about what was required and watched one of my favourite videos again. Grant Fritcheys Backup Rant

I decided that the process should be as follows

  1. Performs a DBCC CHECKDB
  2. Database is backed up WITH CHECKSUM
  3. Database is restored with VERIFY ONLY on the source
  4. An Agent Job is created to easily restore from that backup
  5. The database is dropped
  6. The Agent Job restores the database
  7. performs a DBCC CHECKDB and drops the database for a final time

This (hopefully) passes all of Grants checks. This is how I created the command

I check that the SQL Agent is running otherwise we wont be able to run the job. I use a while loop with a timeout like this

$agentservice = Get-Service -ComputerName $ipaddr -Name $serviceName
if ($agentservice.Status -ne 'Running') {
    $agentservice.Start()
    $timeout = new-timespan -seconds 60
    $sw = [diagnostics.stopwatch]::StartNew()
    $agentstatus = (Get-Service -ComputerName $ipaddr -Name $serviceName).Status
    while ($dbStatus -ne 'Running' -and $sw.elapsed -lt $timeout) {
        $dbStatus = (Get-Service -ComputerName $ipaddr -Name $serviceName).Status
    }
}

There are a lot more checks and logic than I will describe here to make sure that the process is as robust as possible. For example, the script can exit after errors are found using DBCC CHECKDB or continue and label the database backup file and restore job appropriately. Unless the force option is used it will exit if the job name already exists. We have tried to think of everything but if something has been missed or you have suggestions let us know (details at end of post)

The only thing I didn’t add was a LARGE RED POP UP SAYING ARE YOU SURE YOU WANT TO DROP THIS DATABASE but I considered it!!

Performs a DBCC CHECKDB

Running DBCC CHECKDB with Powershell is as easy as this

$sourceserver = New-Object Microsoft.SQLServer.Management.Smo.Server "ServerName"
$db = $sourceserver.databases[$dbname]
$null = $db.CheckTables('None')

you can read more on MSDN

Database is backed up WITH CHECKSUM

Stuart Moore is my go to for doing backups and restores with SMO

I ensured that the backup was performed with checksum like this

$backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$backup.Action = [Microsoft.SqlServer.Management.SMO.BackupActionType]::Database
$backup.BackupSetDescription = "Final Full Backup of $dbname Prior to Dropping"
$backup.Database = $dbname
$backup.Checksum = $True

Database is restored with VERIFY ONLY on the source

I used SMO all the way through this command and performed the restore verify only like this

$restoreverify = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
$restoreverify.Database = $dbname
$restoreverify.Devices.AddDevice($filename, $devicetype)
$result = $restoreverify.SqlVerify($sourceserver)

An Agent Job is created to easily restore from that backup

First I created a category for the Agent Job

Function New-SqlAgentJobCategory {
    param ([string]$categoryname,
        [object]$jobServer)
    if (!$jobServer.JobCategories[$categoryname]) {
        if ($Pscmdlet.ShouldProcess($sourceserver, "Creating Agent Job Category $categoryname")
            {
                try {
                    Write-Output "Creating Agent Job Category $categoryname"
                    $category = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobCategory
                    $category.Parent = $jobServer
                    $category.Name = $categoryname
                    $category.Create()
                    Write-Output "Created Agent Job Category $categoryname"
                }
                catch {
                    Write-Exception $_
                    throw "FAILED : To Create Agent Job Category $categoryname - Aborting"
                }
            }
        }
    }
}

and then generated the TSQL for the restore step by using the script method on the Restore SMO object

This is how to create an Agent Job

$job = New-Object Microsoft.SqlServer.Management.Smo.Agent.Job $jobServer, $jobname
$job.Name = $jobname
$job.OwnerLoginName = $jobowner
$job.Description = "This job will restore the $dbname database using the final backup located at $filename"

and then to add a job step to run the restore command

$jobStep = new-object Microsoft.SqlServer.Management.Smo.Agent.JobStep $job, $jobStepName $jobStep.SubSystem = 'TransactSql' # 'PowerShell' 
$jobStep.DatabaseName = 'master' 
$jobStep.Command = $jobStepCommmand 
$jobStep.OnSuccessAction = 'QuitWithSuccess' 
$jobStep.OnFailAction = 'QuitWithFailure' 
if ($Pscmdlet.ShouldProcess($destination, "Creating Agent JobStep on $destination")
    { 
        $null = $jobStep.Create()
    } 
    $job.ApplyToTargetServer($destination)
    $job.StartStepID = $jobStartStepid 
    $job.Alter()

 

The database is dropped

We try 3 different methods to drop the database

$server.KillDatabase($dbname)
$server.databases[$dbname].Drop()
$null = $server.ConnectionContext.ExecuteNonQuery("DROP DATABASE ")

The Agent Job restores the database

To run the Agent Job I call the start method of the Job SMO Object
    $job = $destserver.JobServer.Jobs[$jobname]
    $job.Start()
    $status = $job.CurrentRunStatus
    while ($status -ne 'Idle') {
        Write-Output " Restore Job for $dbname on $destination is $status"
        $job.Refresh()
        $status = $job.CurrentRunStatus
        Start-Sleep -Seconds 5
    }
Then we drop the database for the final time with the confidence that we have a safe backup and an easy one click method to restore it from that backup (as long as the backup is in the same location)
There are further details on the functions page on dbatools
Some videos of it in action are on YouTube http://dbatools.io/video
You can take a look at the code on GitHub here

You can install it with

Install-Module dbatools
You can provide feedback via the Trello Board or discuss it in the #dbatools channel in the Sqlserver Community Slack
You too can also become a contributor https://dbatools.io/join-us/ Come and write a command to make it easy for DBAs to (this bit is up to your imagination).

SQL Server Operators and Notifications with Powershell – Strange Enumerate issue fixed by @napalmgram

Alerting of issues across the SQL Server estate is important and recently I needed to audit the operators and the notifications that they were receiving.

I created a SQL Server Object

2013-09-04_125056

One of the important things to remember when investigating SMO is the Get-Member cmdlet. This will show all methods and properties of the object

$server | Get-Member

gave me the JobServer Property

$Server.JobServer|gm

includes the Operator Property

$Server.JobServer.Operators | gm
2013-09-04_125717

has the EnumJobNotifications and EnumNotifications methods

So it was easy to loop through each server in the servers.txt file and enumerate the notifications for each Operator

2013-09-04_130052

and create a simple report

However this does not work as it does not perform the second enumerate. Try it yourself, switch round the EnumJobNotifications and EnumNotifications methods in that script and see what happens.

So I ended up with two functions

2013-09-04_174005
2013-09-04_173953

and I thought I could do this

2013-09-04_174056

But that doesnt work

So I tried this

2013-09-04_174112

and that doesnt work either

Now the reports are coming out showing the correct number of lines but not displaying them. I spent a period of time on my Azure boxes trying to work a way around this. I set the outputs to both enums to a variable and noted that they are different type of objects.

2013-09-05_113931

Job Notifications are System.Object and Alert Notifications are System.Array

I tried to enumerate through each member of the array and display them but got too tired to finish but I had contacted my friend Stuart Moore Twitter | Blog who had a look and resolved it by simply piping the Enumerates to Format-Table. Thank you Stuart.

So the final script is as follows

2013-09-05_114601

and the script is

#############################################################################################
#
# NAME: Show-SQLServerOperators.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:03/09/2013
#
# COMMENTS: Load function for Enumerating Operators and Notifications
# ————————————————————————

Function Show-SQLServerOperators ($SQLServer) {
    Write-Output "############### $SQLServer ##########################"
    Write-Output "#####################################################`n"     

    $server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
        
        
    foreach ($Operator in $server.JobServer.Operators) {
        $Operator = New-Object ("$SMO.Agent.Operator") ($server.JobServer, $Operator)

        $OpName = $Operator.Name
        Write-Output "Operator $OpName"
        Write-Output "`n###### Job Notifications   ######"
        $Operator.EnumJobNotifications()| Select JobName | Format-Table
        Write-Output "#####################################################`n"  
        Write-Output "`n###### Alert Notifications  #######"
        $Operator.EnumNotifications() | Select AlertName | Format-Table
        Write-Output "#####################################################`n"  
                 
    }
 
}