dbatools – fully automated offsite backups, database restores and integrity checks

In this article you’ll find the process I implemented to test my backups using dbatools and PowerShell.

dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Backed up by a community of passionate people, it will do wonders in your environments.

Requirements:

Now let’s dive a bit into the tasks I wanted to tackle. I needed to:

  1. copy my full backups to a separate location
  2. run daily restores to measure how long it will take me to react in case of a disaster
  3. perform daily integrity checks and still be able to run other maintenance tasks outside business hours (we have big databases > 1TB)
  4. constantly measure how long it takes for each of the above steps to complete
  5. not do all the above manually

Process:

To solve this, I designed the following process (simplified here to show only the interesting parts):

  1. From the Production servers (Server A, Server B, Server C) I run usual full backups on a local drive. That’s done using Ola Hallengren’s MaintenanceSolution. From the C&C (Command and Control) server which has the dbatools module installed, we run remote PS sessions on the Production servers (Server A, Server B, Server C) which are checking for the last full backup using:
    Get-DbaBackupHistory -SqlInstance $server -LastFull

    From the output I get the file name which is then copied to Server E (Samba share).
    Once this is done, task 1 is solved.

  2. The C&C server will run a PS remote session to Server D where it will restore the databases directly from the Samba share using:
    Restore-DbaDatabase -SqlInstance $srv -Path $backupDirRoot\$dir\$dbName -MaintenanceSolutionBackup -WithReplace -DestinationFilePrefix $prefix -RestoredDatababaseNamePrefix $prefix -DestinationDataDirectory $dataDir -DestinationLogDirectory $logDir

    With one line of code we are able to restore a bunch of databases, regardless if they were taken using different file paths or on different versions of SQL Server.
    This nails the second task.

  3. During the same session, right after the restores are finished, we trigger integrity checks:
    $query = "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = '$dbName'"
    Invoke-Sqlcmd2 -ServerInstance $targetServer -Database $dbName -Query "$query" -QueryTimeout 99999999

    Task 3 done, two to go!

  4. To measure the elapsed time of that particular command, I use:
    Measure-Command

    and keep this in a custom PS object.

    I’m writing the data from those custom PS objects to the a database located on the C&C Server.
    Of course, dbatools hadles that for me:

    # Record the results for File Copy
    $DataTable = $result | Select-Object FNameSource, FNameDestination, FSizeMB, CopyDuration_hh:mm:ss.mss, PSComputerName, ExecutionDate | Out-DbaDataTable
    Write-DbaDataTable -SqlInstance 'C&C Server' -InputObject $DataTable -Table autoDB.dbo.AutoFileCopy -AutoCreateTable
    # Record the results for restore db and integrity check operations
    $DataTable = $RestoreCheckDBResult | Select-Object Database, RestoreDurationMinutes, RestoreExecutionDate, CheckDBDurationMinutes, LastGoodCheckDbStatus, LastGoodCheckDb | Out-DbaDataTable
    Write-DbaDataTable -SqlInstance 'C&C Server' -InputObject $DataTable -Table autoDB.dbo.AutoRestoreCheckDB -AutoCreateTable
  5. Task 5 is completed with a simple scheduled task on the C&C Server:
    $powershellCmd = (Get-Command powershell.exe).Definition
     
    $action = New-ScheduledTaskAction -Execute $powershellCmd -Argument '-NoProfile -Executionpolicy bypass -WindowStyle Hidden -file "D:\DBA\AutoRestoreCheckDb.ps1"'
    $trigger = New-ScheduledTaskTrigger -Daily -At 6:00pm
    $settings = New-ScheduledTaskSettingsSet -Compatibility WIN8
     
    $STPrincipal = New-ScheduledTaskPrincipal -UserID "Domain\username" -RunLevel "Highest" -LogonType S4U
    Register-ScheduledTask -Action $action -Trigger $trigger -TaskName "AutoRestoreAndCheckDb" -Description "AutoRestoreAndCheckDb" -Principal $STPrincipal -Settings $settings

From here on I can have reports analyzing all the bits and pieces of my process.
Most important, I know now, how long it takes to restore each database and if indeed my backups are valid.
I’m also able to offload the DBCC CHECKDB outside Production servers.

Conclusion

All this is achieved in under 230 lines of code, with the help of the amazing dbatools PS module.

Adding more servers to the process will mean to just add them in a ServerList.txt file that the script consumes. Simple as that!

Now I have the backups and restores verified without moving a finger! How cool is that?

Thanks dbatools and the wonderful people behind it!

For more awesomeness, check out here the full spectrum of commands dbatools has for you!

 

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close