Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Log Shipping setup not restoring

Author  Topic 

mattt
Posting Yak Master

194 Posts

Posted - 2011-01-06 : 06:46:51
Hi,

I have attempted to set up a log shipping partnership between two databases, using the wizard. It setup fine and the principal server is indeed producing log file backups at the specified instances.

However the failover server isn't picking them up. As a double annoyance I can't find out any useful information on *why* the restore is failing: all I'm seeing in the log is warning alerts telling me that the log shipping job has passed its threshold, and the various log shipping tables are either empty or not returning any meaningful information.

The folder that I've specified as a target for the copied files on the secondary server (using local notation - i.e. D:\Pickup rather than a network share such as \\localmachinename\Pickup) is empty. I've given write permissions to all users and anonymous users just to try and ensure that permissions aren't an issue, so I assume that's where the problem is.

Can anyone suggest a way forward to troubleshoot this issue? Also I keep seeing references to a "log shipping monitor" function in Management Studio, and I can't find it - where is it?

EDIT

In the SQL Server Agent logs, I found some helpful information. The Copy and Restore jobs are both failing. The copy job offers the following error message:

2011-01-06 12:00:00.30 *** Error: Could not retrieve copy settings for secondary ID 'a895f735-c11b-476b-9245-05c0ec7eb6a1'.(Microsoft.SqlServer.Management.LogShipping) ***

2011-01-06 12:00:00.30 *** Error: Failed to connect to server BLAH-W7\SQLEXPRESS.(Microsoft.SqlServer.ConnectionInfo) ***

2011-01-06 12:00:00.30 *** Error: Login failed for user '-MYLOCAKLWINDOWSACCOUNT7$'.(.Net SqlClient Data Provider) ***

And the restore job fails with a similar message.

What's odd about this is that the server "BLAH-W7" doesn't actually exist. It's not a meaningful machine name on our network and it certainly isn't the name of the principal server that it should be looking for the logfiles on, which is a bit weird.

It's trying to use my local Windows account for the job. That should be fine as that account has admin status (i.e full control) on all drives on both servers. But should I change it to use a SQL login account - if so, how?

Cheers,
Matt

jay456
Starting Member

4 Posts

Posted - 2011-01-06 : 07:07:38
Hello,

When you finished the configuration, in the "Save Log Shipping Configuration" were all the 5 steps succesfull ?
(Backing up primary database, restoring backup to secondary database...)

Are there files on your primary server, in the folder that contains the backups of your primary db ?
You are supposed to have the initial backup (.bak) of your db and some transaction logs backup files (.trn).

brs
Jay
Go to Top of Page

mattt
Posting Yak Master

194 Posts

Posted - 2011-01-06 : 07:13:15
quote:
Originally posted by jay456
When you finished the configuration, in the "Save Log Shipping Configuration" were all the 5 steps succesfull ?
(Backing up primary database, restoring backup to secondary database...)


Slightly puzzled by this question. When I finished going through the wizard it only went through 2 steps, not 5 and both of them succeeded.

quote:
Originally posted by jay456Are there files on your primary server, in the folder that contains the backups of your primary db ?
You are supposed to have the initial backup (.bak) of your db and some transaction logs backup files (.trn).



Yes. That's all there as expected and the system is generating new .trn files at the expected intervals. The primary server seems to be doing what it should without any fuss.
Go to Top of Page

jay456
Starting Member

4 Posts

Posted - 2011-01-06 : 07:14:36
I would first try to grant everyone full control on
- the shared folder on your primary server
- the copy folder on your secondary server
Just to check that it is not just a problem of accessing the data while attempting to copy it from primary to secondary.
Go to Top of Page

mattt
Posting Yak Master

194 Posts

Posted - 2011-01-06 : 07:20:34
quote:
Originally posted by jay456

I would first try to grant everyone full control on
- the shared folder on your primary server
- the copy folder on your secondary server
Just to check that it is not just a problem of accessing the data while attempting to copy it from primary to secondary.



That's already the case. I did it to try and avoid exactly these sorts of problems :)

Thanks for trying to help by the way.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-01-06 : 07:24:22
I know nothing about this, so this may be no help, but I seem to remember reading that the Service Accounts at both ends of Log Shipping must be identical (or even perhaps they may have to be the SAME network account)
Go to Top of Page

mattt
Posting Yak Master

194 Posts

Posted - 2011-01-06 : 07:36:55
OK, this is odd. It turns out that the machine the job is trying to copy to does exist, but it's certainly not the primary server - it's another desktop machine belonging to a colleague.

I have no idea why the wizard has assigned the job to look at that machine, but there we go. In the interim I can't work out the syntax for the job its trying to do, execute the command line:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Copy A895F735-C11B-476B-9245-05C0EC7EB6A1 -server BLAH-W7/SQLEXPRESS

The sql server that's supposed to be the primary is named SQL01 but substituting that for BLAH-W7/SQLEXPRESS still fails. What's the correct syntax for the server name?
Go to Top of Page

jay456
Starting Member

4 Posts

Posted - 2011-01-06 : 07:44:39
In my case after the -server switch, I have :
Secondary_Server_Hostname\Instance_Name

I think BLAH-W7 is your primary server, isn't it ?
Go to Top of Page
   

- Advertisement -