As a DBA, working from home (WFH) is not a new concept or new experience, I do it a lot. However, this shift to working from home due to the office being closed for Covid-19 is a little different. First off, I’m enjoying getting my commute time and saving money on fuel. The downside is, since moving in 2017, I haven’t put a lot of effort into getting my work area properly set up for me. So that’s a bit distracting.

Other things I have learned Working from home

  • I need to keep my schedule as similar to the office as possible
  • I need to structure my day as I normally would – times for learning and deep dives, etc.
  • I need to remember to get up and move
  • I need to remember to take breaks

Work from homeI’m naturally an introvert and don’t mind being without a lot of people, so I imagine that’s been a little easier on me than most. Although there are times when I need to communicate or just banter with people: I can use Teams for that at work and Twitter for the SQL community, not that I’m on Twitter a lot.

With everyone else working from home, I have noticed a few things that I do differently that has made my life easier. I leave my work laptop at the office, mostly because it’s 10lb brick and I prefer carrying my MacBook instead. So instead of VPN’ing into the Office, I have a LogMeIn account, so I was spared all the issues with the VPN being maxed out. I also prefer LogMeIn because if my connection gets dropped to the laptop, and I’m running a script than the script doesn’t get ghosted on the SQL side.

Please don’t

Somethings I have seen people do that they probably shouldn’t over VPN:

  • Don’t do virtual meetings over the VPN, you’re chewing up bandwidth. Instead, drop the VPN connection to have the meeting.
  • Have a backup plan in case the VPN has an outage
  • Please, please for the sake of those you work with – pay attention to the ambient noises. We know your kids and pets are cute, but listening to them in the background makes it harder to hear.
  • Use Mute. Really….if you’re not talking, put yourself on mute.
  • Don’t drink or chew on calls unless you are muted.
  • If you have a webcam set up, be careful what you broadcast, and don’t forget it’s there.

It’s going to be awhile

Just because you are not in the office, that is no reason to act unprofessional or forget to take care of yourself. Working from home can provide a golden opportunity to get your diet under control, get some training in, as well as spending time with your family and doggos. You can feel good about reducing your carbon footprint (if that’s important to you), you can use the savings from commuting and eating out to fund a vacation when practical, or you can just enjoy the slowdown and quiet in your daily life. We’re going to be at this a while, so we may as get good at it.

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.