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.