Solved

SQL Log Shipping Error

Posted on 2013-12-12
8
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 78

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 78

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 78

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 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