I would like to set up a secondary, mirrored SQL Server for reporting purposes.
The reporting database doesn't have to be up to date with the primary and can generally lag behind the production database by as much as one day, maybe more.
Generally, the first FULL backup gets restored, and sometimes even a few LOG backups, but then it gets stuck, it stops seeing newer backups and just goes in a loop, doing nothing.
Troubleshooting seems to indicate to me that
sp_AllNightLog @Restore = 1 runs into an error when restoring a database, or maybe a LOG file, records that in the
restore_worker table, which stops all further restores from being issued. If I clear the
error_number (-1) and
last_error_date from the table, it starts restoring again but then hits an error again because it starts restoring the FULL backup instead of continuing with LOG restores. Then it logs that error date and error number and stops again.
Another issue is that
sp_DatabaseRestore (from the Kit) leaves the restored databases, either from a FULL restore or LOG restore, in an unrecovered state (always uses
NORECOVERY and never runs
RESTORE WITH RECOVERY). That makes the databases unreadable on the secondary unless I change
sp_AllNightLog from using
@RunRecovery = 0 to
@RunRecovery = 1:
-- Line 1302 IF @restore_full = 0 BEGIN IF @Debug = 1 RAISERROR ('Starting Log only restores', 0, 1) WITH NOWAIT; EXEC master.dbo.sp_DatabaseRestore @Database = @database, @BackupPathFull = @restore_path_full, @BackupPathLog = @restore_path_log, @ContinueLogs = 1, @RunRecovery = 1, @OnlyLogsAfter = @only_logs_after, @Debug = @Debug END IF @restore_full = 1 BEGIN IF @Debug = 1 RAISERROR ('Starting first Full restore from: ', 0, 1) WITH NOWAIT; IF @Debug = 1 RAISERROR (@restore_path_full, 0, 1) WITH NOWAIT; EXEC master.dbo.sp_DatabaseRestore @Database = @database, @BackupPathFull = @restore_path_full, @BackupPathLog = @restore_path_log, @ContinueLogs = 0, @RunRecovery = 1, @Debug = @Debug END
Do I have to delete backup history before I set this up? Has anyone written a complete guide to setting something like this up? I genuinely can't seem to find one even while googling.
Any help is greatly appreciated!