SQL Server Performance

Log shipping skip trx log and not able to resotre trx backup

Discussion in 'SQL Server 2005 Log Shipping' started by yhchan2005, Apr 2, 2010.

  1. yhchan2005 Member

    Hi,
    i have restore the full backup into secondary server and manual run the script to configure the log shipping setting. i also have copy the trx backup from primary server into secondary server. when i try to run the schedule copy job, the job run successful but no file are copy. it is same to restore job. below is the process which i copy from hisotry
    Message
    2010-04-03 11:42:26.84 Skipped log backup file. Secondary DB: 'LS_Account', File: 'd:LogShippingAccount_20100402003001.trn'
    2010-04-03 11:42:26.90 Skipped log backup file. Secondary DB: 'LS_Account', File: 'd:LogShippingAccount_20100402004501.trn'
    2010-04-03 11:42:27.00 Skipped log backup file. Secondary DB: 'LS_Account', File: 'd:LogShippingAccount_20100402010000.trn'
    2010-04-03 11:42:27.12 Skipped log backup file. Secondary DB: 'LS_Account', File: 'd:LogShippingAccount_20100402011500.trn'
    2010-04-03 11:42:27.73 Skipped log backup file. Secondary DB: 'LS_Account', File: 'd:LogShippingAccount_20100402013000.trn'
    2010-04-03 11:42:27.82 Found first log backup file to restore. Secondary DB: 'LS_Account', File: 'd:LogShippingAccount_20100402014500.trn'
    2010-04-03 11:42:27.82 The restore operation was successful. Secondary Database: 'LS_Account', Number of log backup files restored: 0
    2010-04-03 11:42:27.84 Deleting old log backup files. Primary Database: 'Account'
    2010-04-03 11:42:27.85 The restore operation was successful. Secondary ID: '9cbeb51a-5dfe-4295-a68d-c347e3f73c95'
    i have other 2 database ( CRM, Payroll ) which is doing the same but only this database i face this problem. CRM and Payroll Db now is running without any problem. Please help !
  2. MohammedU New Member

    It doesn't make any send to work with one db not the other...other than there is some config change or there is some other tlog backup job on primary running.
  3. yhchan2005 Member

    i have check, all these 3 db log shipping setting are same ( other than database name ). and also confirm there is only 1 trx backup job in primary server.
    i have download all the trx backup file from primary server back to secondary sever and i manage to retore them 1 by 1 up to date. this means the trn backup should not have any problem with the database. the problem i am facing is why the SQL server manage to locate the trx backup file but not restore them.
    refer to the SQL message, '2010-04-03 11:42:27.82 Found first log backup file to restore', but sql server did not perform the restore job and report successful ( 2010-04-03 11:42:27.82 The restore operation was successful ). but you can see there is no file to restore ( Number of log backup files restored: 0 )
    this is the CRM log shipping message which was successful
    Message
    2010-04-06 09:45:05.62 Retrieving restore settings. Secondary ID: 'fa2bb71d-7fba-4d97-bb18-9e45870edaba'
    2010-04-06 09:45:05.64 Retrieved common restore settings. Primary Server: 'Production', Primary Database: 'CRM', Backup Destination Directory: 'd:LogShipping', File Retention Period: 4320 minute(s)
    2010-04-06 09:45:05.64 Retrieved database restore settings. Secondary Database: 'LS_CRM', Restore Delay: 5, Restore All: True, Restore Mode: Standby, Disconnect Users: True, Last Restored File: d:LogShippingCRM_20100406010001.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
    2010-04-06 09:45:06.81 Disconnecting users. Secondary DB: 'LS_CRM'
    2010-04-06 09:45:14.48 Restored log backup file. Secondary DB: 'LS_CRM', File: 'd:LogShippingCRM_20100406011500.trn'
    2010-04-06 09:45:15.06 The restore operation was successful. Secondary Database: 'LS_CRM', Number of log backup files restored: 1
    2010-04-06 09:45:15.09 Deleting old log backup files. Primary Database: 'CRM'
    2010-04-06 09:45:15.15 Deleting log backup file. File: 'd:LogShippingCRM_20100403013000.trn', Last Write Time (UTC): '4/3/2010 1:30:00 AM'
    2010-04-06 09:45:15.20 The restore operation was successful. Secondary ID: 'fa2bb71d-7fba-4d97-bb18-9e45870edaba'
    2010-04-06 09:45:15.32 ----- END OF TRANSACTION LOG RESTORE -----
    Please help !!!!
  4. yhchan2005 Member

    i have search on the internet and also found some user facing the same problem. notice that if sql return this kind of message 'Skipped log backup file. Secondary .....' then the log shipping will not work. unfortunately all the result i search on the internet there is no answer for it.
    please help ..... if the log shipping not wrok for this db, i might need to study how to configure this db using database mirroring....
  5. MohammedU New Member

    Did you try to cleaning the log shipping for this db and redo it again...see it resolves...

Share This Page