我正在实施一个自动日志传送解决方案,到目前为止,我有以下内容:
<#
#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
我在事务日志还原步骤中遇到错误:
Restore-SqlDatabase:System.Data.SqlClient.SqlError:无法恢复日志或差异备份,因为没有准备好前滚的文件。
我发现这-NoRecovery
是防止错误所必需的。否则,如果 SQL Server 在线并且还接受数据库备份文件,则可能存在数据不一致。因此,数据库必须处于可以进一步接受备份数据而不是在线数据请求的状态。
所以我尝试了:
Get-SqlDatabase -ServerInstance localhost -Database Testxyz | Backup-SqlDatabase -BackupFile 'C:\Users\...\Documents\DB Log Shipping\Backups\Testxyz.trn' -BackupAction Log -NoRecovery
但即便如此,仍然会导致错误。
我读过差异备份,其中差异备份仅捕获自完整备份以来已更改的数据。
如何解决此问题并实施差异备份日志?
使用增量开关,可以创建差异备份。
注意:必须先创建数据库的完整备份,然后才能使用此开关,否则您将遇到此错误: