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.
| 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?EDITIn 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).brsJay |
 |
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2011-01-06 : 07:13:15
|
quote:
Originally posted by jay456When 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. |
|
|
|
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 serverJust to check that it is not just a problem of accessing the data while attempting to copy it from primary to secondary. |
|
|
|
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 serverJust 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. |
|
|
|
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) |
|
|
|
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/SQLEXPRESSThe 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? |
|
|
|
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_NameI think BLAH-W7 is your primary server, isn't it ? |
|
|
|
|
|
|
|
|
Log Shipping setup not restoring