Log Shipping does not ship to secondary database

Good day to the people who's reading this post!

I have some trouble with shipping my transaction logs to the secondary database be it on

another server or within the same server to another database instance.

Im using SQL Server 2005 workGroup editions with Service Pack 2.

Here are the problems that i encountered.

I do hope someone has bumped into such a problem and willing to help me out.

I tried on 2 separate servers (not domain environment) and also between 2 separate instances

(which is supposed to be simple!) on

our development server,but was unsuccessful.

Between 2 separate instances on the development server -

No error after configuring Log shipping though.

The configuring went through and it was a success.

Transaction logs was backup every minute and it got copied over to the other folder.

But SQL Agent not doing its last job which is supposed to restore to the secondary database on

another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to

look to see if there are errors given out?

Both instances SQL Agent has the same log on username and password with Administartive rights

So what went wrong?

Between the 2 servers-

The transaction logs was backup every 1min on the primary server

but it didn't got copied over to to the secondary database.

- Error message given was:Error in restoring database to the secondary

database.Network path given could not be found. Can't open the AxTest.bak file.

(i am very sure i have type the correct network path,even have shared it out and i think the firewall

is blocking incoming traffic since unlike our development server,

which allows us to access when we use Start>Run and type in the ip address,user name

and password,the primary server will only tell me no network path was found)

I also believe it's because the SQL Agent on the secondary database server wasn't given

permission to access the primary database folder.

I've shared out the drive and folder on the secondary server and

even have allowed SQL Agent to read,write and modify on both servers.

For the primary and secondary SQL Agent,

I configure their log on to be the same user account name and password

which have administrative rights.

So what went wrong?

Isit really true that both servers have to be in domain environment before you can configure log

shipping,mirroring and replication?

Hope someone help me out of this predicament.Thank you in advance!

Log shipping between domains or different work groups is pretty picky. Here's a link I used when setting it up before.

http://support.microsoft.com/default.aspx/kb/321247/

This section may apply to your situation:

Cross Domain Log Shipping

If computers that are running SQL Server are placed in a multi-domain environment, Microsoft recommends that you set up two-way trusts between all domains that are involved in log shipping. However, if you cannot establish trusts between domains, you can use network pass-through security for log shipping. Refer to the section of this article that discusses the LocalSystem network account startup option for SQL Server-related services.

Why your restores are not working between instances is another story. Maybe try restoring the copied tran log manually and see if it works or generates any errors. Add some log files to the job steps directly and see if you get anymore information.

Good Luck|||

Hi Mark!Your post is certainly very helpful!

Could you correct me if i misunderstood the article please.

Does it mean that if i set up the SQL services log on account on the secondary database with the exact match of a local user account which exists on the primary

database server,

my secondary database SQL services will be able to access the primary database because it has bypass the network security?

Hope to hear your reply soon.

By the way i found out that certain ports has to be open to enable SQL services to execute the log shipping properly.

Port 135 - for the services

Ports 137, 138, and 139 or 445 need to be open too cuz im using UNC path for the folder sharing

This link will provide more information. Hope it will help others...

http://72.14.235.104/search?q =cache:Nb5a6PvckzwJ:www.stlssug.org/docs/Log-Shipping.ppt+what+port+does+log+shipping+use&hl=en&ct=clnk&cd=1

|||Yes, we synced the administrator passwords on both servers.

Good luck and thanks for the port info.|||

Hi Mark.

Turns out the log shipping still didnt work between the two servers even after opening the ports.

It keeps saying the network path could not be found each time the agent from the secondary database server tried to retrieve the

transaction log file to restore a new database.

Any idea if there is any other security settings that might be blocking the server from accessing the primary database

server network path? Or do you know if i have to enable any services?

My log shipping between the 2 instances work now though.

SQL Browser were disabled just had to enable it back.

Thanks in Advance!

|||If you have already done/tried the things below, I can't help you. There seems to be a network disconnect. Is there a trust set up between the 2 servers? Sorry, I'm not a network admin guy.

Local Network Account

You can use SQL Server to start under a locally-created network account. In the situation where there is network access required by a SQL Server process, which is the case if you have configured SQL Server to use log shipping, you can use network pass-through security. With pass-through security, all machines that will be accessed by SQL Server must have the same network account with the same password and appropriate permissions, configured locally. Additionally, when the SQL Server process requests resources from the second computer, traditional network security is bypassed if the same account (under which the requesting SQL Server service is started) exists with the same password. As long the account on the second computer is configured with enough permission to carry out the task that is requested by calling SQL Server, the task will be successful.

Local System Account

You can also configure SQL Server to start under the Local System account. Modifying the password for the LocalSystem account may result in the failure of some services that are critical for system stability. This account is local to the computer where it resides, which means that the security context that SQL Server services uses is local. As stated in the Local Network Account section, you cannot use network pass-through security when you start SQL Server under the LocalSystem account because the passwords for the LocalSystem account on different computers are different. The starting of SQL Server under this account when network resource access is required will most likely result in the unsuccessful completion of tasks.