• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

Log Shipping - Need Help Making it Work

I need help figuring out why log shipping is not working for me.  The DB gets created and nothing obviously fails, but nothing past the initial DB-creation ever happens.

Executing this query on Server2 (the log shippee, the server who should be restoring TRN files from Server1):

>select * from msdb.dbo.restorehistory

yields a single record...and that single record is the initial DB restore that's performed to create the log-shipping target DB.

On Server1 (the log shipper), the TRN files do successfully continue to pile up.

On Server2, its "target" folder never ever receives any TRN files at all.

I need help getting this working.  Oh, by the way, Server2 is doing NOTHING, there are no DBs on it other than this one I'm trying to create and log-ship to from Server1.

I'm going to put some screenshots below, showing my current settings.

Thanks.
LogShipping0.jpg
LogShipping1.jpg
LogShipping2.jpg
LogShipping3.jpg
0
bamapie
Asked:
bamapie
  • 9
  • 6
  • 2
2 Solutions
 
chapmandewCommented:
Look at your job histories...specifically the jobs on the target that are to apply the trans logs....see if they are enabled.  If they are, see if they are erroring.
0
 
bamapieAuthor Commented:
One of the errors is this:

Login failed: The login is from an untrusted domain and cannot be used with Windows authentication.

0
 
chapmandewCommented:
ahhh..the plot thickens.  make sure the services (sql service and sql agent) both use domain accounts (same accounts should be fine) and that the accounts have local access to the drives on the machines you're using to put the log files.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bamapieAuthor Commented:
Both are using the same domain account, and on both machines, this domain account is part of the local Administrators group.

Also, this same credential has Full Control over the 2 folders in question (on Server1 and Server2).

Thanks
0
 
chapmandewCommented:
are you spanning domains?
0
 
bamapieAuthor Commented:
Sorry, Chapman...I don't even know what that means.  I'll ask one of our network guys if we're spanning domains.
0
 
chapmandewCommented:
Sorry...I should have been more specific. The error you recieved made me think that the computers are on different domains and those domains do not trust each other. I would talk to your network guys to be sure.
0
 
Eugene ZCommented:

check sql agent service account (on server2 ) : make sure it is an AD account with NTFS rights (R-W at least) on the trn backup folder

0
 
bamapieAuthor Commented:
I'm not sure what helped me get through that step, but I:

(1)  Stopped using "MYDOM\MyUser" and started using "MyUser@MyDom.local" instead
and
(2)  Disabled T-logging completely for my DB, then added it all back in

and now, at least, I'm getting my TRN files copied over to Server2's folder now.

But I still don't think it's working--and I say that because running this query:

select * from msdb.dbo.restorehistory

on Server2 yields a single record, and this record is the initial full restore of the full backup that you can opt for when setting up T-logging.

Or is there some other way I should be trying to sense whether T-logging is actually working?

Thanks!
0
 
chapmandewCommented:
That query should do it....make sure there is a sql job that is being used to apply the logs on the destination....also, I think when a log is applied it will change the ModifiedDate on the files (database and log files) in the OS.
0
 
Eugene ZCommented:
check logshipping from logship admin server by running logship report that will tell you how is your LS doing
--
<Stopped using "MYDOM\MyUser" and started using "MyUser@MyDom.local" instead.

it looks like different logins ...
0
 
bamapieAuthor Commented:
Well, some headway, but I'm pretty firmly stuck now, it seems.

First, I now have TRN files appearing in Server2's target folder.  So the copy is happening.  That is a small triumph right there.

But I cannot get these guys to restore automatically.  This:

>select * from msdb.dbo.restorehistory

still yields the single record.

<sigh>
0
 
chapmandewCommented:
Did you check the sql agent jobs on the secondary to see what errors they are giving?
0
 
bamapieAuthor Commented:
The logs look so "green" and happy.  Yet nothing is apparently being done:

Searching through log backup files for first log backup to restore. Secondary DB: 'RSSQL1'<nl/>2010-01-12 09:40:01.13      Skipped log backup file. Secondary DB: 'RSSQL1'<c/> File: 'F:\BACKUPS\LOG_SHIPPING\RSSQL1\RSSQL1_20100112151516.trn'<nl/>2010-01-12 09:40:01.17      Skipped log backup file. Secondary DB: 'RSSQL1'<c/> File: 'F:\BACKUPS\LOG_SHIPPING\RSSQL1\RSSQL1_20100112153016.trn'<nl/>2010-01-12 09:40:01.17      Could not find a log backup file that could be applied to secondary database 'RSSQL1'.<nl/>2010-01-12 09:40:01.17      The restore operation was successful. Secondary Database: 'RSSQL1'<c/> Number of log backup files restored: 0<nl/>2010-01-12 09:40:01.17

It's "successful", but it's always zero backup files restored, and no log backup files found that could be applied.

Yet those files are there, ready to go.

What the heck am I missing here?

Thanks for all your help on this.
0
 
bamapieAuthor Commented:
It remains to be seen--need to see it work a little longer--but I THINK that the problem was this:

I still had a periodic transaction-log backup active on Server1 for this database.  I thought I'd killed it, but I apparently had not.  Actually, I think this was several days ago, and I went back later and re-enabled the t-log backups.

Anyway, I currently have turned it back on, and one t-log backup has been automatically performed thus far.  This is a first.

I will let you know when all seems to be well.  

Thanks!
0
 
bamapieAuthor Commented:
That was indeed the problem--this database still had an active t-log backup job.  Which is a no-no if you're also going to undertake log-shipping.

I just plain missed that.  All good now.

If you two don't mind, I'm going to split points and award the question to chapmandew.

Thanks so much, you're both very helpful.
0
 
bamapieAuthor Commented:
Crap, I somehow still gummed that up.  I'm sorry, chapmandew.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now