How To Use Powershell to Configure Multiple Databases for Log Shipping

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Configure Log Shipping With PowerShell

I first used some commands to find what I could use to do this.

Get-Command *LogShipping* -Module DBATools

#This returned Invoke-DbaDbLogShipping

#Then I ran the below to look at the help for this command
Help Invoke-DbaDbLogShipping -Full 

<#I knew I would need to get the databases on the SQL instance so I did the
below and I came across Get-DbaDatabase
#>
Get-Command *database* -Module DBATools

<#Then I ran the help for that to see how to use the command#>
Help Get-DbaDatabase -FULL

 

Then I wrote two options for providing a list of databases to the script. The first option is a hard-coded list of databases. I did this because you might not want to set up every database on an instance to be log shipped. Perhaps you’re only interested in certain ones.  Rather than hard-coding in the PoSH script, you could change this to have the script read a list of databases from a provided text file as well.

The second option assumes that you want to log ship all user databases. I used the switch -ExcludeSystem to make sure the script didn’t try to log ship my system databases.

#Populate a list of databases on the instance that you want to configure logShipping for 
$SQLInstanceName = "MySQLInstance" 
$Databases = "CollegeFootball", "DBAUtility", "StackOverflow2013", "UMSLIntermediateSQLClass" 

<#Or to set up Log Shipping for every user database on the SQL instance#> 
$SQLInstanceName = "MySQLInstance" 
$Databases = get-dbadatabase -sqlinstance $SQLInstanceName -ExcludeSystem -Database $Databases | SELECT Name

 

An Example from the PowerShell Help

Next, from the help for the log shipping command I pulled out one of the examples. This only gets me one database at a time though, so I need to modify this so I can set up log shipping for multiple databases at once.

PS C:\>$params = @{

>> SourceSqlInstance = 'sql1'
>> DestinationSqlInstance = 'sql2'
>> Database = 'db1'
>> SharedPath= '\\sql1\logshipping'
>> LocalPath= 'D:\Data\logshipping'
>> BackupScheduleFrequencyType = 'daily'
>> BackupScheduleFrequencyInterval = 1
>> CompressBackup = $true
>> CopyScheduleFrequencyType = 'daily'
>> CopyScheduleFrequencyInterval = 1
>> GenerateFullBackup = $true
>> RestoreScheduleFrequencyType = 'daily'
>> RestoreScheduleFrequencyInterval = 1
>> SecondaryDatabaseSuffix = 'DR'
>> CopyDestinationFolder = '\\sql2\logshippingdest'
>> Force = $true
>> }
>>
PS C:\> Invoke-DbaDbLogShipping @params

I add in a foreach loop and put it all together to arrive at the below to do this for a selected list of databases.

<#Populate a list of databases on the instance that you want to configure logShipping for. You could also get the list from a text file, if you wanted to.
#>
$SQLInstanceName = "MySQLInstance"
$Databases =  "CollegeFootball", "DBAUtility", "StackOverflow2013"

# Configure Log Shipping.
foreach ($Database in $Databases)
{
  $params = @{
    SourceSqlInstance = $SQLInstanceName
    DestinationSqlInstance = 'MySQLInstance\sql2019'
    Database = $Databases
    BackupNetworkPath = '\\MySQLInstance\LSBackups'
    BackupLocalPath = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\LSBackups'
    BackupScheduleFrequencySubdayType = 'Minutes'
    BackupScheduleFrequencySubdayInterval = 5
    CopyDestinationFolder = 'D:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\LogShippingCopy'
    CopyScheduleFrequencySubdayType = 'Minutes'
    CopyScheduleFrequencySubdayInterval = 5
    RestoreScheduleFrequencySubdayType = 'Minutes'
    RestoreScheduleFrequencySubdayInterval = 5
    GenerateFullbackup = $true
    CompressBackup = $true
    NoInitialization = $false
    NoRecovery = $true
    Force = $true
    }
  Invoke-DbaDbLogShipping @params 
}

Take note of a few things.

  1. First, there is a switch in the script called GenerateFullBackup that I’m setting to true. If you have existing backups you want to use, there is a parameter for that called UseExistingFullBackup, and a related parameter called UseBackupFolder.
  2. Second, compressing the backup is not enabled by default, so I used the CompressBackup parameter and set it to true in order to compress backups.
  3. Third, a parameter called DisconnectUsers exists that, in combination with setting the StandBy parameter to true, will make the databases on the secondary readable and then disconnect users when the Log Shipping schedule for the restores kicks in.
  4. I am using the NoRecovery switch set to True. You will need to remove that if you want to use the StandBy Parameter.

 

Log Shipping All User Databases

To configure log shipping with PowerShell so that all the eligible user databases on a SQL instance are done, you would do the following. Notice in this example, I’ve changed the $params for Database from simply $Databases to $Database.Name.

<# 
For setting up Log Shipping on all eligible user databases 
Notice in this script that I changed the Database part of the script to $Database.Name
#>
$SQLInstanceName = "MySQLInstance"
$Databases = get-dbadatabase -sqlinstance $SQLInstanceName -ExcludeSystem | SELECT Name

# Configure Log Shipping 

foreach ($Database in $Databases)
{
    $params = @{
        SourceSqlInstance = 'MySQLInstance'
        DestinationSqlInstance = 'MySQLInstance\sql2019'
        Database = $Database.Name
        BackupNetworkPath = '\\MySQLInstance\LSBackups'
        BackupLocalPath = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\LSBackups'
        BackupScheduleFrequencySubdayType = 'Minutes'
        BackupScheduleFrequencySubdayInterval = 5
        CopyDestinationFolder = 'D:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\LogShippingCopy'
        CopyScheduleFrequencySubdayType = 'Minutes'
        CopyScheduleFrequencySubdayInterval = 5
        RestoreScheduleFrequencySubdayType = 'Minutes'
        RestoreScheduleFrequencySubdayInterval = 5
        GenerateFullbackup = $true
        CompressBackup = $true
        NoInitialization = $false
        NoRecovery = $true
        Force = $true
    }

    Invoke-DbaDbLogShipping @params 

}

 

These scripts allow you to configure Log Shipping with PowerShell either for a pre-determined list of databases or for all eligible databases on the SQL instance.

Next Steps to Take

  1. Consider looking up other articles about Log Shipping to learn the details and variations. For example, here and here.
  2. Find a good introduction to PowerShell for use with SQL Server because automation is key these days, especially as your work environment gets larger.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

Top 3 Most Popular Posts of 2020

So, I realize it’s now almost April of 2021 when I’m posting this. Normally this sort of post would come out in January for the previous year. However, I have a reason for this being late and I’ll actually post about that separately.

Showing My Blog Some Love

In mid-2020 I decided to give my blog more attention. there were a number of reasons for this. First, I’d already spent a fair amount of money over the years on hosting and related things so I didn’t want to waste that. Second, I really wanted to try to start giving back to the SQL Server community. I have been the recipient of a lot of learning and help through a number of regular, and occasional, bloggers. I have also purchased and mostly read my fair share of SQL Server books, which was another way I had been helped by the community. Third, I wanted a place to record my own troubleshooting and learning so when I needed to do something again, I would have a record of what to do. By the way, I have seen this repeatedly listed as a reason that someone blogs.

Blog Posts by Month

So, here is the break down of the number of posts by month for 2020:

  • April – 1
  • July – 1 This was the last week of the month and marks my decision to start blogging more consistently.
  • Aug – 3
  • Sept – 4
  • Oct – 4
  • Nov – 7 – I had built a good backlog of posts that were ready to publish and nearly reached my goal of posting twice a week for the entire month
  • Dec – 4

To get the list of the most popular blog posts I looked at Google Analytics PageView data for Jan 1, 2020 – Dec 31, 2020.

Popular Post # 1

The most viewed page based on this data had 354 views and was part 1 of a series on PowerShell for the DBA. The series was meant to show some “getting started” type techniques so that someone could open PowerShell and start exploring what the tool could do for them as a DBA. I explored how to find commands that might be of interest and how to use the help system in PowerShell to figure out how to use the command. I also provided some practical examples of PowerShell to use in every day scenarios.

https://leemarkum.com/archive/2020/11/introduction-to-powershell-for-the-dba-part-1/ 

Popular Post #2

The second most viewed page had 332 views and covered 5 different ways to make a SQL Server backup. Backups are so important, especially in the world of data. They give you and your business a way to recover data that has been lost or damaged in some way. As a Database Administrator, you need to be able to recover and that all starts with taking backups. That seems obvious but I still read stories of businesses that aren’t even backing up their databases. If you do have backups, you also need also to test whether you can use those backups to actually restore data.

https://leemarkum.com/archive/2020/09/5-ways-to-make-sql-server-backups/ 

Popular Post #3

The third most viewed post had 273 views and was about using a new feature in SQL Server 2019. This feature is based on the polybase technology that arrived in SQL Server 2016. However, the feature has now been extended to allow SQL Server to have external tables to ole db and odbc sources, such as SQL Server, MS Access, and, yes, even things like Oracle and IBM iSeries/AS400. The external, or virtual table, uses mostly built-in drivers. With this feature yo can run SELECT statements against remote data sources without a linked server. The environment I’m in has an AS400/IBM iSeries so I wanted to see if I could get the feature to work with that data source.

https://leemarkum.com/archive/2020/04/querying-an-as400-using-sql-server-2019-data-virtualization/

There you have it. I hope you enjoyed these posts in 2020, and if you haven’t seen them, give them a click and read through the information. I hope you learn something from the posts and if you have questions or comments reach out to me here, or on Twitter, and I’ll be happy to talk to you.

My Availability Group Database Isn’t Synchronizing

I currently manage 5 AlwaysOn Availability Groups. Two are on SQL Server 2014 and overdue for an upgrade, while three of them are on SQL Server 2017.  From time to time I have run into a couple of different situations that I needed to troubleshoot and I want to tell you where to look and what to check on in these scenarios. I can’t possibly tell you about everything that could go wrong, but I can tell you about my experience with AlwaysOn Availability Groups and let you decide if that experience helps you or not.

A Single Database Is Not Synchronizing

If a single Availability Group database is in the “Not Synchronizing” state, what would you do?

There are several things to check. First you could look in SQL Server Management Studio to see if data movement has been suspended. If it has then you will see an icon similar to two pipes || over the database icon.  You will also see messages in the Error Log about data movement being suspended. If this is the case, try to resume data movement. This can be done in SSMS by right clicking the database in the Availability Databases folder and choose “Resume Data Movement”. Alternatively, you could execute the below T-SQL.

ALTER DATABASE [YourDatabase] SET HADR RESUME

Beyond that you can also use PowerShell to resume data movement for the database.

<#
gcm is the alias for Get-Command. Below I'm using wildcards to find any commands with the wor "resume" in it
and specifying that I want PowerShell to look in the DBATools module.
#>
gcm *resume* -Module DBATools

<#
Once I have found a command I think might help me, then I run the alias Help for Get-Help
to find out how to use the command

#>

help Resume-DbaAgDbDataMovement -Detailed

#Resumes data movement on db1 and db2 to ag1 on sql2017a. Prompts for confirmation.

Resume-DbaAgDbDataMovement -SqlInstance sql2017a -AvailabilityGroup ag1 -Database db1, db2
    

 

Second, check the SQL Server Error Log for errors related to the AG and check the  AlwaysOn_health xEvent that is running by default for AGs. To check the default health session for AlwaysOn Availability Groups, open SSMS and navigate to Management > Extended Events > Sessions > AlwaysOn_health. Then, click the plus sign next to it in order to drill down to see the file. Right click the file and choose “View Target Data”. After doing that, you can filter and search the file for relevant entries.

Extended Events AlwaysOn Health

If none of these things gives you enough information to resolve the problem, you could also restart the SQL Server service on the secondary that is showing the database that isn’t synchronizing. Again, there are many ways to do this, and I’m going to encourage you to look at the DBATools module in PowerShell to find an automated way to handle this. As a last resort, you could remove the database from the AG and then re-add it to the AG.

None of the Availability Group Databases Are Synchronizing

What if all of the databases are in the “not synchronizing” state, what would you do?

This is a little different because in this case every database on at least one secondary is in the “Not Synchronizing” state. The prior scenario was only about a single database with a synchronization issue.

The primary thing I check in this scenario is whether the cluster service is running on the secondary. If it isn’t, then none of the databases will synchronize. I have seen this happen several times, particularly after secondary servers have been rebooted. You could also have a network disconnect between this replica and the primary. Again though, the SQL Server Error log or the AlwaysOn_health extended events session will show you this. Additionally, you can look in the Cluster Events section of the Failover Cluster Manager for one of the nodes in the AG. In there you may find that you have lost quorum for some reason.

If for some reason you need to get down into the fine details of what is happening with the failover cluster, then check out the following path in the Windows Event Viewer.

Event Viewer > Applications and Services Log > Microsoft > Windows > FailoverClustering. In that location are three logs called Diagnostic, DiagnosticVerbose and Operational.

Next Steps To Take

  1. Go to YouTube and find videos on Availability Groups.
  2. Consider taking a class from the fine folks at SQLSkills about Failover Clustering and Availability Groups.
  3. Reach out to somebody if you have questions. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

Measuring Your DBA Skills

Over the last 9 weeks I took you on a journey of skills and career topics related to being a SQL Server DBA. We looked at the Production DBA. We saw skills and career topics from the beginning to mid-career to Senior DBA. Then we looked at the Development DBA and their skills and career development needs. Finally there was a wrap up post.

To make it easier for everyone to get to these posts, I decided to bring them all together on a single page.

measuring your skills

 Production DBA Skills Years 0-2

Production DBA Skills Years 2-4

Production DBA Skills Years 4-7

Senior Production DBA Skills Years 7+

Development DBA Skills Years 0-2

Development DBA Skills Years 2-4

Development DBA Skills Years 4-7

Senior Development DBA Skills Years 7+

Series wrap-up

If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

How Do I Measure My DBA Skills Part 9

Production and Development DBA Skills and Things We Didn’t Touch On

We’ve covered a lot of ground for Production and Development DBAs in terms of the skills and applicable career topics. We’ve mentioned necessary skills with T-SQL, backup/restore, automation, PowerShell, troubleshooting, SSIS, SSRS, patching, Query Store, HA/DR, leadership and emotional intelligence. That’s quite a list! However, looking back at the series, there are still a number of things that weren’t mentioned.

Other skills and career topics not discussed along the way:

  1. OS skills – This once used to be just Window skills. However, with SQL Server on Linux, and depending on what is in use in your environment, you may need to learn the basics of the Linux platform as well as Windows.
  2. I also didn’t touch on the topic of SQL Server Wait Statistics. This is a tried and true method for diagnosing server level issues that can lead to further investigation about what ails your SQL Server.
  3. What about the cloud and virtualization? Azure, AWS, Google Cloud Platform, VMware, Hyper-V. These are all virtualization platforms that are in use these days, so you may encounter these and need to know, or might want to know, something about how they work.
  4. What about certifications? Are they worthwhile? Should you pursue one? If yes, which one?

Then there is also a third career title I didn’t even mention. What about the DBA whose focus or specialty is in BI? You are the person who takes care of the SQL Servers, but maybe you also write a lot of reports for the business using SSRS, Power BI, maybe SSAS, Tableau, Qlik or some other reporting platform. This may really just be a specialty within the Development DBA role, but it could be separate as well.

Beyond the Senior levels of the types of DBA careers discussed, you’re likely to end up moving into management. That will open up an entirely different set of needed skills and career topics that you will need to explore.

Next Steps To Take

Planning - To Do List

  1. If you’ve read through this series and followed the instructions about building a training plan for skills you would like to develop, then you likely have a significant list of skills to work on. Don’t focus on how long the list is. Select one thing and work on it for however long it takes for you to feel at least semi-comfortable with it. Then, cross it off the list and pick something else. Repeat the process. Within a few weeks or months, you will see a lot of progress.
  2. Remember to keep your skills/career development plan somewhere that allows you to see the plan every day. This will help keep you on track and your To Do list prioritized.
  3. You can go to kand.io and find a variety of graded skills test. There is one for Database Administrators and one for Database Developers. Both were written by the fine people at SQLSKills.com.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

Introduction To PowerShell For The DBA Part 3

Hello dear reader! This is the third post in a series to help you get started automating things with PowerShell. You will be looking at how to start and stop SQL Server using PowerShell as well as setting the Max Server Memory setting, measuring free disk space and more.

This series is just an introduction to PowerShell and basic functionality you might be interested in as a SQL Server Database Administrator or as a System Administrator responsible for SQL Server. Because it is introductory I don’t have examples of long scripts that do a series of complex things as part of these posts. What the series does have are one or two line scripts that are still powerful and help you explore what is available to you in PowerShell.

With that in mind, let’s delve into a few more PowerShell commands that can help you with common tasks.

How To Start And Stop SQL Server Using PowerShell

Open PowerShell as an Administrator and run these commands. These are simple ways to search for commands related to the SQL Server services. Since we’re looking for information related to services, the first command searches for cmdlets containing the word “service”.  Since we know we want to start or stop the service, we’re looking for commands containing “start” or “stop” in those second and third commands.

GCM *service* -Module DBATools, SQLServer

GCM *start* -Module DBATools, SQLServer

GCM *stop* -Module DBATools, SQLServer

Service Related PowerShell Commands

PowerShell Commands with Start

Commands With Stop in PowerShell DBATools SQLServer

I’m showing you how to find interesting commands related to what you’re trying to accomplish, but I also want you to see how much else you can learn about the capabilities of the DBATools and SQLServer PowerShell modules. As you can see from the output, DBATools has Stop-DBAService, Start-DBAService, Restart-DBAService. The SQLServer module has Stop-SqlInstance and Start-SqlInstance.

I encourage you to look through the output of the screenshots, or in your own PowerShell session, to see what else you can do.  Particularly inside the DBATools these Get-Command outputs show many things are available. For example, you can start a SQL Agent job, start a trace, start an extended events session, you can stop an endpoint or process as well as do several other things. By the way, there is no Stop-DbaAgentJob because you can actually do that from the Start-DbaAgentJob cmdlet in DBATools using a switch.

So, now that you know the types of commands available how are you going to find out what they do? Remember your friend Get-Help or its alias Help?

Help Get-DBAService -Full
Help Restart-DbaService -Full

The output of these commands is too much to put into the post. So, I’ll just point out a few things about each one.

For Get-DBAService notice that you can pass it a computer name value to get the services for all SQL Server instances installed on a given computer.  This will be useful if you know your environment tends to do what’s called “instance stacking.” This is were 2 or more SQL instances are installed on the same computer. If this is done in your environment, you will want to know that information before you start sending commands to restart SQL Server services or you could end up restarting more SQL Server instances than you planned on. That sounds like unplanned down time and you want to avoid that!

<#Returns all SQL Server instances on a given computer. Default columns 
show things like the computerhname, instancename, service name and whether
the current status of the service.
#>
Get-DBAService -ComputerName MyComputerNameHere

Setting Max Server Memory With PowerShell

We’re going to look at Set-DBAMaxMemory from the DBATools module. If you would like more information about SQL Server memory settings. I have a blog post that describes the topic in more detail.

Here is what the DBATools website has to say about this cmdlet in their documentation.

“Inspired by Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this uses a formula to determine the default optimum RAM to use, then sets the SQL max value to that number.”

Here are some examples from the documentation from DBATools and from the Help commands available in PowerShell.

<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory where it is set to something larger than the total amount of RAM assigned to the server.
#>
Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Where-Object { $_.MaxValue -gt $_.Total } | Set-DbaMaxMemory

<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory to this accepted formula created by a SQL Server expert.
#>

Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Set-DbaMaxMemory

<#
If you don't have a registered server then just use the below
#>

Test-DbaMaxMemory -SQLinstance SQLServerInstanceNameHere | Set-DbaMaxMemory

Measuring Free Space with PowerShell

DBAs should have some way of knowing and tracking free space in the database files and for the drives of the SQL Server machines. The DBATools module has two commands for doing just that. Check out the below examples and be sure to use the Help cmdlet on these to look at other examples.

<#Gets just certain parts of the output of get-dbaDbspace. Can add | Out-File C:\DBATools\SpaceOutput.txt 
to the end of this to output this information to a file for review.
#>
Get-DbaDbSpace -SqlInstance SQLinstanceNameHere | SELECT Database, FileName, UsedSpace, FreeSpace, PercentUsed, AutoGrowth

#Resturns drive letters, total space and free space on drives on a computer.
Get-dbadiskspace -ComputerName MyComputerNameHere

Detecting IO Latency With PowerShell

One of the things that DBAs want to check for is IO performance of the storage as seen by SQL Server.  This is often done using sys.dm_io_virtual_file_stats. You can view this information in the DBATools cmdlet Get-DbaIoLatency.

The DBATools output from the Help command says that the output of this commands is based on two articles by Paul Randal of SQLSkills.com. Those articles are listed below. If you don’t know who Paul Randal is, you need to find out. Your career with SQL Server will be greatly enhanced by reading his stuff.

How to examine IO subsystem latencies from within SQL Server

Capturing IO latencies for a period of time

I also have a post on finding queries experiencing waits related to SQL Server IO latency.

So here are a couple of examples of how to run this PowerShell cmdlet.

#Outputs the IO latency information of two different SQL Servers

Get-DbaIoLatency -SqlInstance SQLServerInstance1, SQLServerInstance2

#Outputs the IO latency to a GridView UI for visual examination
$output = Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-GridView -PassThru

#Writes the output to a file.
Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-File C:\DBATools\IOLatency.txt

 

Next Steps To Take

  1. On a Dev environment, practice using the DBATools commands related to stopping and starting SQL Server. This gives you another option when things go south and you have to restart the SQL instance. If the machine suddenly has issues showing the MMC snap-ins then SQL Server Configuration Manager may not be available. And yes, this has happened to me!
  2. Use the Test-DBAMaxMemory command to see if your SQL Servers are misconfigured in terms of the RAM allocated to them. Once you’ve reviewed the output of the command and are comfortable with it, then use the PowerShell examples in here to make the changes.
  3. Consider how you can leverage the cmdlets about diskspace, database space and IO latency to examine your environment. Write some test scripts and try them out.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

Introduction To PowerShell For The DBA Part 2

In Part 1 of this series we began to look at how you can leverage PowerShell as a DBA and there was a brief discussion about automation. Today you will experiment with the DBATools command Copy-DbaLogin and learn about Out-Gridview and the -whatif switch.

Let’s suppose you have one, or more logins you need to get from one SQL instance to another.  Perhaps you are setting up a new test environment for SQL Server 2019 and you need  to move this login to that environment. Maybe this is one node of a SQL Server AlawaysOn Availability Group and you need to get this login, with its SID, on each node of the AG. Below you see I have a screenshot of a login called mysqltestlogin. It’s on a SQL Server 2017 instance.

 

How would we copy this with PowerShell? First, let’s use the Get-Help command to see what commands are available related to logins.

Get-Command *login* -module DBATools, sqlserver

From that output you see a command called Copy-DBAlogin in the DBATools module.

So now run the first command to see the syntax and run the second one to see all the help data, including some examples of how the command works:

Help Copy-DBALogin

Help Copy-DBALogin -Full

The most common way you’re going to use this command is with a Source and Destination SQL Server name and one or more login names. See the example below.

These logins can be SQL logins or Active Directory Logins. One thing to note about this command is that the SID is copied with the login as well as the associated permissions. If you need to, you can use the -Force switch to re-copy an existing login. You might need to do this to ensure that logins, SIDs and permissions are the same between all nodes involved in a group of servers. Those SQL Servers could stand alone instances that are all servicing a particular application or they could be nodes in a Failover Cluster Instance or an Availability Group.

Copy-DBALogin -Source MySourceSQLServerIntanceName -Destination MyDestinationSQLServerInstanceName -Login Login1, Login2, Login3

Maybe you want to copy all the logins except a certain small list. In that case, use the -ExcludeLogin parameter and pass in the logins to omit from the copy operation.

For my scenario from above, my command looks like this.

Copy-DbaLogin -Source MyPCName\Kronos2017 -Destination MyPCName\Romulus2014 -Login mytestsqllogin

And the output is this.

DBATools Copy-DBALogin Output

 

What Is Out-GridView?

You can also use a cmdlet called Out-GridView to pass the objects of the pipeline into a GUI where you can then select the items you want to have PowerShell do the action against.

Get-DBALogin -Sqlinstance MySourceSQLInstance | Out-GridView -PassThru | Copy-DBALogin -Destination MyDestinationSQLInstance

Here are the results of that Out-GridView. Click to enlarge.

Out-GridView Example

To use this for selecting only the rows of output that you want to do something with, left click the row you want to actually copy and then click the OK button in the bottom right. That will copy the selected row. You can use Shift + Click or Control + Click to do a multi-select. There is a Filter option at the top of the Out-GridView screen that also allows you to trim down the results you want to copy.

Of course, using Out-GridView re-introduces the human element to the process and so isn’t great when the goal is to completely automate a process. However, it is great if you want to see the output before you do something you don’t want to do. As a consequence, if you see results in Out-GridView that don’t match what you expect, then you can simply click on Cancel in the lower right hand corner and the PowerShell will do nothing.

Using the -WhatIf Switch In PowerShell

This brings me to another switch that helps in this scenario where you want to see what a command is going to do before you actually executing it. There is a switch called -WhatIf that will stream output showing you what a command would do if it were actually executed. This is a great tool for testing your commands. Often there will be errors or other unexpected results from a command. Using -WhatIf is a great way to catch this before you actually execute the command for real.

So far in this article, you have reviewed what you can do for a very specific object, logins. Let me suggest that you also look at what can be done at the wider scope of the SQL Server level by running the below. You may remember from the previous post that GCM is an alias for the Get-Command cmdlet.

GCM *instance* -Module DBATools, SQLServer

Here is my output for the above. Click to enlarge.

Get-Command Instance :evel Commands

As you see, there are a lot of things you can do with commands related to the SQL Server Instance.

If you want to consider automating the scripting out of your SQL Server instance objects without buying software to do it, then check out the Help for Export-DBAInstance, which is one of the commands that is in the above list.

Help Export-DbaInstance -Full

 

Next Steps To Take

  1. Try out Copy-DBALogin in a test environment. Use it with the -WhatIf switch, or pipe it to a file by using the Out-File command to see what the command does when it runs.
  2. Experiment with various commands and Out-GridView in your test SQL Server environment.
  3. Don’t have a test SQL Server environment? Consider learning what Export-DBAInstance does and using it as part of an “Easy button” for creating a test environment on a fresh install of SQL Server. Or, look up Start-DBAMigration for this task as well.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Introduction To PowerShell for the DBA Part 1

Automating Tasks With PowerShell

What we’re really talking about in this series is automation, specifically by using PowerShell. Now, in simple terms, automation is about removing the human, manual element in doing tasks. This will produce time savings for you as a DBA in the long run and provide better consistency in your environment than doing things manually. Better consistency means reduced or easier troubleshooting because your SQL Server environment will be more consistent with automation. All of this will make your SQL Servers easier to manage.

Imagine being able to migrate an entire SQL Server instance with a single command! You can do this with PowerShell. You want to copy logins from one server to another without installing sp_help_revlogin and  working through the scripting process involved? You can do that with PowerShell.  You want to copy DatabaseMail settings from one server to other servers without writing out all the T-SQL? You can do that with PowerShell!

With PowerShell you can get information faster than from a GUI, especially when that task needs to be done on multiple computers. These tasks include things like like search event logs, search the SQL Server Error Log,  get the status of services, see sp_configure information and more.

You can also do things like copy SQL Server jobs from one server to another or export the results of sp_configure to a file. When doing an action like backing up a database, PowerShell also enables you to output the actions of the script itself and thereby create a self-documenting log that you can look at afterward to see exactly what the script did.  With a module called DBATools, you can migrate an entire instance to a new SQL Server with a single, short command.

Getting Set Up for Using PowerShell

PowerShell is already installed on Windows. Different OS versions have different versions of PowerShell. There is a process to use for updating your PowerShell version. I’ll not be showing that process in this series though.

Also, in order to follow along in the series. You will need to get the DBATools module for PowerShell. Instructions for doing that are located at DBATools.io. The simple answer is to open PowerShell as an Administrator and run the below command. There are some things to consider and so that’s why the link to DBATools instructions on installation.

Install-module DBAtools

 

You will want the SQLServer module in order for some of these commands to work properly, and you may want to explore the more “native” way to use PowerShell with SQL Server, without the aid of the DBATools module. I think DBATools simplifies things so immensely that it is indispensable. However, you may be curious about what is in the SQLServer module for PowerShell. If so, you can add the module to your PowerShell by opening  PowerShell as an administrator and running the following.

Install-Module -Name SqlServer

 

Once you have DBATools and the SQLServer module installed, you are ready to get started.

Finding Available PowerShell Commands

Get-Command: This cmdlet enables you to explore what is available to do in PowerShell.  For instance, you might run the below to see what commands are available in PowerShell related to backups. Notice you can use asterisks as wildcards as well as specify a PowerShell module to look in for commands. If you don’t have the DBATools module, I urge you to get it installed and start using it, if for no other reason than that you will need it if you’re going to follow along with the examples!

Get-Command *Backup* -Module DBATools, SqlServer

The above command will output a long list of things.  You can backup computer certificates, database certificates, find information about backup history and throughput, as well as a lot of other things beyond simply using PowerShell to make a database backup.

Maybe you’re interested in what is available for Logins or Database Mail. If so, try the below commands.  Notice that the second command says “gcm”.  What is that? Well, that is a shortened version, or alias, for Get-Command.  Most, maybe all commands, have an alias you can use.  Aliases provide a shorter way to write a command, but when someone else is looking at your code, then they have to know what your alias is for. Otherwise it will be harder to figure out what you’re code is doing.

Get-Command *Login*  -Module DBATools, SqlServer

gcm *mail*

Getting Help with PowerShell Commands

From the above example where you are looking for commands containing the word “Login”, you should see something similar to the below if you have DBATools and the SQLServer module installed.

 

Let’s start simple and review Get-DbaLogin.  In your PowerShell window run the command  below.

Get-Help Get-DBALogin -Detailed

From this code you will get an output that provides a summary of what the cmdlet does, the syntax options it can be used with and a text description of what those syntax options do.

Let’s say that you’re interested in finding information about the Logins on your SQL Servers. If you just pass this command to PowerShell you will get a list of all the logins on the SQL instance with some properties. Of course, you will want to substitute your own valid SQL Server instance name.

Get-DbaLogin -SqlInstance MySQLInstanceName

The output will list the computer name, SQL Server instance name, name of the login, the login type, create date, the last login timestamp and some other useful properties.

Let’s suppose we only cared about the logins that weren’t system logins so we want to exclude logins like NT Service\SQLWriter and the ones whose name starts with ##. How do we do that? Well, look at the help output again and notice the -ExcludeFilter option. Add that to the earlier command, like so:

Get-DbaLogin -SqlInstance MYSQLServerInsstane -ExcludeFilter '##*', 'NT*'

PowerShell Piping

Now let’s suppose that you are only interested in a couple of the properties that are output from this command.  You’re interested in the login name and the last time that login accessed this SQL Server.

To get only those properties we can do what is called “piping”.  This is where the “|” symbol is used to move PowerShell output from the left over to the right side of the script objects.  This is often done for filtering or to pass along PowerShell output for further processing by other commands.  PowerShell output looks like text, but it’s really .Net objects.

In the first example below, you’re passing all the objects left from Get-DbaLogin when the ones with ‘##*’ or ‘NT*’ are eliminated and then telling PowerShell to display only the Name and LastLogin objects.

In the second example, you will be sending the output of the command to a file.

Get-DbaLogin -SqlInstance MySQLServerInstance -ExcludeFilter '##*', 'NT*' | SELECT Name, LastLogin

Get-DbaLogin -SqlInstance Skolarlee-PC\KRONOS2017 -ExcludeFilter '##*', 'NT*' | SELECT Name, LastLogin | Out-File 'C:\DBATools\LoginsOutput.txt'

On my local PC this returns the sa account and my local Windows account along with the last time these accounts accessed the SQL Server. This sort of information is useful, probably in a number of scenarios, but let’s suppose that you’re doing an audit of SQL Server logins because you suspect you have a lot of left over, unused logins.  You might want to examine them to see if you can drop users and logins that are no longer being used.  This sort of thing makes your SQL Server easier to manage because you don’t have unused Logins and database users cluttering up your SSMS when you’re connected to a SQL Server.

Let’s suppose you don’t want or need to do something with this information right now, but you want to review it later, or provide it to someone else for review. What can you do? The simplest thing is to use the second command above to send the PowerShell objects to a text file by using the piping technique I mentioned earlier.  The directory in the Out-File command needs to exist already but the command will create the text file for you.  This information can be written to a share for your team to access later.  You can also do things like email this output to the team using a PowerShell command for email. If you run GCM *mail* in PowerShell, you will find a command that lets you do this.

How To Script Out SQL Server Logins With PowerShell

Another powerful thing that can be done with a one line PowerShell script is scripting out your logins and users.  This cmdlet from the DBATools module will create a script of your logins at the server level and their corresponding database users and all associated permissions. Look at the help for this command.

#Start by getting the help for the command
Help Export-DbaLogin -Detailed

Now that you have reviewed the help content, let’s try something.

Export-Dbalogin -SqlInstance MySQLServerInstanceNameHere -Path 'C:\DBATools\'

Now go to the C:\DBATools\ directory and double click the .sql file.  SQL Server Management Studio will launch and prompt you to connect to a SQL instance. Once you do that, the script will load. Review the output and take a look at the Help output from PowerShell to see what else you can do with this.  This command gives a DBA a great way to script out login and user information, and this can be helpful for migrations or for providing a history of changes to logins, users, and their permissions.

Next Steps To Take

  1. Explore more commands and the possibilities that they offer. Here is a place to start.
GCM *export* -Module DBATools

Help Export-DbaInstance -Detailed

Export-DbaInstance -SqlInstance MySQLServerInsanceNameHere -Path C:\DBATools\

2. Find a book or video series on PowerShell and consume that material.

3. Read some of the documentation and blogs over at dbatools.io .

4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

3 Ways to Restore SQL Server Databases

This is likely the last in a series of three posts about backups and restores.  My aim with this series has been to give you an idea of the options available and a basic start on using those options. I’m not trying to go in-depth on the subject of backups and restores.

Today will be about getting you going on the concept of restoring databases in SQL Server.  I’m using a local install of SQL Server 2017 on my desktop PC to demonstrate these techniques.

Obtaining the StackOverflow Database

Before I dive into demonstrating the restore options, I want to talk a little bit about the set up. I’m using the StackOverflow2010 database.  Anonymized data dumps of the StackOverflow database are available here. This particular version of the database I’m using came from a page on Brent Ozar’s site.

I then used the below PowerShell code to take backups.  Between the Full and Diff backup I added one user to the Users table.  Between the Diff and Log backup I add two more users.

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -Type Full -FilePath dbname-backuptype-timestamp.bak -ReplaceInName -CompressBackup -Verify

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\' -Type DIFF -FilePath dbname-backuptype-timestamp.bak -ReplaceInName -CompressBackup -Verify

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -Type Log -FilePath dbname-backuptype-timestamp.trn -ReplaceInName -CompressBackup -Verify

Below is the T-SQL I used to insert some rows.  That value for Age though!  Ah, to be 25 again!

 

INSERT INTO dbo.Users ([AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
VALUES
('I make SQL Server more stable, highly available and easier to manage', 25, GETDATE()-7, 'Lee Markum', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, 'LeeMarkum.com', NULL)

--Took a differential backup here so I would have some changes to restore

INSERT INTO dbo.Users ([AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
VALUES
('I make T-SQL solutions that just work', 25, GETDATE()-7, 'T-SQLGuru', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, NULL, NULL),
('I make SSIS solutions that just work', 25, GETDATE()-7, 'SSISGuru', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, NULL, NULL)

--Took a log backup here

Using SSMS to Restore a Database

Just like for creating a backup, SQL Server Management Studio has menu options for restoring a database. Open your copy of SQL Server Management Studio and connect to a non-production or lab environment of some kind. You’re going to right click the database you want to restore and use the fly out menu to navigate to Tasks > Restore > Database.

 

Selecting the Database option from the fly-out menu above brings you to the below page. Notice in the following screen that SQL Server finds the applicable backup files for you. No in-depth knowledge of restore operations is needed in this case because the work is done for you. It has found the full, differential and log backups that I took earlier.  Each row represents a backup file.

If you only want to restore to the time represented by the differential backup, then uncheck the box next to the log backup.  SQL Server will then restore the full and differential backup, but not the log backup. You might want to do something like this if you know that the data in the database was corrupted or changed in an unexpected or undesired way immediately after the differential backup.  As a result, you restore the database to the point in time represented by the differential in order to restore the database to its last known good condition.

In the Destination section, SSMS fills in the name of the database that you selected earlier for restoring.  However, if you give the database a different name here, then you can restore the database and compare the restored database to the current database.  You might want to do this in order to attempt to repair data in the current database by getting data from the restore ddatabase. Please note the yellow banner that informs you that this process will take a tail log backup.  This is a preventative measure.  It captures the last portion of the transaction log called the tail of the log.  It allows for complete recovery.  Without this backup, if you restore this database, you could lose transactions.

 

One other option I want to point out on this screen is what the “Timeline” button is for. When clicked, you’ll arrive at a screen like the below.  This allows you to choose a point in time to restore to based on the available backups.  This point in time restore is only available for databases in the full and bulk-logged recovery model. For bulk-logged recovery model, certain rules apply as to when this option would be available.

If you select the radio button next to “Specific date and time” then you can either specify a certain date and time in the Date and Time boxes or you can also use the slider icon along the bottom.  There is also a Time Interval drop down that controls what you see in the colored timeline above the slider icon. Using this option is the same as using the WITH STOPAT command using T-SQL for the restore. For my demo, I have not used this option, but I want you to be aware that it is available. This Timeline feature is new in SQL Server 2012.

 

Once you have the General page looking the way you need it, click on the Files selection on the left to bring you to the page below. Here you have one option to work with and that is whether you want to relocate the database files to another folder. Checking this option allows you not only to relocate the files, but rename them as well.  You might want to do that if you’re restoring the database to a different name so that you don’t have two databases with different names, but the same file names. That might make it more difficult later to figure out which files belong to which database without running a query to figure it out.

Should You Use the WITH REPLACE Option?

Now click on the Options selection on the left.  You arrive at the below page. The option “overwrite the existing database (WITH REPLACE)” will bypass certain checks that would normally occur during the restore process.  As a result, you really would rarely use this option, if ever. This option will do what it sounds like and replace the target database with the database in the backup file.  If you mistakenly point this restore to database on a different server that isn’t the same database that is in the backup and use this WITH REPLACE option, then you will overwrite the other database on the other server. There is a large section in this page about caution using the WITH REPLACE option.

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017

Here is where the option for taking the Tail-log backup is located.  You should leave this checked and note the location for where that backup will be written.  If it isn’t the location you would like, change it in the Backup file box.

There is also an important option under “Server connections.” If you leave this unchecked and there are current connections to the database then the restore will fail.  A restore operation needs exclusive access to the database in order to perform a restore so other connections must be terminated first.  When this option is selected, SSMS will take care of that for you.

Once all the options you want are filled in, I would encourage you to click the Script option at the top so you can obtain the T-SQL equivalent for what SSMS is going to do. You can either click OK in the lower right of SSMS to execute the restore, or click Cancel and go to the T-SQL to review what will be done and then execute the T-SQL.

 

Now let’s turn our attention to using T-SQL for restoring a database.

Using T-SQL to Restore a Database

Below is the generated T-SQL for restoring the StackOverflow2010 database based on the available backup chain on my local SQL Server. I have added comments so you can better understand what each part is doing.  T-SQL syntax for performing a restore is, in most scenarios, not too difficult.  I am not showing things like file and filegroup restores or piecemeal restores.  These are more advanced scenarios.

USE [master]
/*This is the first part of the T-SQL generated when the "close existing connections" option is chosen in the GUI. This gives the session doing the restore the exclusive access needed.
*/
ALTER DATABASE [StackOverflow2010] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--This is the tail-log backup
BACKUP LOG [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010_LogBackup_2020-10-04_10-23-33.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2010_LogBackup_2020-10-04_10-23-33', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

/*Here is the full backup.  Database restores need a place to start from and the full backup is that place.
*/
RESTORE DATABASE [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Full-202010030919.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

--Here is the differential backup
RESTORE DATABASE [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Differential-202010030938.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

--Her are the two log backups to restore
RESTORE LOG [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Log-202010030947.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010_LogBackup_2020-10-04_09-20-23.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

/*This is the second part of the T-SQL generated when the "close existing connections" option is chosen in the GUI. This puts the database back to multi_user so it is ready to be used*/
ALTER DATABASE [StackOverflow2010] SET MULTI_USER

GO

 

Now let’s take a look at doing this with PowerShell

Using PowerShell to Restore a SQL Server Database

There are commands in the PowerShell on your local PC or server that will perform restores of SQL Server backups.  However, we will be discussing the PowerShell module called DBATools, which has to be installed before it can be used.

Please go to https://dbatools.io/download/ to see how to obtain and set up the module for use with this part of the blog post. I won’t be covering that here as the material is well documented on their site.

Once you have that, open PowerShell as an Administrator.  Type in the below code and hit F5.

Help Restore-dbadatabase -Detailed

This will give you a full description of the parameters and switches available in this command.  Numerous example code snippets will also be shown. From that help information I wrote the two examples that follow.  The first example will not perform the restore but instead writes to a .sql all the T-SQL needed to perform the command.  Strictly speaking, the -DatabaseName and value aren’t needed because the only backup files in that directory are for the StackOverflow2010 database. This option is useful so you can see what the command is going to do.

In the second example, the PowerShell will actually do the restore and then write it’s actions out to a text file. This is useful for having a history of what the command did.

In the third example, this will  use the folder structure from Ola Hallengren’s world famous maintenance solution. Notice the following switch:

-MaintenanceSolutionBackup

There are also switches for maxtransfersize, buffercount, and blocksize.  These can all be experimented with for your databases to find an optimal combination that restores the database as fast as possible. Additionally, there is a -RestoreTime option that is the equivalent of the WITH STOPAT syntax in T-SQL and the TimeLine function in the SSMS GUI.

<#This will not perform the restore, but will instead create a .sql file of the necessary T-SQL to do the requested restore.
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -DatabaseName StackOverflow2010 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -WithReplace -OutputScriptOnly | Out-File 'C:\DBAToolsRestoreScripts\RestoreStackOverFlow2010.sql' 

<#This will do the restore and create a text file showing what the PowerShell command actually did
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -DatabaseName StackOverflow2010 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -WithReplace | Out-File 'C:\DBAToolsRestoreScripts\RestoreActionsStackOverFlow2010.txt' 

<# This will do the restore based on Ola Hallengren's directory structure from his world famous maintenance solution.  Notice the switch -MaintenanceSolutionBackup
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -MaintenanceSolutionBackup -WithReplace -OutputScriptOnly | Out-File 'C:\DBAToolsRestoreScripts\RestoreStackOverFlow2010.sql' 

Next Steps to Take

  1. Download and install DBATools.  It’s useful for a lot more than handling SQL Server restore scenarios!
  2. If you’re not familiar with PowerShell, you need to learn it, if for no other reason than the options that DBATools provides you for working with SQL Server.
  3. Connect to a test server and try out each method. Get some experience with these methods so you know which one you like the most and so you’re not trying to figure out how you want to perform a restore when you actually have to do it for real.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

5 Ways to Make SQL Server Backups

Last time we discussed 5 backup types for SQL Server.  Now I want to introduce you to five ways to make backups in SQL Server. I can’t possibly explore all the options available with each of these six methods. There is a lot to cover for even a “simple” topic like backups.

Method 1: Using the SSMS GUI to Make a SQL Server Back up

 

For those readers who like the SSMS GUI, there is good news.  SQL Server Management Studio offers a fairly straightforward method for backing up a database. Once you connect to the SQL Server instance containing the database you want to back up, left click on the “+” sign next to the Databases folder.  Then right click the name of the database you want to backup, choose Tasks, then choose the Backup option in the fly out menu.

That brings you to the General Backup Menu page in SSMS.  In this menu you can access a number of settings related to the backup that will be generated.

With the “Backup type” drop down you can control whether this will take a Full, Differential or Log backup.

The “Backup component” section can be adjusted to take either a backup of files and filegroups or the default selection of “Database.”

In the Destination section you can backup to the default of “Disk” or choose “URL” from the drop down to make a backup to Azure as the target. When the destination of Disk is left you will be supplied with the destination of the backup and a filename for the backup.  This directory will be the default backup directory selected during the SQL Server installation.  If this is not where you want the backup, simply click “Remove” then click “Add” to navigate to the location you want to use.  The “Add” menu does accept share paths.

The Media Options selection on the “Select a Page” allows you to choose options for things like whether you want to append this backup to the same media set or start over with a new one.

The media options seem to me to be from an earlier day when backups were written to physical tapes.  Those tapes then had to be rotated from time to time.  It’s not a best practice to append backups to the media set because you’re adding backup files into a single media set.  If something happens to the media set and it is unusable, then the backups are not accessible.

In the Reliability section, you should check the options for “Verify backup when finished” and “Perform checksum before writing to media.” Thes options will cause your backup to take longer to complete, but they do help with validating the integrity of the backup at the time it was written.

The Backup Options are in the “Select a page” menu has what I think is one very important feature to note.

On this page there is an option related to backup compression.  Back in older versions of SQL Server, like 2005 and 2008 this was an Enterprise Edition only feature.  As of SQL Server 2008R2 it is available in Standard Edition.  To make using this compression the default for all of your backups, simply run the code below on your SQL Server.  Then, when you get to this option in the SSMS GUI, just leave it set to “Use the default server setting.”  You’ll want the space savings that compression offers.  Why use more space on your separate storage for backups than is necessary? I mean, you are storing your backups somewhere other than on the SQL Server, right?!

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Once you work through these meu options, simply click “Ok” and SQL Server will make the backup for you.  You can also click on the “Script” option at the top middle of the wizard to have SQL Server show you the T-SQL it is about to run.  You could save this as an example script to review later.

Method 2: Using T-SQL To Make a SQL Server Backup

T-SQL is a tried and true method for backing up databases.  You have more options available when backing up with T-SQL versus the GUI.  Most of those options are going to be more advanced options. A very basic example of the backup command that produces a full SQL Server backup is below. That is followed by examples for a differential backup and a log backup.

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Full.bak'

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Differntial.bak' WITH DIFFERENTIAL

BACKUP LOG [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_log.trn'

Buffer Count and maxtransfersize are two options worth noting. You can experiment with these T-SQL options to take backups faster. The buffer count value conrols how many I/O buffers are used for processing the backup and the maxtransfersize controls how much data is moved at any one time.

Below I’ve provided three examples from my testing on my home lab PC. The initial buffercount and maxtransfersize data was captured by turning on trace flags 3605 and 3213 then looking in the error log after the first backup was taken.  After that I simply experimented with values.  Be aware that increasing the buffer count too much can cause an out of memory error.

As you can see, the initial throughput was 219.412 mb/sec and the elapsed time for that part was 39 seconds. This was using SQL Server defaults.

Increasing the buffer count to 8 increased the throughput to 258.653 mb/sec and the elapsed time dropped about 6 seconds. Combining the second change with a maxtransfersize of 4MB increased the throughput to 270.095 and pushed the time down another 1.4 seconds. I shaved 8 seconds off the backup time.  This was for a small database of about 14 GB.  For larger databases the increased throughput could represent a significant time savings.

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
/*
buffercount = 4 and maxtransfersize = 1024kb
no options
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 39.041 seconds (219.412 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8
/*
Buffer Count = 8
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 33.118 seconds (258.653 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8, maxtransfersize = 4194304

/*
Buffercount = 8 and maxtransfersize = 4MB
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 31.715 seconds (270.095 MB/sec).
The backup set on file 1 is valid.
*/

 

Method 3: Using Powershell to Make a Backup

 

If you’re not using PowerShell with your SQL Servers, you should be.  If you’re not using the DBATools module with your SQL Servers, get it now. PowerShell can do fantastic, wonderful things and DBATools can do powerful, amazing things for you with respect to all things SQL Server. Below is a simple example of using the DBATools command Backup-DbaDatabase to make a full backup.  The command has a full range of options available, including backing up all the databases on a SQL Server when you don’t pass the -Database parameter. You should check into it right away.

Backup-DbaDatabase -SqlInstance MySQLServer -Database Stackoverflow2010 -path 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup'

 

Method 4: Using Maintenance Plans to Make a Backup

 

Because this post is already running long and there are a lot of other places that show how to use maintenance plans, I’m not going to step through how to do this here. You can go here and here to see how to use maintenance plans.  Also Jonathan Kehayias has a Pluralsight course on using them.

I am going to give you some quick thoughts on using maintenance plans though. First, Maintenance Plans are another GUI method to setting up backups.  In that respect, they are an easy point and click method to handle taking backups and handling backup retention, which we haven’t even talked about. Secondly, because of the nature of this method, which allows you to pick Backups as an option for a plan and then steps you through each part of a workflow wizard, Maintenance Plans can be a common approach for IT professionals coming from the SysAdmin side of things. No need to know or understand the options presented in the SSMS Backup wizard, for example.

Some downsides exist in backups inside maintenance plans prior to SQL Server 2016. Namely, the below options don’t exist.

– Perform checksum
– Continue on error
– Block size
– Max transfer size

Method 5: Using Ola Hallengren’s Maintenance Solution to Make A Backup

 

You can get help with Ola’s scripts here. Ola’s scripts are known and used world wide by DBAs.  They just work.  When you go to his page, you’ll download the file maintenancesolution.sql.  That file contains not only the backup routine but routines for CHECKDB and index maintenance.  You can download each components scripts individually as well. On his support page for the backup script, he explains all the available options and provides a laundry list of real world examples for how to set up the scripts for various scenarios.

Ola has options for the buffer count and maxtransfersize I demonstrated earlier.  He has options for using his scripts with AlwaysOn Availability Groups and he has options for backup retention. His scripts even work with third party vendor applications like LiteSpeed and Red Gate SQL Backup Pro.

What to Do Next:

  1. Review your current backup strategy and look for servers that aren’t being backed up at all.
  2. If you are a SysAdmin or an “Accidental DBA”, look at those links about setting up maintenance plans and get busy backing up your servers.
  3. If you didn’t know about DBATools, then stop what you’re doing and go to their web page.