question

Cataster-7485 avatar image
0 Votes"
Cataster-7485 asked ErlandSommarskog commented

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.

sql-server-generalwindows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog
I understand now, thank you! So just to confirm, there's no point/compelling reason to take differential log backups as it will contain the log transactions changes only, right?

Do you recommend the transaction log (.trn) script we would be scheduling to run every 5-10 mins, that I create timestamped .trn files? Ive noticed that the Backup-sqldatabase command just appends to the existing file if the name is the same. Is that normal/fine to restore from? Would it restore the entire file or is the restore command smart enough to seek whats in the file, and only restore the latest changes it detects?

0 Votes 0 ·

Having one file per backup is very much to recommend. There are not very many situations where having multiple backups in the same file is useful, and log shipping is certainly not one of them. For one, how do you know which file to apply? It is also a source for confusion, so avoid that.

0 Votes 0 ·

@ErlandSommarskog so in other words, I should timestamp each backup taken at interval right?

0 Votes 0 ·
Show more comments