Solved

SQL 2005 Log Shipping

Posted on 2010-11-07
6
381 Views
Last Modified: 2012-06-27
Hi

I am trying to setup log shipping on my test server. This is what I did.

I created a test database on primary server (SQL 2005) with full recovery model
Took a full backup of this database and restored on secondary server (SQL 2005)
On primary server, selected the database and enabled log shipping using wizard. I left most of the options to default. Selected the "Network path to Backup folder" as a shared folder on secondary server (shared folder with write permission to everyone). Same folder is mentioned as "Destination folder for copied files". Selected "No, the secondary database is initialized".

After this two jobs are created in primary server and three jobs created in secondary server. The two jobs in primary server and two jobs in secondary server executed without any errors. But the job LSRestore_PrimaryServer_TestDB on secondary server fails with the below messages

Date            08/11/2010 11:15:01 AM
Log            Job History (LSRestore_PrimaryServer_TestDB)

Step ID            1
Server            SecondaryServer
Job Name            LSRestore_PrimaryServer_TestDB
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: CHSAPTEC\Administrator. The step failed.

Any ideas?

Thanks.
0
Comment
Question by:bijualex
  • 4
  • 2
6 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34082427
when you restored your secondary database,
did you leave the database non-operational (restore with norecovery) ?
You have to do this so the transaction logs can be restored.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34082464
On the restore tab, under database state when restoring backups, choose the no recovery mode.
On Books Online, type in
How to: Enable Log Shipping (SQL Server Management Studio).

It gives a step by step approach on how to configure it correctly.
0
 

Author Comment

by:bijualex
ID: 34082501
Thank you very much for your feedback. No it was not restored with no recovery. I restored it again with "no recovery " option and now the database on the sec server is showing TestDB (Restoring...). Then I started the log shipping wizard again, but the time when I choose sec server, I get the below error.

Cannot open database "TestDB" requested by the login. The login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4060)

The credentials are given correctly, may be because the database is in non-operational mode? How to select the sec server here?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:Humpdy
ID: 34082509
Hi,

So it's restoring correctly now ?
You just can't connect to any of the databases on the secondary server to query the data is it ?
0
 
LVL 10

Accepted Solution

by:
Humpdy earned 500 total points
ID: 34082529
sorry, I misread your second post.

have a look through, it's a very good step by step example with print screens which has worked for me.
http://alexsdba.spaces.live.com/blog/cns!F86565E81CD9BC16!182.entry
0
 

Author Comment

by:bijualex
ID: 34084116
I created an empty database in the sec server and then selected "Generate a full back and restore to secondary" option and it worked :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Move SQL 2005 Express to Server 2012R2 19 100
First name pregmatch 11 36
MySQL Init Waits 25 78
Could you point why this Codeigniter's data insertion is causing an error? 8 46
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now