aatishpatel
asked on
Restore job fails in SQL Server
I have a job LSRestore_IHS_MAIN-SQL-02- P51 that fails.
When I go in the steps I just see
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqllo gship.exe" -Restore C95BF037-BDE6-4B38-9985-2A 0E3E2E6595 -server IHS-MAIN-IMG-01. Nothing else.
Now when I see history I see------
The job failed. The job was invoked by schedule 136 (DefaultRestoreJobSchedule ). The last step to run was step 1. (Log Shipping Restore Log job step).
More in detail--------------
Executed as user HIS\Sqlrptuser. The step failed.
so what is the problem. let me tell you that P51 database does not exist. if you see the first line it says that LSRestore_IHS_MAIN-SQL-02- P51
When I go in the steps I just see
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqllo
Now when I see history I see------
The job failed. The job was invoked by schedule 136 (DefaultRestoreJobSchedule
More in detail--------------
Executed as user HIS\Sqlrptuser. The step failed.
so what is the problem. let me tell you that P51 database does not exist. if you see the first line it says that LSRestore_IHS_MAIN-SQL-02-
post screenshots, please.
ASKER
this happens step by step:------------
Date 5/17/2009 10:00:00 AM
Log Job History (LSRestore_IHS-MAIN-SQL-02 _P51)
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_ P51
Step Name Log shipping restore log job step.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Microsoft (R) SQL Server Log Shipping Agent
[Assembly Version = 9.0.242.0, File Version = 9.00.3042.00]
c Microsoft Corp. All rights reserved.
2009-05-17 10:00:00.90 ----- START OF TRANSACTION LOG RESTORE -----
then
Log Job History (LSRestore_IHS-MAIN-SQL-02 _P51)
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_ P51
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:00.99 Starting transaction log restore. Secondary ID: 'c95bf037-bde6-4b38-9985-2 a0e3e2e659 5'
2009-05-17 10:00:01.00 Retrieving restore settings. Secondary ID: 'c95bf037-bde6-4b38-9985-2 a0e3e2e659 5'
2009-05-17 10:00:01.04 Retrieved common restore settings. Primary Server: 'IHS-MAIN-SQL-02', Primary Database: 'P51', Backup Destination Directory: 'S:\Transaction\P51', File Retention Period: 2880 minute(s)
2009-05-17 10:00:01.04 Retrieved database restore settings. Secondary Database: 'P51', Restore Delay: 0, Restore All: True, Restore Mode: No Recovery, Disconnect Users: False, Last Restored File: S:\Transaction\P51\P51_200 8110218300 4.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
then
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_ P51
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:01.15 *** Error: The restore operation cannot proceed because the secondary database 'P51' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer. Management .LogShippi ng) ***
2009-05-17 10:00:01.18 Deleting old log backup files. Primary Database: 'P51'
then
Log Job History (LSRestore_IHS-MAIN-SQL-02 _P51)
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_ P51
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:01.18 The restore operation completed with errors. Secondary ID: 'c95bf037-bde6-4b38-9985-2 a0e3e2e659 5'
then
Date 5/17/2009 10:00:00 AM
Log Job History (LSRestore_IHS-MAIN-SQL-02 _P51)
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_ P51
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:01.24 ----- END OF TRANSACTION LOG RESTORE -----
Exit Status: 1 (Error)
then
ate 5/17/2009 10:00:00 AM
Log Job History (LSRestore_IHS-MAIN-SQL-02 _P51)
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_ P51
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: IHS\sqlrptuser. The step failed.
so these are all the detailed error steps.
Date 5/17/2009 10:00:00 AM
Log Job History (LSRestore_IHS-MAIN-SQL-02
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_
Step Name Log shipping restore log job step.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Microsoft (R) SQL Server Log Shipping Agent
[Assembly Version = 9.0.242.0, File Version = 9.00.3042.00]
c Microsoft Corp. All rights reserved.
2009-05-17 10:00:00.90 ----- START OF TRANSACTION LOG RESTORE -----
then
Log Job History (LSRestore_IHS-MAIN-SQL-02
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:00.99 Starting transaction log restore. Secondary ID: 'c95bf037-bde6-4b38-9985-2
2009-05-17 10:00:01.00 Retrieving restore settings. Secondary ID: 'c95bf037-bde6-4b38-9985-2
2009-05-17 10:00:01.04 Retrieved common restore settings. Primary Server: 'IHS-MAIN-SQL-02', Primary Database: 'P51', Backup Destination Directory: 'S:\Transaction\P51', File Retention Period: 2880 minute(s)
2009-05-17 10:00:01.04 Retrieved database restore settings. Secondary Database: 'P51', Restore Delay: 0, Restore All: True, Restore Mode: No Recovery, Disconnect Users: False, Last Restored File: S:\Transaction\P51\P51_200
then
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:01.15 *** Error: The restore operation cannot proceed because the secondary database 'P51' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.
2009-05-17 10:00:01.18 Deleting old log backup files. Primary Database: 'P51'
then
Log Job History (LSRestore_IHS-MAIN-SQL-02
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:01.18 The restore operation completed with errors. Secondary ID: 'c95bf037-bde6-4b38-9985-2
then
Date 5/17/2009 10:00:00 AM
Log Job History (LSRestore_IHS-MAIN-SQL-02
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2009-05-17 10:00:01.24 ----- END OF TRANSACTION LOG RESTORE -----
Exit Status: 1 (Error)
then
ate 5/17/2009 10:00:00 AM
Log Job History (LSRestore_IHS-MAIN-SQL-02
Step ID 1
Server IHS-MAIN-IMG-01
Job Name LSRestore_IHS-MAIN-SQL-02_
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: IHS\sqlrptuser. The step failed.
so these are all the detailed error steps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanx
this wasn't a B answer...