Why is trn differential backup only successfuly executing first time?

Cataster 641 Reputation points
2021-08-21T17:56:44.2+00:00

Im implementing automated log shipping solution, and came accross differential backups. Im interested in this feature, so I tried appending -Incremental switch to the .bak backup command, but that resulted in error:

Cannot perform a differential backup for database "MainDB", because a current
database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option

So instead, I appended Incremental swicth to the transaction log backup command and that worked the FIRST time/run only. Since we plan to run the transaction log script every 5-10 mins, I tested it again and it failed with following error:

Restore-SqlDatabase : System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to
rollforward.

How do i resolve this issue? Am I not implementing differential log shipping logic correctly?

Heres my code:

 #First, create a 1-time full backup of the the primary instance DB that we are interested in shipping over to the secondary server
 Get-SqlDatabase -ServerInstance $PrimaryServerInstance -Database $DatabaseName | Backup-SqlDatabase -BackupFile "$BackupFilePath\$DatabaseName.bak"

 #Restore the full .bak 1-time
 Restore-SqlDatabase -ServerInstance $SecondaryServerInstance -Database $DatabaseName -BackupFile "$BackupFilePath\$DatabaseName.bak" -AutoRelocateFile -NoRecovery -PassThru

 #Then, create a backup of the the primary instance DB transaction log that we are interested in shipping over to the secondary server
 Get-SqlDatabase -ServerInstance $PrimaryServerInstance -Database $DatabaseName | Backup-SqlDatabase -BackupFile "$BackupFilePath\$DatabaseName.trn" -Incremental -BackupAction Log

 #Next, restore the transaction log for the DB on the secondary server/instance (scheduled at some point)
 Restore-SqlDatabase -ServerInstance $SecondaryServerInstance -Database $DatabaseName -BackupFile "$BackupFilePath\$DatabaseName.trn" -RestoreAction Log

Note: The transaction log code is in a separate script, because we should only backup the database full and restore it to secondary instance just 1 time.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,320 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-08-21T20:52:26.767+00:00

    As I answered in your other thread, it is difficult to see the space for differential backups in a log-shipping scheme.


0 additional answers

Sort by: Most helpful