flock of birds flying

Getting Started with Log Replay Service for Azure SQL Managed Instance

Spread the love

Recently, I’ve started on a project where we are migrating a customer to Azure SQL Managed Instance, which now supports a few different migration paths. You can simply backup and restore from a database backup, but you can’t apply a log or differential backup to that database. You can also use the Database Migration Service, but that requires a lot of infrastructure and Azure configuration. The log replay service, or LRS, is the functional equivalent of log shipping to your new managed instance database. While log shipping is a well known methodology for both database migrations or disaster recovery. However, the implementation is a little different–let’s talk about how it works.

flock of birds flying
Photo by Shakhawat Shaon on Pexels.com

First, You Must backup to URL

In order to use the log replay service, you need to take a backup of your database to Azure Blob Storage. Alternatively, you can push on-premises backups using AzCopy or uploading files into the portal or Storage Explorer. But let’s focus on backup to URL. The first thing you need to do is create a credential in SQL Server. While there are other methods of creating a credential, I have had absolutely the best luck, using this PowerShell code which produces a CREATE CREDENTIAL statement to execute on your SQL instance.

CREATE CREDENTIAL [https://storageaccount.blob.core.windows.net/sqlbackups] WITH IDENTITY='Shared Access Signature', SECRET='sv=2020-08-04&si=sql1&sr=c&sig=Dw0uE13l2347%2FCNJgQbm5sHhM9ZtDts9F8SMi5Re1ASUw%3D'

The SAS token you create is created on the container level–you will also need to create a policy (that code from MS will create everything for you including the storage account). Next execute the backup.

EXECUTE dbo.DatabaseBackup@Databases = 'DB1',@URL = 'https://storageaccount.blob.core.windows.net/sqlbackups',@BackupType = 'LOG',@Compress = 'Y',@Checksum = 'Y',@Verify = 'Y',@DirectoryStructure = '{DatabaseName}',@NumberOfFiles = 8,@MinBackupSizeForMultipleFiles = 10240,@logtotable=y

I’m using Ola Hallengren’s code to do my backups, but you can also do this manually.

BACKUP LOG [db1] TO URL = N'https://storageaccount.blob.core.windows.net/sqlbackups/db1/687593983fe8_db1_LOG_20211216_162916.trn' WITH CHECKSUM, COMPRESSION

I highly recommend using Ola’s code to perform these backups and all of your backups. A couple of things you should note–you have to use the checksum option, and with Ola’s backups I’m using the Directory Structure of {DatabaseName}, The reason for this is that LRS does not support nested directories, like Ola’s code would default to, nor does it support back ups stored in the root of the container. (You should note the backup example on the LRS doc page does just this).

Docs also say you need a full, differential, and log backup to do a complete migration, however in my testing I’ve been successful with just a log backup in addition to a full. To complete your restore process, you will need to have the name of your last log backup. You can use this code to generate the powershell code to initiate the migration process.


DECLARE @RGName VARCHAR(256) = 'ResourceGroupName'

DECLARE @MIName VARCHAR(256) = 'ManagedInstanceName'
DECLARE database_cursor CURSOR FOR

SELECT name FROM MASTER.sys.sysdatabases where name not in ('master','msdb','model','tempdb')

create table #migration (command nvarchar(max))

OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DBName

SELECT @lastlogbackup = REVERSE(SUBSTRING(REVERSE(physical_device_name ),1,CHARINDEX('/',REVERSE(physical_device_name )) - 1))FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]WHERE (bs.database_name = @DBName OR @DBName IS NULL) AND bs.type = 'L'ORDER BY bs.backup_start_date DESC;

insert into #migration select ‘Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “'+@RgName+'" -InstanceName "'+@MiName'" ` -Name "'+@dbName+'" `-LastBackupName "'+@lastlogbackup+'" -Collation "SQL_Latin1_General_CP1_CI_AS" -StorageContainerUri "https://storageaccount.blob.core.windows.net/sqlbackups/'+@dbname+'/" -AutoCompleteRestore -StorageContainerSasToken "sp=rl&st=2021-12-14T21:25:07Z&se=2021-12-25T05:25:07Z&spr=https&sv=2020-08-04&sr=c&sig=h8%2FimcTh%2BnlCQ3WodgTShWERT7yA38HfBQFOLwvEoiqw%3D"'
FETCH NEXT FROM database_cursor INTO @DBName


CLOSE database_cursor DEALLOCATE database_cursor

select * from #migration

For a small database, this only takes a few minutes. If your PowerShell execution goes out to lunch, and your database is stuck in restoring, there is probably something wrong with the way your formatted your backup string. This also manifests itself in the form of http 204 error in the log of your managed instance.


Sending the notification action: NotifyRestoreProgress

Date                       12/9/2021 8:09:42 AM

Log                         SQL Server (Current – 12/9/2021 4:27:00 AM)

Source                  spid101


Http code after sending the notification for action NotifyRestoreProgress: 204

What’s annoying is that the PowerShell doesn’t attempt to do any validation of requirements. Hoepfully, this will change in the future because this was a challenging problem to troubleshoot.


3 thoughts on “Getting Started with Log Replay Service for Azure SQL Managed Instance

  1. Pingback: Log Replay for Azure SQL Managed Instance – Curated SQL

  2. Jagath

    Hi jdanton1, Im getting an error where the lastlogbackup variable is not declared, when i declared it like this: DECLARE @lastlogbackup VARCHAR(256); it gives error:
    Msg 537, Level 16, State 3, Line 15
    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Can you help to correct or fix this issue?

  3. Jagath

    Hi Joeydantoni, I was able to fix the issue after declaring the variable and changing the script from
    REVERSE(SUBSTRING(REVERSE(physical_device_name ),1,CHARINDEX(‘/’,REVERSE(physical_device_name )) – 1))

    REVERSE(SUBSTRING(REVERSE(physical_device_name ),1,CHARINDEX(‘\’,REVERSE(physical_device_name )) – 1))

    upon then i was able to get the lastlogbackup filename in the logreplayscript. I was performing a migration in continous mode. So inorder to complete the migration, i need to get the output with below script.
    insert into #migrationcontinousmode select’Complete-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “’+@RgName+'” `
    -InstanceName “‘+@MiName+'” -Name “‘+@dbname+'” `
    -LastBackupName “‘+@lastlogbackup+'” `’

    Thanks a lot for the script, i was able to modify and use as per my requirement. 🙂


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.