Solved

SQL Log Shipping Error

Posted on 2013-12-12
8
346 Views
Last Modified: 2014-01-03
I created a new log shipping connection and the initial backup and restore worked flawlessly.  Since then I have not been able to get the automated primary database backup job to generate the log files.

The backup job on the primary server is throwing an error every time the job executes.

The error is: Error: Failed to connect to server WIN-94GDJK3NTGD.(Microsoft.SqlServer.ConnectionInfo).

This is not the server name and I don't know where it came from or how to get rid of it.  It should not be trying to reach across the network for this job.  

What am I missing?

Thank you,
Terry
0
Comment
Question by:terry_cole
  • 3
  • 3
  • 2
8 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 39715952
Versions of OS/sql server?
Variations since the initial setup?
Look at the job that is failing are you using a linked server references.
0
 

Author Comment

by:terry_cole
ID: 39717065
Hello,

We are using Windows Server 2008 R2 and SQL Server 2008.

This is the initial setup.  It performed the initial restore and has not processed anything since.

I'm not sure about linked server reference question.  I used the wizard to set up the process.  It seemed to be very standard.
0
 
LVL 77

Expert Comment

by:arnold
ID: 39717432
Look at the jobs.

There should be two (primary) or (destination) three jobs.  one that generates the transaction log. One that then notifies the remote that a log is available.
On the destination there are three. one that transferrs/copies the log from the share. one that restores while in recovery the log.

The issue might be if you have another backup (full/transaction) process setup

What credentials are you using for these jobs? Domain account?
With whose credentials does the mssql agent is runing?

When you ran the process initially, it may have ran with Administrator (integrated) while when it is running as a job it is being denied network access.
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.

 

Author Comment

by:terry_cole
ID: 39720486
There is only the one job and it seems that the only problem is the server name has aribitrarily changed in the job.  There are no errors logged related to credentials.  

The job cannot locate the server "WIN-94GDJK3NTGD" as noted above.  That is not the name of either server.  

I can set up the log shipping from scratch again but I want to understand how it changed the name so it doesn't happen again.  

I have set up log shipping several times on other servers and have not seen this happen before.

Thanks!
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 39722940
So this server is not listed in the Transaction Log Shipping section when you right click on the Primary database and choose Properties.

Look for the name of the server in that dialog and see what it says.
0
 

Author Comment

by:terry_cole
ID: 39723938
The server name reported under SQL Management studio is correct and matches the name under System Properties for the server however the name of the database owner is WIN-94GDJK3NTGD\Administrator.  This leads me to believe the server name was changed at some point.  

Searching the registry I find WIN-94GDJK3NTGD listed under :
HKLM\Software\MS\MSSQLServer\100\Machines\OriginalMachineName
HKLM\Software\MS\MSSQLServer\Services\ReportServer\GroupPrefix
HKLM\Software\MS\MSSQLServer\Services\ReportServer\SSIS Server
HKLM\Software\MS\MSSQLServer\Client\ConnectTo
HKLM\Software\Wow6432Node\MS\MSSQLServer\100\Machines\OriginalMachineName
HKLM\Software\Wow6432Node\MS\MSSQLServer\90\Machines\OriginalMachineName
and several others.

Would it be safe/advisable to change those registry keys to the correct AD server name or...?

thanks,
0
 
LVL 77

Expert Comment

by:arnold
ID: 39724055
There is an sp in sql to correct the sql server name.
double check whether the system has two name references.  You could use netdom to rename it.  Another option is to create a domain service account and then configure ms sql to use those credentials instead of local account.
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 39724177
you can use sp_helpserver to find out what the server name is being reported.

You can also do any of the following:

SELECT @@SERVERNAME
SELECT name FROM sys.servers

If you find that the name is wrong, you can do the following

sp_dropserver 'name of wrong server'
sp_addserver 'name of correct or local servername', 'LOCAL'

Then you can check the SELECT @@SERVERNAME and it should reflect the local server.
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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

840 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