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.

 

Steps to Catch Up Log Shipping Secondary

 

If you are using Log Shipping, there have probably been times that you have discovered that the Log Shipping Secondary is not up to date.  Perhaps you found out by right clicking on your SQL Server instance and running the performance dashboard report for Log Shipping Status.  If you are not familiar with this report check here

Perhaps there is a Reporting team that uses that secondary and they met you at your desk upon arrival, informing you that the database is not online so they can’t work yet. So what do you do when you realize that the secondary is so far behind that it is probably not going to catch up on its own?  This process assumes that you have a Log Shipping config set up to run a Log Shipping Restore job just prior to the beginning of the business day and that job leaves the database in Standby mode so it can be read from.  At the end of the business day the log shipping restore job has one or more schedules set to  kick off restores for the transaction logs during the business day.

Here is a process you can use. 

1.       Disable the Copy Job on the secondary that is behind. 

2.       View the previously mentioned Log Shipping Status report and determine which file is the next in line to be restored. 

3.       Create a folder in the path where the transaction log backups are being copied to and perform a Cut operation on all the files just after the current file being restored. 

4.       Paste those files into the folder you made in step 3. 

Here is what you have accomplished with this.  You have set up the database to go to Read Only/Standby once the current file is done restoring.  It can’t continue the restore process because it has reached the last transaction log file in the original folder path. If the Reporting team can live with the data as is, then they can get to work while you set up the next part that will allow you to catch it up.  If it still is not usable for the Reporting team because the data has to be more current, then you have still helped yourself because you have to get the database out of recovery mode so you can do the next part to catch it up.

5.       Take a differential backup of the current database. 

6.       While that is running, open SQL Server Management Studio to set up your script to restore the most recent full backup and the differential you are making.  Remember to consider that you may have to use the WITH MOVE part of the RESTORE database syntax because the location of your files on the secondary may not be the same as on your primary.  Also, if your Log Shipping config is designed to leave the database in Standby then you will need to use the WITH STANDBY clause and then name the .tuf file where the redo information is kept.

7.       If the Reporting team started querying after step 3 you will need to ask them to disconnect because you can’t restore a database while other sessions are connected.  Alternatively you could use ALTER MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE at the beginning of your RESTORE script but that would be inconsiderate.

8.       Once the secondary is done restoring the most recent transaction log it was on, start the restore scripts you built in step 6. Once this step is done, you can inform the Reporting team that the database is ready to be queried.

9.       Cut and paste the files from steps 3 and 4 so they are back in the target folder for the copy job. 

10.   Re-enable the Copy job. Once step 8 is done, you can inform the Reporting team that the database is ready to be queried. 

You Really Should Stay Current on Cumulative Updates

 

I have encountered a Log Shipping error that I want to detail as an encouragement for staying current on SQL Server cumulative updates. First, a little background. The environment is SQL Server 2014 Standard edition with a physical machine as the primary and two secondaries, both virtual machines backed by a flash storage system.   SQL Server is on service pack 1, cumulative update 1. Log shipping had been configured in this environment for many months, but SQL Server 2008R2 was updated to SQL Server 2014 some months prior. The Log Shipping Restore job leaves the database in the Standby Mode so it can be queried for reporting purposes.

 

One evening I received the following error via database mail: “SQL Server Alert System: ’Severity 021’ occurred on \\MySecondary. An error occurred during recovery, preventing the database ‘MyDatabase’ from restarting. Diagnose the recovery errors and fix them or restore from a known good backup.” This was immediately followed by another email advising that the Log Shipping restore job had failed. Yet a third email was sent for error 9004 severity 16, explaining that an error occurred processing the log for “MyDatabase.” The message informed that I could restore from backup or rebuild the log. I connected to the SQL instance and saw that the database was in the restoring mode.

 

A quick internet search turned up the following two links.

https://support.microsoft.com/en-us/kb/2987585

https://support.microsoft.com/en-us/kb/2015753

 

The symptoms section of the first link seemed to fit my situation fairly well. I was unfamiliar with the fsutil command line utility. When I executed it against my environment the results indicated that the Bytes per Sector value was 512 and Bytes per Physical Sector returned “Not Supported.” The support article gave me two workarounds for addressing the issue.

  • Move the transaction log file at the destination to a drive that has “Bytes per Physical Sector” set as 512 bytes.Note The Standby file can still be located on the drive that has “Bytes per Physical Sector” set as 4096 bytes.
  • Restore the log backups without using the standby option. Instead of the STANDBY option, use the WITH NORECOVERY option during the restore operation.

 

Since my database was still in the restoring mode and the second URL indicated I had a damaged transaction log, I could not offline the database and relocate the file per the first recommendation. I also could not confirm how the bytes per sector value on the drive was configured anyway. The first article indicates that the issue was fixed in SQL Server 2014 SP1 CU2 and SQL Server 2012 SP2, CU7. Remember, I mentioned that the environment was on CU1 of SQL Server 2014 SP1. Unfortunately, that put me one update behind with respect to being protected from this situation.

 

Looks like I had one good course of action – restore from backup. I disabled the restore job, restored from a recent full backup and the most recent differential. I left the database in the restoring mode so I could re-enable the Log Shipping restore job. This would get the secondary back to Standby mode.

 

If you want to know when the latest updates for SQL Server are available, then I recommend saving the links below in your favorite web browser.  As this post demonstrates, be sure to read what the latest are about as soon as possible to see if they apply in your situation.

 

http://sqlserverbuilds.blogspot.com/

http://sqlserverupdates.com/

http://sqlblog.com/blogs/aaron_bertrand/default.aspx