?
Solved

SQL Log Shipping Error

Posted on 2013-12-12
8
Medium Priority
?
351 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 79

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 79

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 25

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 79

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 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

800 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