This week I had to restore to a point in time for an entire instance. The instance had approx 25 databases on it, and all of them had to be replaced to get the information we needed. As this was on a SQL Availability Group, we are backing up the logs every 15 minutes. We also use Ola’s backup scripts, so if you’re not familiar with those, the directory structure is <Database Name> -> subfolder the DIFF, FULL and LOG in the database folder. So the choices were to go through the SSMS GUI or spend half a day working out the logic to script out the restores.

However, I came up with a different idea. I have been working the DBATools PowerShell module and was able to use that to script out a full restore with minimal lines of code, and way faster than half of a day working out the logic. What I came up with, well for me at the time, was pretty ingenious and damn sure easier than restoring via the GUI.

## Insert list of databases with format
$dbList= 'db1',
'db2',
'DB3',
'db4'

foreach ($dbName in $dbList)
{

## for the path, insert UNC path. for time, use 24 hr clock
$path = '<insert UNC path here>\' + $dbName 
$rtime = '20:30 03/17/2020'

## dbatools module
restore-dbadatabase -Sqlinstance <server name here> -Path $path -DatabaseName $dbName -MaintenanceSolutionBackup -RestoreTime $rtime

}

I don’t know why I am slow to take up new tools, but I am. I recently, like back in December, began working with DBATools. In case you’re not familiar, DBATools is a PowerShell module assembled by the community over at DBATools.io. This module, oh this wonderful module, makes some of the most aggravating parts of a DBA’s job so much easier. Things like server migrations, copying users, adding Ola’s jobs, and a slew of other things so much simpler.

What sold me? Well in December I had to migrate 70 some small databases off to a new SQL AG. I knew I was going to have to script out the backups so I decided this was a good as a time as any to learn Powershell and solve a problem. That thought process got me thinking about DBATools, and I decided to see if the module could help me out. Oh, and help me out it did. This module is a tool EVERY DBA should be using.

DBATools works

I went about setting up the list of databases into a Powershell list, created a specific UNC file location for the backups to land on, and ran the following the code:

Backup-DbaDatabase -SqlInstance $server -Database $dblist -Path $path -Type Full -CopyOnly -compressbackup

Where $server was the name of the server I was backing up from, $dblist was the list of databases I needed, $path was the UNC location. The -CopyOnly switch, made sure I didn’t affect the backup chain on the server I was migrating from. Although, this was less of a concern post-migration.

The really cool part about this, or at least I thought it was cool, was the restore command.

Restore-DbaDatabase -SqlInstance $newServer -path $path 

The restore command allowed me to point the restore command to the path, and it restored each database in the directory to the default file locations. Pure genius.

Other areas of the toolset I used were copy users, migrate settings, migrate jobs, and copy linked servers to name a few. Oh, this tool saved me so much work and time. I’m not sure why I did not embrace it earlier.

One of the things that has always frustrated me with Microsoft’s tools like SSMS and Visual Studio, is the sheer depth of the tool. Figuring out how to create an SSRS or SSIS package in Visual Studio the first few times can result in half a day wasted. SSMS can be just as bad some days. So it’s nice when you come across a blog series that points out features or tips and tricks you may not know about.

Wayne Sheffield has created a blog series on SSMS pointing out tips and tricks to help make learning these things easier. It’s worth checking out.

A month of SSMS tips and tricks

Awhile back Russ Thomas did 31 days of SSMS and that link is here.

31 days if SSMS