Log Shipping skipping restore files

Question

Post reply

Log Shipping skipping restore files

sonicwrx

SSC Veteran

Points: 212
More actions
June 17, 2010 at 9:35 am

#140753

I have setup log shipping between two servers. All the jobs are completing with no errors but the restore job on the secondary server does not actually restore the files.
It just skips all the log files, here is a sample entry:
Skipped log backup file. Secondary DB: 'DBName', File: 'C:\SQL\LogShip\DBName_20100616190001.trn'
What would make this happen?
These two servers are connected via vpn and previously had been setup to use log shipping, but the backups had expired. When I went to set it up again with SSMS, when I went to delete log shipping, the GUI couldn't connect to the secondary server. So I removed all the entries from the log shipping tables in msdb. I am wondering is there any way to know what logic the log shipping restore job uses to know if it should apply a log file or not?
Any help would be greatly appreciated!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 741912 More actions · Answer 1

Have you checked these tables to see what's on the secondary server as far as restores/not restores?

http://msdn.microsoft.com/en-US/library/ms190224%28v=SQL.90%29.aspx

I believe it goes on LSN, checks the first one in the backup file (log) to determine if it should be applied. If it broke somewhere in the chain, it wouldn't restore more log files.

sonicwrx SSC Veteran Points: 212 More actions · Answer 2

when I run

select * from msdb..log_shipping_monitor_secondary

I see that the [last_restored_file] field is null.

running

sp_help_log_shipping_secondary_database 'server'

sp_help_log_shipping_secondary_primary 'server, 'db'

seems to bring back the right results.....

Steve Jones - SSC Editor SSC Guru Points: 741912 More actions · Answer 3

What's the right results?

Sounds like log shipping got out of sync somewhere. My guess is you need to remove it from the secondary and reset it up between them.

sonicwrx SSC Veteran Points: 212 More actions · Answer 4

sp_help_log_shipping_secondary_database 'db'

secondary_id primary_server primary_database backup_source_directory backup_destination_directory file_retention_period copy_job_id restore_job_id monitor_server monitor_server_security_mode secondary_database restore_delay restore_all restore_mode disconnect_users block_size buffer_count max_transfer_size restore_threshold threshold_alert threshold_alert_enabled last_copied_file last_copied_date last_copied_date_utc last_restored_file last_restored_date last_restored_date_utc history_retention_period last_restored_latency

------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ------------------------------------ ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ----------- ------------ ---------------- ----------- ------------ ----------------- ----------------- --------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ------------------------ ---------------------

18DC765F-6D6D-4C72-BCC4-579F58880FD2 myhost\MSSQL2005A mydb \\myhost\LogShip C:\SQL\LogShip 20160 0290A8A2-AF44-465A-9266-03B3ECF3F4C1 AA1EFC8B-C54D-4D9D-8EF6-82B483D41F93 NULL NULL mydb 0 1 1 1 -1 -1 -1 60 14421 1 C:\SQL\LogShip\mydb_20100617160500.trn 2010-06-17 12:24:22.630 2010-06-17 16:24:22.630 NULL 2010-06-15 12:10:15.020 2010-06-15 16:10:15.020 8000 NULL

sp_help_log_shipping_secondary_primary 'host','db'

secondary_id primary_server primary_database backup_source_directory backup_destination_directory file_retention_period copy_job_id restore_job_id monitor_server monitor_server_security_mode

------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ------------------------------------ ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------------------------

18DC765F-6D6D-4C72-BCC4-579F58880FD2 myhost\MSSQL2005A mydb \\myhost\LogShip C:\SQL\LogShip 20160 0290A8A2-AF44-465A-9266-03B3ECF3F4C1 AA1EFC8B-C54D-4D9D-8EF6-82B483D41F93 NULL NULL

Good question!