1

I'm implementing an automated log shipping solution, and so far I have the following:

<#
#Pre-req#
    a. The primary database needs to be in Full recovery mode

#Primary Steps#
1. Backup: Generate DB backup from primary server/instance
2. Restore: Restore backup DB on secondary server/instance.
3. In order to remain in sync efficiently, ship just the transaction logs from the primary -> secondary instance
#>

Get-SqlDatabase -ServerInstance localhost -Database Testxyz | Backup-SqlDatabase -BackupFile 'C:\Users\...\Documents\DB Log Shipping\Backups\Testxyz.bak'

Restore-SqlDatabase -ServerInstance localhost\MSSQLSERVER01 -Database Testxyz -BackupFile 'C:\Users\...\Documents\DB Log Shipping\Backups\Testxyz.bak' -AutoRelocateFile -PassThru

<#
#Primary Steps#
1. Backup: Generate Transaction Log backup (.trn) from primary server/instance DB
2. Restore: Restore DB .trn backup on secondary server/instance.
#>

Get-SqlDatabase -ServerInstance localhost -Database Testxyz | Backup-SqlDatabase -BackupFile 'C:\Users\...\Documents\DB Log Shipping\Backups\Testxyz.trn' -BackupAction Log

Restore-SqlDatabase -ServerInstance localhost\MSSQLSERVER01 -Database Testxyz -BackupFile 'C:\Users\...\Documents\DB Log Shipping\Backups\Testxyz.trn' -RestoreAction Log

I got an error at the transaction log restore step:

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

I've found out that -NoRecovery is necessary to prevent the error. Otherwise, If the SQL Server is online and also accepts database backup file, then there can be data inconsistency. Thus, the database has to be in the state where it can further accept the backup data and not the online data request.

So I tried:

Get-SqlDatabase -ServerInstance localhost -Database Testxyz | Backup-SqlDatabase -BackupFile 'C:\Users\...\Documents\DB Log Shipping\Backups\Testxyz.trn' -BackupAction Log -NoRecovery

but even that still resulted in error.

I've read about differential backups, in which a differential backup captures only the data that has changed since that full backup.

How do I resolve this issue and implement differential backup logs?

6
  • why is this question flagged for close? It follows the guidelines...
    – Cataster
    Aug 21 at 1:38
  • 1
    @DaleK thanks for clarifying Dale! Also how do you check the reason?
    – Cataster
    Aug 21 at 1:48
  • @DaleK ohhhh I didnt know you can click the number to see details, sweet!
    – Cataster
    Aug 21 at 6:47
  • Why don't you use SQL Server's own config tool for log shipping? Anyway, it's the restore part that needs norecovery.
    – vonPryz
    Aug 21 at 7:15
  • @vonPryz we want to automate it outside the sql server
    – Cataster
    Aug 21 at 14:52
0

Using Incremental switch, differential backup can be created.

Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -Incremental

Note: a full backup of the database has to be created first BEFORE this switch can be used, or else, you'll run into this 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.