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?
norecovery
.