As I answered in your other thread, it is difficult to see the space for differential backups in a log-shipping scheme.
Why is trn differential backup only successfuly executing first time?
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.