Testing your backups with dbatools

It has always said, you’re as good as your last restore, not your last backup. How many of you make your backups and think that everything is OK. There comes a time that you have to restore your database from a backup and you find out that one or more backup files is corrupt.

Testing your backups is a tedious job and it takes a lot of time which I as a DBA don’t have. I don’t have the time to restore a database, run a DBCC command for every database that’s backed up.

There is a solution and it’s called “Test-DbaLastBackup” which is part of the dbatools module.

It goes through the following steps:

  1. Restore the database with the name “dbatools-testrestore-[databasename]” by default. The prefix can be changed.
  2. Run a DBCC check on the database
  3. Remove the database

You’ll see a progress bar how far the database restore is.

After the restore the DBCC command is executed. You’ll not see any progress for that step.

When the entire process is complete the command will output the results:

But for me that’s not enough. This is one database and some of my servers have more than 20 databases on it with sizes ranging from 50 GB to 500 GB (not that large yet but large enough). I want to create a job that executes the test on all the databases and send the results to me.

It’s not going to be a SQL Server Agent job but a Windows Scheduled Task. I don’t need the SQL Server Agent for this and it makes more sense to do this outside of SQL Server.

To start testing I created a file called “backuptest.ps1” and put in the following code:

## Load module and run functions now..
Import-Module dbatools
 
## Execute the test
try {
    Test-DbaLastBackup -SqlServer SSTAD-PC -Databases AdventureWorks2014, AdventureWorks2014_2, AdventureWorks2014_3 |  Export-Csv C:tempbackuptest.csv -NoTypeInformation
}
catch {
    $_.Exception.Message | Out-File C:Tempbackuptest.log
}

I added a try/catch block to make sure I would be able catch what went wrong.

If you don’t know how to execute a PowerShell script from the Windows Task Scheduler please read the following blog post: Use the Windows Task Scheduler to Run a Windows PowerShell Script.

After the setup my action looks like this:

Make sure your task is set to run under an account that has privileges to access the SQL Server and write the file.

A couple of things that could be in this script:

  1. Execute the script over multiple servers
  2. Mail the results
  3. Add checks and error catching to make sure everything runs

Unfortunately the command “Test-DbaLastBackup” doesn’t allow us to supply servers. I could copy the row that tests the backup but that’s not me. I want things to run in one go and repetitive things don’t work out for me.

I don’t want to log into my server and lookup the results. I want them in my e-mail box when I check my e-mail i the morning. For that you could use the Send-Message commandlet.

With the multiple servers, e-mail functionality and the checks the final code looks something like this:

function TestMyBackups
{
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true, ValueFromPipeline=$false)]
        [Alias("ServerInstance", "SqlInstance")]
        [string[]]$SqlServer,
        [Parameter(Mandatory=$true)]
        [string]$OutputPath,
        [Parameter(Mandatory=$false)]
        [string]$SMTP,
        [Parameter(Mandatory=$false)]
        [string]$From,
        [Parameter(Mandatory=$false)]
        [string]$To,
        [Parameter(Mandatory=$false)]
        [PSCredential]$MailCredential
    )
 
    BEGIN
    {
        ## Load module and run functions now..
        Import-Module dbatools
        
        # Test if the outputpath is available
        if(!(Test-Path $OutputPath))
        {
            try 
            {
                New-Item -ItemType Directory $OutputPath
            }
            catch 
            {
                Write-Error $_.ErrorMessage
            }
        }
 
        # Clean up the output path variable
        if(!($OutputPath.EndsWith("")))
        {
            $OutputPath += ""
        }
 
        # Create a timestamp
        $TimeStamp = Get-Date -format 'yyyyMMddHHmmss'
 
        # Set up the error log
        $ErrorLog = "$($OutputPath)BackupTest_$($TimeStamp).log"
    }
 
    PROCESS
    {
        foreach($s in $SqlServer)
        {
            if ($s)
    {
                Write-Host "Start testing backups for $($s)"
 
    # Check the instance if it is a named instance
                $ServerName, $InstanceName = $s.Split("")
 
                if($InstanceName -eq $null)
                {
                    $InstanceName = 'MSSQLSERVER'
                }
 
                # Execute the test
                try {
                    
 
                    # Set up the output file
                    $OutputFile = "$($OutputPath)BackupTest_$($ServerName)_$($InstanceName)_$($timestamp).csv"
 
                    # Test it!
                    try
                    {
                        Test-DbaLastBackup -SqlServer $s |  Export-Csv $OutputFile -NoTypeInformation
                    }
                    catch
                    {
                        # Oh oh error
                        $ErrorMessage = $_.Exception.Message
                        $TS = Get-Date -format 'yyyyMMddHHmmss'
                        "$($TS): $ErrorMessage" | Out-File $ErrorLog -Append
                    }
                
                    # Check if the results need to be e-mailed
                    if($SMTP -ne $null -and $From -ne $null -and $To -ne $null)
                    {
                        try 
                        {
                            Send-MailMessage -From $From -To $To -Subject "Backup Test $($ServerName)$($InstanceName)" -Body "Backup Test for server $($ServerName)$($InstanceName)" -Attachments $OutputFile -Priority High -SmtpServer $SMTP -Credential $MailCredential                            
                        }
                        catch 
                        {
                            # Oh oh error
                            $ErrorMessage = $_.Exception.Message
                            $TS = Get-Date -format 'yyyyMMddHHmmss'
                            "$($TS): $ErrorMessage" | Out-File $ErrorLog -Append
                        }
                    }
                    else 
                    {
                        "Couldn't send email for backup test on instance $($ServerName)$($InstanceName). Missing values in the e-mail parameters." | Out-File $ErrorLog -Append
                    }
                }
                catch {
                    # Oh oh error
                    $ErrorMessage = $_.Exception.Message
                    $TS = Get-Date -format 'yyyyMMddHHmmss'
                    "$($TS): $ErrorMessage" | Out-File $ErrorLog -Append
                }
            }
            else
            {
                Write-Error "No instance given."
            }
        }
    }
 
    END
    {
        Write-Host "Done Testing."
    }
}

To execute the code I added a line at the end of the file:

$pass = ConvertTo-SecureString “blabla” -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ("[email protected]", $pass)
 
TestMyBackups -SqlServer 'yourserver', 'yourserver2' -OutputPath 'yourpath' -SMTP 'yoursmptserver' -From 'youremailfrom' -To 'youremailto' -MailCredential $cred

The script works well although, I would have liked to put in functionality like values from pipeline etc.

I hope you can use this script to your advantage and that testing your backups is no longer a something to avoid but to embrace.

稿源:SQL Stad (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Testing your backups with dbatools

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录

登录

忘记密码 ?

切换登录

注册