Solved

Restore job fails in SQL Server

Posted on 2009-05-16
7
1,419 Views
Last Modified: 2012-05-07
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\sqllogship.exe" -Restore C95BF037-BDE6-4B38-9985-2A0E3E2E6595 -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
0
Comment
Question by:aatishpatel
  • 3
  • 2
7 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24404800
post screenshots, please.
0
 

Author Comment

by:aatishpatel
ID: 24406271
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-2a0e3e2e6595'
2009-05-17 10:00:01.00      Retrieving restore settings. Secondary ID: 'c95bf037-bde6-4b38-9985-2a0e3e2e6595'
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_20081102183004.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.LogShipping) ***
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-2a0e3e2e6595'


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.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24408378
The problem is that you're doing log shipping, but the last restore you did on your 2ndary server, RECOVER was specified...so no further backups can be applied.  You'll need to reinitialize the backups starting with a full....really need to start it all over again.  That is really the only thing you can do here.
0
 

Author Closing Comment

by:aatishpatel
ID: 31582259
thanx
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24476525
this wasn't a B answer...
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading to SQL Server 2015 Express 2 27
SQL- GROUP BY 4 21
Trigger C# code inside the SQL Server 6 23
Need SSIS project 2 23
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question