SQL Log Shipping Error

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
terry_coleAsked:
Who is Participating?
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
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
 
arnoldCommented:
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
 
terry_coleAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
arnoldCommented:
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
 
terry_coleAuthor Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
terry_coleAuthor Commented:
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
 
arnoldCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.