December 9, 2009 at 1:30 pm
I am attempting to create log shipping between our production server and a server that
has been created as out standby source.
The primary database BACKUP file is approximately 157 GB. It is taking us about twenty five minutes to do a FULL BACKUP and about another fifty minutes to send the the BACKUP file over to the secondary server. From there it takes about another thirty five minutes to RESTORE the database in STANDBY. Upon completion I have been attempting to use the Log Shipping Wizard on the primary server to set up the log shipping between the primary and secondary servers. upon completing the required information for the wizard to set up the log shipping I can see the three SQL jobs created (lsbackup on the primary and lscopy and lsrestore on the secondary) but the backup job fails from the start.
I have set up smaller log shipping jobs without much difficulty. However, this one has me completely stumped as to what to do. I have tried scripting based on other succesful log shipping jobs, re-doing the STANDBY database while the rest of the log shipping jobs remain in place. I have tried breaking everything and scripting the jobs one by one. Nothing seems to work. Does anyone have any suggestions how I can establish log shipping for this database?
Your help would be greatly appreciated.
December 10, 2009 at 10:38 am
Please post the errors that backup job is throwing.
December 10, 2009 at 10:45 am
i have redone everything and the ls backup appears to be functioning properly, the ls restore on the secondary is also functioning (although not actually restoring anything) but the lscopy is erroring. Here is the errorlog for the job....
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Error,0,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,(Job outcome),,The job failed. The Job was invoked by Schedule 17 (DefaultCopyJobSchedule). The last step to run was step 1 (Log shipping copy job step.).,00:00:02,0,0,,,,0
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Error,1,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,Log shipping copy job step.,,Executed as user: A2WCSQL01\SYSTEM. The step failed.,00:00:02,0,0,,,,0
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,Log shipping copy job step.,,2009-12-10 10:15:02.00----- END OF TRANSACTION LOG COPY -----<nl/><nl/>Exit Status: 1 (Error),00:00:02,0,0,,,,0
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,Log shipping copy job step.,,2009-12-10 10:15:01.84*** Error: Access to the path '\\A2WFMSQL01\WebCenterLogs' is denied.(mscorlib) ***,00:00:01,0,0,,,,0
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,Log shipping copy job step.,,2009-12-10 10:15:01.65Copying log backup files. Primary Server: 'A2WFMSQL01'<c/> Primary Database: 'WebCenter'<c/> Backup Source Directory: '\\A2WFMSQL01\WebCenterLogs'<c/> Backup Destination Directory: 'H:\WebCenterLogs',00:00:01,0,0,,,,0
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,Log shipping copy job step.,,2009-12-10 10:15:01.59Starting transaction log copy. Secondary ID: 'ff32be55-dbfb-47e3-bc01-4d0a4023c695'<nl/>2009-12-10 10:15:01.60Retrieving copy settings. Secondary ID: 'ff32be55-dbfb-47e3-bc01-4d0a4023c695'<nl/>2009-12-10 10:15:01.60Retrieved copy settings. Primary Server: 'A2WFMSQL01'<c/> Primary Database: 'WebCenter'<c/> Backup Source Directory: '\\A2WFMSQL01\WebCenterLogs'<c/> Backup Destination Directory: 'H:\WebCenterLogs'<c/> Last Copied File: '<None>',00:00:01,0,0,,,,0
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,Log shipping copy job step.,,2009-12-10 10:15:01.12----- START OF TRANSACTION LOG COPY -----,00:00:01,0,0,,,,0
12/10/2009 10:15:00,LSCopy_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSCopy_A2WFMSQL01_WebCenter,Log shipping copy job step.,,Microsoft (R) SQL Server Log Shipping Agent<nl/>[Assembly Version = 9.0.242.0<c/> File Version = 9.00.4035.00]<nl/>c Microsoft Corp. All rights reserved.,00:00:01,0,0,,,,0
12/10/2009 10:15:00,LSRestore_A2WFMSQL01_WebCenter,Unknown,0,A2WCSQL01,LSRestore_A2WFMSQL01_WebCenter,(Job outcome),,The job succeeded. The Job was invoked by Schedule 18 (DefaultRestoreJobSchedule). The last step to run was step 1 (Log shipping restore log job step.).,00:00:02,0,0,,,,0
12/10/2009 10:15:00,LSRestore_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSRestore_A2WFMSQL01_WebCenter,Log shipping restore log job step.,,Executed as user: A2WCSQL01\SYSTEM. The step succeeded.,00:00:02,0,0,,,,0
12/10/2009 10:15:00,LSRestore_A2WFMSQL01_WebCenter,Unknown,1,A2WCSQL01,LSRestore_A2WFMSQL01_WebCenter,Log shipping restore log job step.,,2009-12-10 10:15:01.89Searching through log backup files for first log backup to restore. Secondary DB: 'WebCenter'<nl/>2009-12-10 10:15:01.89Could not find a log backup file that could be applied to secondary database 'WebCenter'.<nl/>2009-12-10 10:15:01.89The restore operation was successful. Secondary Database: 'WebCenter'<c/> Number of log backup files restored: 0<nl/>2009-12-10 10:15:01.89Deleting old log backup files. Primary Database: 'WebCenter'<nl/>2009-12-10 10:15:01.89The restore operation was successful. Secondary ID: 'ff32be55-dbfb-47e3-bc01-4d0a4023c695'<nl/>2009-12-10 10:15:01.90----- END OF TRANSACTION LOG RESTORE
December 10, 2009 at 10:58 am
Hi Dave,
If you look into the log, it says it has some persmission issue for copying for that reason copying job is failing, look into that fix it and run the job, that should work.
*** Error: Access to the path '\\A2WFMSQL01\WebCenterLogs' is denied.(mscorlib) ***,
December 10, 2009 at 11:58 am
we have redone our access from the Secondary Server to the Primary and guess what? It all works! Thank you so much for your diagnosis.
February 15, 2011 at 11:25 pm
Dear Dave,
I am getting same error ,What permoission you have given here.
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply