Solved

SQL Log Shipping Error

Posted on 2013-12-12
8
340 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 76

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 76

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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 76

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now