TSQL Tuesday #96: Thanking those that helped along the way

This month’s TSQL Tuesday fits into the theme of the month. Thanksgiving. The TSQL Tuesday invitation is being sent out by Ewald Cress (b|t).

There have been many people who have helped my career along the way. Most have indirectly helped via their blog posts, videos, tweets, training classes, user group topics, SQL Saturday Sessions, or PASS Summit sessions. Those people have spent countless hours to help the community learn and get better by the work they do and share. Some of these people I have had the pleasure of meeting and thanking in person, some I have not had that chance yet.

While I have crossed paths with some incredibly smart people in my career, there are those that have left a great impact.

I started my career as a developer and fell into the DBA role. The first role I had was a good starting role for a DBA. Small environment, a few databases, and exposure to both OLTP and Data Warehousing. I could learn on the job and quickly grasp the concepts of what needed to get done to keep things going relatively smoothly. However, I had no measuring stick. I had no way to determine if I was doing a good job other than no one was at my desk to complain that the data warehouse was down. I went out looking for a new position in the field to give me a greater challenge and the ability to learn the things I did not know. I went out for an interview, which I did not think went all that great. Questions were asked that I admittingly did not know the answers to. This brings me to the first person that had great impact on my career.

Scott Hitchcock

He saw that I did not know certain things, but also saw that I was hungry to learn. He decided to recommend me to his boss to hire me. Shortly thereafter I started as a Jr DBA. I just went from an environment that was basically the size of a swimming pool to one the size of Lake Ontario. He taught me tons about handling a large environment, clustering, HA/DR, how to do performance troubleshooting and tuning, how to handle changes in a good way, security, when to pull the trigger on an action and when not to, and many more things. Some of these things I had done at my previous environment, but if I ran into a wall, that was it, I could go no further as I did now know how to get past the wall. Under Scott, the wall got further away, also, when I did get to the wall, I now had someone I could ask for help. Getting stuck was now a delay to me, not an all-out halt. I learned so much during my 4 years working with him. He allowed me to ask questions when needed, and work through an issue on my own when needed. Most importantly, when I screwed up he’d course correct.

Rick Heiges and Larry Chestnut

I entered the world of consulting which is like stepping onto an alien world. No longer was I exposed to one environment which its unique set of challenges. I was now being exposed to a different environment each week, with a different set of challenges and requirements. I now must adjust my thinking each week to solve a different set of problems and a new set of rules. I was thankfully paired up with two architects that would guide me through various challenges. They elevated my thinking to a higher level from the super low-level details of a project to a higher-level thinking of an architect. They taught me to understand the bigger picture and apply that bigger picture knowledge to the low-level details that I was accustomed. They also both taught me how to present the work I was doing at different client sites to their different audiences. At every client, there are always two audiences. The first being the day-to-day worker, their in-house DBA or other IT staff. The second being management, whether C-level or other. While both audiences appreciate the level of detail I will go to in solving a problem, they are not necessarily both interested in hearing all those details. The IT worker may want to, so they can understand what is going on and resolve the issue at hand, but the management level does not. They want to know how fast the issue can be fixed and how much impact the solution will have. The management level needs to be less technical and detailed as the IT staff. I know feel comfortable talking through a solution with either audience due to these two.

Mike Hillwig and Andy Mallon

Neither of these two people I have had the pleasure of working with, yet still both have had impact on my career. While both are highly skilled DBAs, their impact comes from my interactions with them at user group meetings. While working as a consultant, I had to learn to present, but secretly I did not like doing so. I love doing the work, but hate being the center of attention. I would much rather be behind the scenes making things awesome than in the front of everyone. I am pretty sure Mike and Andy both understand this about me, yet don’t care. Generally, when I go to a user group meeting, I will sit in the back and listen in, somewhat participate in the discussions, and work on something I was dealing with at the office. One of these times, Mike was looking for speakers for upcoming user group sessions. He was telling everyone that we all have a story to tell and can share something with the group that will help members of the group. He then went on to say he is going to pick someone randomly to do a lightening talk at the next meeting. He stopped what he was doing, put his hands on the table and then just proceeded to stare at me, until I looked up. I was his random pick. As he says, I was volun-told to present. He later volun-told me to speak at SQL Saturday Boston.

While Mike’s involvement with the user group has lessened, Andy’s has picked up. He has worked tirelessly to make Boston SQL into a strong user group, while also becoming the organizer of the SQL Saturdays in the Boston area. While these two things alone are enough to say thank you for, Andy is always pushing me to present more or blog more. I have had many private conversations with Andy that have left me thinking and inspired to do more. He has also made me realize that while part of me still hates presenting, not out of fear, but out hatred of being the center of attention, another part of me likes it. The part of me that likes it, is the part of me that likes to help people. I like to see others learn and get better. It is a great feeling when you can help someone and see that person grow.

Each person in this post has had great impact on my career, and I cannot thank them enough. Each person has helped me grow and get better over the years. Scott imparted great technical skills. Rick and Larry added to and sharpened those technical skills and helped me translate them into ways different audiences could understand. Mike and Andy got me to get out of my head and start to give back.

Automatic Seeding Error

In my lab, I decided to play around with the automatic seeding functionality that is part of Availability Groups. This was sparked by my last post about putting SSISDB into an AG. I wanted to see how it would work for a regular database. When I attempted to do so, I received the following error:

Automatic Seeding Error
Cannot alter the availability group ‘Group1’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

This seemed odd to me. What would cause this error? What changed in my environment?

First, I realized that I did break down my AG and recreate it in management studio. When I created the group, I did not put any databases into the group, so I cold test automatic seeding.

Next, I decided to go through the process and instead of using the GUI, I scripted out the commands and would run manually. This way I could pinpoint where the process was failing.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect sql2016n1

USE [master]

GO

ALTER AVAILABILITY GROUP [Group1]
MODIFY REPLICA ON N'SQL2016N2' WITH (SEEDING_MODE = AUTOMATIC)

GO

USE [master]

GO

ALTER AVAILABILITY GROUP [Group1]
ADD DATABASE [WWI_Preparation];

GO

:Connect sql2016n2

ALTER AVAILABILITY GROUP [Group1] GRANT CREATE ANY DATABASE;

GO

When I ran the above code, the steps that were to run on SQL2016N1, succeeded, but those that were to run on SQL2016N2, failed. Additionally, I received the below message:

Msg 15151, Level 16, State 1, Line 24
Cannot alter the availability group ‘Group1’, because it does not exist or you do not have permission.

I went and looked at the AlwaysOn High Availability folder in SSMS on SQL2016n2 and found nothing.

2017-05-15_17-07-58

So, the group cannot be given permissions to create any database, because the group did not exist. Then it dawned on me as to why.

Creating an availability group, without databases using the GUI, only creates the group on the primary. It does not reach out to the secondary replicas to join those nodes to the group. This must be done manually. So to resolve the error, I had to run this code on SQL2016N2:


USE [master]
GO
ALTER AVAILABILITY GROUP Group1 JOIN
GO

This allowed the secondary replica to join the AG that was established on SQL2016N1. Then I was able to proceed with the next step of running


USE [master]
GO
ALTER AVAILABILITY GROUP [Group1] GRANT CREATE ANY DATABASE;
GO

Once this was completed, the database started seeding from one node to the other.

I think the biggest takeaway from this is the importance of using a script to do DBA actions instead of the GUI. It allows for a more focused and deliberate execution, where you can see things happen in order. This, in turn, teaches you and allows for greater flexibility, when things go bump.

Enabling AlwaysOn for SSISDB

I am at client where I get to do some fun work. I get to work on a brand new environment that is dedicated to SQL Server. It has some pretty cool specs. Biggest thing is that they are going all 2016. Both the OS and SQL Server will be 2016. This has let to a lot of fun and interesting discussions. One of those being how do we place the SSIS Catalog into an availability group.

This led me to ponder the possibilities. When AlwaysOn first came out, this was not supported. Sure, you could find clever work-arounds for this, but at the end of the day, they were not sanctioned by Microsoft. This would then leave the client in a bit of a bind if they ran into issues. However, starting in SQL Server 2016, this is now supported. Microsoft has made it possible to officially place the SSISDB into an availability group.

Which group you put the SSISDB in, will be a design question that you will need to answer for yourself. In short, it should go into a group that makes sense. If all your databases are in one group, then placing this in DB into the same group makes sense. If you have multiple groups, you may need to make a choice that best suits your application needs.

In this example, I will assume you already have an availability group set up with a listener. In my lab, I have a very simple setup. my availability group is called Group1 and my listener is called Listener1.

This video shows the steps I took to add the SSISDB into an availability group. I even show publishing a SSIS package to the listener name instead of a SQL Server Name.

In case you do not want to watch the video, below is the process I did to add the SSISDB into the availability group.

  1. On the Primary Replica, open SQL Server Management Studio
  2. Right click on Integration Services Catalog, click Create Catalog
    img1
  3. On the Create Catalog Dialog,
    img2
    Check the box to enable automatic execution of Integration Services stored procedures at SQL Server startup. Then enter a strong password.
  4. Open the Availability Group folder and navigate to the group you want to add the SSISDB to. Right click on Availability Database and click Add Database.
    img3
  5. Due to the encryption that the SSISDB uses, you will need to provide the password you used when creating the catalog. Put that password into the password field, click refresh and then click the check box next to SSISDB. Click Next
    img4
  6. On the Add Database to Availability Group dialog, ensure that Automatic Seeding is selected. Click Next.
    img5
  7. You will be presented with a list of the secondary replicas, connect to each one listed. Then click Next
    img6
  8. The information will be validated. As long as there are no failures, click Next.
    img7
  9. On the summary dialog, click Finish. SSMS will add the database to the availability group
    img8
  10. Click Close
    img9
  11. Once the database has been added to the availability group, go to the Integration Services Catalog folder, right click and click Enable AlwaysOn Support. (you may need to refresh this folder before the option is available to you)
    img10
  12. Click connect next to each secondary replica. Then click OK.
    img11

The SSISDB is now established in an availability group and will failover based on the group rules.

Spring Cleaning

The birds are singing, the grass is able to be seen, the air is warm, the sun is shining, more importantly, baseball has started. I finally get to go enjoy some down time at America’s most beloved ballpark. Fenway Park for the non-New Englanders. Yes, Spring is finally here and with Spring generally, comes some cleaning up. I have a VM lab that I run on my Surface Pro 4. Nothing crazy, just a bunch of VMs to test out different versions of SQL  in different scenarios. Recently I had to build up a 3 node cluster to test with Windows and SQL Server 2016 Always On Availability Groups for a client. The tests required me to tear down and install SQL Server multiple times while I tested out a set of scripts to install and configure SQL, and later test out failover scenarios. More on that later though.

As I was going through my environment, I realized I created a new domain controller for my tests. This DC has a new name and domain name which is different from my other VMs. I quickly realized that this will cause me issues later with authentication. No worries. I will just boot up the VMs and then and join them to the new domain. Easy-peasy. Now let met go test out my SQL Servers.

DOH!!

I received a login failure with access is denied. Using Windows Authentication with my new domain and recently joined server is not working. Why?…..Oh right, my new user id does not have access to SQL Server itself. As I sit there smacking myself in the head, I am also thinking about the amount of time it will take me to rebuild those VMs. Then it hit me!!!

Powershell to the rescue!!!

More specifically, dbatools to the rescue.

Now if you have not heard of dbatools, you have to check this out. Head over to dbatools.io and look around on the site. This set of Powershell cmdlets, started out as a project by Chrissy Lemaire to make database migrations super simple. Now with a couple of cmdlets, she can migrate an entire server and sit back enjoying a beer. Now, with the help of a few MVPs and input from the SQL Community, this has evolved into a fairly comprehensive set of cmdlets to aid DBAs everywhere in their day-to-day tasks. I highly recommend checking this out.

One of the cmdlets in dbatools is a command called Reset-SQLAdmin. This is a super useful script. You point this at a SQL Server and it will either reset the sa account or add an account to your SQL Server with sysadmin access. It is meant for you to use as a tool when you have lost access to your SQL Server. This fits my needs perfectly. The command is as easy as running the below command.

Reset-SqlAdmin -SqlServer sqlserver\sqlexpress -Login ad\administrator

That is it. It will go through its process and after a few seconds, you will have access to your SQL Server. Want to see it in action? Check out the video on the dbatools.io site. One thing to note, this process does require an outage as it will stop and start your SQL Services. So do not use this in production. 

So back to my issue….I ran this against my 6 VMs, and voila, I am in. I had access to my old VMs with little to no effort what so ever. This saved me hours of time and effort. Now I am able to reorganize the VMs in my lab and clean up old users and database.

Powershell and dbatools making things easy-peasy!

 

 

My WTF….Really? Moment

So, to join into this month’s #tsqltuesday88, we are to write about a “WTF” moment. Mine is not directly database related, but it is related.

I was presenting at the NESQL User Group monthly meeting. I was giving a lightning talk on Powershell for a DBA. (See…somewhat related.) It was not meant to be a long presentation. At most about 15 minutes. I was coasting along talking about the enhancements made by Microsoft and the community when all of a sudden my machine would not accept any input from my keyboard or mouse. I have a Surface Pro 4, that suddenly decided to stop talking to the keyboard. Now, I was not about to let this stop me. Yes, it slowed me down, but stop me no…well not yet. I tried to use the touch screen. Yay…it was working. So I tried to continue on using the touch screen but then realized that I could not. There is no F8 key or way to highlight text, then copy and paste the text.

After a few minutes of trying to live troubleshoot, I had to give in to the Demo Gods. They have not smiled favorably upon me this day. I took my Surface and went to my seat and started looking around at what was going on. Windows Update hit me earlier in the day and had not properly finished. It was acting up and I had to hard stop it. I purposely had not wanted to do any updates just to prevent this from happening. I even made sure my demos were practiced and typing would not be needed. I remember the tip I have heard many times, never type in a demo)

After several reboots and a firmware update, and more reboots, everything was working fine. All I could say on this day was WTF…Really?

Now to put together the blog post on the talk I was going to give and figure out what my next talk will be on. Maybe give this talk at the RI SQL User Group.

 

DSC Install of SQL Server

It has been a while since I have made a post and figured it was long over due. I figured for my first post in a while, it would be about something I have been working on lately. The automation of installing and configuring of SQL Server.

So the installation of SQL Server is now fairly straightforward. The wizard does a nice job of guiding you along the way. 2016 even includes best practice suggestions for tempdb and instance file initialization. Along the way, Microsoft as given us ways to automate the installation of SQL Server. You can sysprep an instance, but this does not really automate the installation. It just helps create a template of an instance. At the end of the day, you still need to do things manually. You can also use a configuration file to assist here. This is a great step forward, but it does not allow for all of the things you need to do to configure a SQL server.

Powershell does. Desired State Configuration (DSC) is functionality built into Powershell that allows for the installation and configuration of a SQL Server.

First, to get started, you need to have a base understanding of DSC. Without this, the rest of this post will be hard to follow, as DSC can be difficult to follow. The link below will take you to the videos on the Microsoft Virtual Academy site that Jeffrey Snover put together that explains how DSC works.

Getting Started with Powershell Desired State Configuation (DSC)

Also, the modules you will need to make DSC work in your environment can all be downloaded from the Powershell Gallery. I have also found that searching for the module on GitHub, has returned back better documentation on how each module works.This has been great in my learning of how to get DSC going.

For the example what I have put together, we will use a Push mode for our DSC script.

The snippet that is below, uses a Powershell section called Configuration. This is similar to a Powershell Function in construction and how it works.

Configuration SQLServerInstall
    {
        param
        (
            [string]$ComputerName,
            [int32]$MAXDOP,
            [int32]$MAXMemory
        )
        Import-DscResource –Module PSDesiredStateConfiguration
        Import-DscResource -Module xSQLServer
        Import-DscResource -Module SecurityPolicyDsc
        Import-DscResource -Module xPendingReboot

        Node $ComputerName
        {
            WindowsFeature NET-Framework-Core
            {
                Name = "NET-Framework-Core"
                Ensure = "Present"
                IncludeAllSubFeature = $true
            }
            xSqlServerSetup InstallSQL
            {
                DependsOn = '[WindowsFeature]NET-Framework-Core'
                Features = $Configuration.InstallSQL.Features
                InstanceName = $Configuration.InstallSQL.InstanceName
                SQLCollation = $Configuration.InstallSQL.SQLCollation
                SQLSysAdminAccounts = $Configuration.InstallSQL.SQLSysAdminAccounts
                InstallSQLDataDir = $Configuration.InstallSQL.InstallSQLDataDir
                SQLUserDBDir = $Configuration.InstallSQL.SQLUserDBDir
                SQLUserDBLogDir = $Configuration.InstallSQL.SQLUserDBLogDir
                SQLTempDBDir = $Configuration.InstallSQL.SQLTempDBDir
                SQLTempDBLogDir = $Configuration.InstallSQL.SQLTempDBLogDir
                SQLBackupDir = $Configuration.InstallSQL.SQLBackupDir

                SourcePath = $Configuration.InstallSQL.SourcePath
                SetupCredential = $Node.InstallerServiceAccount
            }
            xSQLServerNetwork ConfigureSQLNetwork
            {
                DependsOn = "[xSqlServerSetup]InstallSQL"
                InstanceName = $Configuration.InstallSQL.InstanceName
                ProtocolName = "tcp"
                IsEnabled = $true
                TCPPort = 1433
                RestartService = $true
            }
            xSQLServerConfiguration DisableRemoveAccess
            {
                SQLServer = $ComputerName
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                OptionName = "Remote access"
                OptionValue = 0
            }
            UserRightsAssignment PerformVolumeMaintenanceTasks
            {
                Policy = "Perform_volume_maintenance_tasks"
                Identity = "Builtin\Administrators"
            }
            UserRightsAssignment LockPagesInMemory
            {
                Policy = "Lock_pages_in_memory"
                Identity = "Builtin\Administrators"
            }
            xPendingReboot PendingReboot
            {
                Name = $ComputerName
            }
            LocalConfigurationManager
            {
                RebootNodeIfNeeded = $True
            }
            xSQLServerAlwaysOnService EnableAlwaysOn
            {
                SQLServer = $ComputerName
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                Ensure = "Present"
            }
            xSQLServerMaxDop SetMAXDOP
            {
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                MaxDop = $MAXDOP
            }
            xSQLServerMemory SetMAXDOP
            {
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                MaxMemory = $MAXMemory
                DynamicAlloc = $False
            }
        }
    }

To break down the script and the areas in the Configuration function, the names are thankfully fairly self explanatory.
WindowsFeature – Ensures that Windows features are present. This is a function that will initiate the installation if needed
xSqlServerSetup – This section will install SQL Server. If you look at the properties it is asking for, it will mirroring most of the properties that are in a configuration file
xSQLServerNetwork – This section is used to enable protocols, like TCP and configure what port SQL Server should use.
xSQLServerConfiguration – Is used to set values that you would normally set via sp_configure
UserRightsAssignment – Will set values in the Local Security Policy. In this case, I am setting who can Lock Pages in Memory and Perform Volume Maintenance Tasks
xPendingReboot – Will check to see if a reboot is required and reboot if needed
xSQLServerAlwaysOnService – Used to enable the Always On Service
xSQLServerMaxDop – Used to set MAXDOP
xSQLServerMemory – Used to set MIN and MAX memory values

Any place you see $Configuration.InstallSQL.* this is a value from a JSON file. I use this a parameter file that will feed this script. This allows me to easily reuse this script as many times as I need.

When the Powershell Script runs, the DSC will output a text file with an extension of MOF. DSC will then push this configuration file to the server and then proceed to configure the server as instructed. Then I use scripts that I have written or found along the years to finish the configuration. Ideally, using Powershell, I can configure the SQL Server any way I would like.

 

I have posted my entire set of scripts for installing and configuring SQL Server on Githib. You can find the scripts here. Here you can see the full example. Enjoy.

T-SQL Tuesday #85

For the last T-SQL Tuesday of the year, this month is being hosted by Kenneth Fisher (b|t) and is about backup and recovery.

Any DBA knows that backups are crucial to their job. Without good backups it is very difficult to ensure that the environment is protected from dangers that lurk all around. Any DBA knows the BACKUP and RESTORE commands fairly well. They have scripts handy that can do a backup or a restore of database ready to go at the slightest whisper of a problem. However there are new commands that are available via Powershell to assist a DBA in getting a backup or a restore completed. These can possible aid in doing those items faster, as you do not need to wait for SSMS to load. Lets take a look at these commands at their basic level. I am going to use WideWorldImporters and my SQL 2016 instance.

Restore Database

T-SQL

USE [master]
RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'E:\Backup\WideWorldImporters-Full.bak'
WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'E:\SQLData\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'E:\SQLData\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'E:\SQLLog\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'E:\SQLData\WideWorldImporters_InMemory_Data_1',
NOUNLOAD,
STATS = 5
GO

Powershell

$RelocateData1 = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('WWI_Primary', 'E:\SQLData\WideWorldImporters.mdf')
$RelocateData2 = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('WWI_UserData', 'E:\SQLData\WideWorldImporters_UserData.ndf')
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('WWI_Log', 'E:\SQLLog\WideWorldImporters.ldf')
$RelocateData3 = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('WWI_InMemory_Data_1', 'E:\SQLData\WideWorldImporters_InMemory_Data_1')
Restore-SqlDatabase -ServerInstance 'sql2016' -Database 'WideWorldImporters' -BackupFile 'E:\Backup\WideWorldImporters-Full.bak' -RelocateFile @($RelocateData1,$RelocateData2,$RelocateData3,$RelocateLog)

Backup Database

T-SQL

BACKUP DATABASE [WideWorldImporters]
TO  DISK = N'E:\Backup\WideWorldImporters-Full.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'WideWorldImporters-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

Powershell

Backup-SqlDatabase -ServerInstance 'sql2016' -Database 'WideWorldImporters' -BackupFile  'E:\Backup\WideWorldImporters-Full.bak'

As you can see, these basic commands are very similar. With some additional review and time, the possibilities to automate a backup or restore of a database, or a group of databases, becomes quite easy. Better yet, these commands are native T-SQL and Powershell statements.

What if you want to go deeper? What if you want to answer the following questions

  1. When was my last database backup?
  2. What is the list of the last backups that were done?
  3. When was the database restored?

DBAs have scripts that will answer these questions relatively quickly using T-SQL. If they do not, they will spend time writing the code themselves or spend time searching online for a query that someone else has published already that answers the questions. What if you want to do this via Powershell? At this time, there is no native command provided by Microsoft to answer the above questions. However, the SQL Community has taken it upon themselves to create Powershell commands that will answer the above questions and do much more.

Chrissy LeMaire (b|t), a Data Platform MVP out of Belgium, and few other DBAs and MVPs have teamed up to create a suite of Powershell commands aimed at DBA. I encourage you to check out their site, https://dbatools.io/ , and download their module. The suite covers a bunch of common DBA tasks as well as database migration tasks. Here I will highlight 3 commands that will answer the above questions.

When was my last database backup?

This is a simple question that we as DBAs get asked, and ask a lot. With the dbatools Powershell module, this question can be answered in one line.

Get-DbaLastBackup -SqlServer sql2016 | Out-GridView

The above simple command returns back the below data

capture1

What is the list of the last backups that were done?

This is another question we all need to answer from time to time. Generally, we will need to answer this question when we need to a restore and need to know what files have been created recently and where they exist.

get-dbabackuphistory -SqlServer sql2016 -Databases WideWorldImporters | Out-GridView

This above one liner, returns back the below output. This tells me that my latest backups were a transaction log and a full backup. From here I can go and perform the restore if I so desired.

capture2

What is great about a command like this is that I can take this and create subsequent restore commands in Powershell and automate the restore.

When was the database restored?

Lastly, at times we need to know when a database was restored and from what file it was restored from. Again, a Powershell one liner from dbatools comes to our rescue to quickly and efficiently answer the question.

Get-DBARestoreHistory -SqlServer sql2016 -Databases WideWorldImporters -Detailed | Out-Grid

Here is the output:

capture3

Powershell is a great tool for the DBA to become more efficient in the normal day to day work. The above commands that I have highlighted above is just scraping the surface. To learn more in depth info about these commands, please read Chrissy’s blog which explains this new functionality much deeper and with videos.

The team that is putting together the dbatools suite, is doing a great job coming up with commands that are useful now to all DBAs. I hope you explore what they have to offer and incorporate them into your daily work.