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

I know, the title is very long, but hey, these are all the operations done entirely automated just by 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 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!

 

How to Change your Network Profile in Server 2012R2

Network Profiles were first introduced to allow administrators to configure different firewall profiles based upon what network a user connects to.
Administrators could change the profile by navigating to Network and Sharing Center in control panel and selecting the profile which suits their needs.
In Server 2012, Administrators can no longer change the network profile in Network and Sharing Center, they can view just not change.
You can accomplish this in Power-Shell:
To see the current profile:

Get-NetConnectionProfile

To change the profile:

Set-NetConnectionProfile -InterfaceIndex 12 -NetworkCategory Private

You can do it with a one line command:

Get-NetConnectionProfile | Set-NetConnectionProfile -NetworkCategory Private

Get SMO version on your server

A quick way to find out what SMO versions are installed:

dir C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo

Silently Download and Install SQL Server Management Studio (SSMS)

SSMS is now free and no longer requiring licensing.
It is a separate install and has a more frequent release cycle, usually around 30 days.
To be able to maintain up to date a larger number of SSMS installations I wrote a PowerShell Script for the task.
Of course that a better approach will be to make the download once and then propagate it to other servers, but for now, this works best for me.

<#
.SYNOPSIS
    Silently Download and Install SQL Server Management Studio (SSMS).
.DESCRIPTION
    This script will download and install the latest available SSMS from Microsoft.
.PARAMETER WriteLog
    You want to log to a file. It will generate more than a few files :)
.EXAMPLE
    .\Get-LastSSMS -WriteLog 1
.NOTES
    Author: Viorel Ciucu
    Date: January, 2017
.LINK
 
#>
 
#Requires -RunAsAdministrator
 
[CmdletBinding()]
param (
    [parameter(Mandatory = $false)]
    [int]$WriteLog = 0
)
 
Clear
$ErrorActionPreference="SilentlyContinue"
 
if(-not $PSScriptRoot) {
    $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent
}
 
$msg = ""
$args = @()
$args += "/install /quiet /norestart" 
 
if($WriteLog -eq 1) { 
    $args += "/log SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
    $msg = "InstallationLog: $PSScriptRoot\SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
}
 
 
Write-Host "Download starting! Please wait.."
 
# Start the download
$Domain = "https://msdn.microsoft.com/en-us/library/mt238290.aspx"
$url = ((Invoke-WebRequest -uri $Domain).Links | Where innerHTML -match "Download SQL Server Management Studio").href | Select -First 1
$job = Start-BitsTransfer -Source $url -DisplayName SSMS -Destination "SSMS-Setup-ENU.exe" -Asynchronous
 
while (($Job.JobState -eq "Transferring") -or ($Job.JobState -eq "Connecting")) { 
    Start-Sleep 5;
} # Poll for status, sleep for 5 seconds, or perform an action.
 
Switch($Job.JobState) {
    "Transferred" { Complete-BitsTransfer -BitsJob $Job; Write-Output "Download completed!" }
    "Error" { $Job | Format-List } # List the errors.
    default { Write-Output "You need to re-run the script, there is a problem with the proxy or Microsoft has changed the download link!"; Exit } # Perform corrective action.
}
 
# We close running SSMS processes
if (Get-Process 'Ssms') {
    Stop-Process -Name Ssms
}
 
Write-Output "Performing silent install..."
 
# Install silently
Start-Process -FilePath SSMS-Setup-ENU.exe -ArgumentList $args -Wait -Verb RunAs
 
Write-Output $msg
Write-Output "All done!"

Hope you’ll find it useful.

Add syntax color to crontab editor

You might be wondering why you don’t have syntax coloring when you are editing crontab entries.
This can be solved very simply by specifying your editor before starting crontab:

export EDITOR=vim

The change can be made persistent across the whole system if you add that line on the /etc/bashrc file.
You you only need this change for certain users only, add the line to their local ~/.bashrc files.

DTExec: The package execution returned DTSER_FAILURE (1)

What to do when a SQL backup job created with SQL server Maintenance Plans fails with this error?

DTExec: The package execution returned DTSER_FAILURE (1).

Investigate! No useful information in the logs.
Previously that day I dropped a few unneeded databases so […] Continue Reading…

T-SQL to generate backup and restore commands

Hi guys,

Since my day to day job is a DBA, I figured I will share stuff that others might find useful.
For the first time I will share a small script which will help you generate T-SQL commands in order […] Continue Reading…

DriveDroid

DriveDroid is an Android application that allows you to boot your PC from ISO/IMG files stored on your phone.
Install it on your android phone and use it to boot any computer.
You can store any number of ISO/IMG files on […] Continue Reading…

Disable recent documents in Gtk2/Gtk3

Use the following to disable recent documents in Gtk2/Gtk3:

echo ‘gtk-recent-files-max-age=0’ | tee -a $HOME/.gtkrc-2.0
echo ‘gtk-recent-files-max-age=0’ | tee -a $HOME/.config/gtk-3.0/settings.ini

The ugly part is that there is no way one can disable ‘Recently Used’ in the GtkFileChooser dialog box.

Quick way to convert Mbox mailboxes to Maildir format

A quick way to convert Mbox mailboxes to Mbox format […] Continue Reading…