Log shipping issue

Question

Post reply

Log shipping issue

shashi_1409

SSCommitted

Points: 1599
More actions
March 14, 2012 at 1:38 pm

#254761

I am trying to setup the log shipping between two SQL 2008 databases residing on two differnt servers in the same domain.

I have logged on the Primary server using the domain admin account, I have made sure that this domain admin has full access to the drives where I am trying to create the backups Or copying the backups for restoring on the secondary server .Not sure what I may be missing. The error log shows Operating system error 5(access is denied).

Backup task is successfull, I could see the backup file in the E:\Backups\LOG_Backup On SWSQL2005A server. the error I see is cannot open backup device '\\SWSQL2005A\E$\Backups\Shashi_Backup\PerformnaceDB_Shashi.bak'. Operating system error 5(Access is denied.).RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201).

So can some one please help me out

LOG shipping configuration

domain admin account: systemworks\spusa

Primary Server : SWSQL2005A

Backup location :E:\Backups\LOG_Backup\

Secondary Server: SWSQL2008A

Copy Destination Folder : E:\Backups\LOG_Backup\

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

SS999 SSCommitted Points: 1663 More actions · Answer 1

Hi,

Create a shared directory on ur primary server and provide necessary privilege for the secondary server SQL Agent service account. Once u have given the rights try accessing the primary server shared folder by RDP into the secondary server and trying the UNC path like \\servername\driveletter$. Once the agent service accounts have sufficient privileges, it should be able to fetch the backups by itself.

Thanks

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 2

It's always worth remembering that SQL runs under the context of it's service accounts (unless you setup specific proxies).

This means if you are executing a command directly it will run as the SQL Server service account. If a job it will run as the SQL Server Agent service account. Be sure that the relevant account has the access that you need to the share.

Shamless self promotion - read my blog http://sirsql.net

shashi_1409 SSCommitted Points: 1599 More actions · Answer 3

Hi , As you said I logged on to secondary server and checked the account SQL server Agent is running on ( I mean I went into SQL server Configuration Manager and went into prperties of sql server agent and looked at the log on as and it is Local System, which I think is the Domain account that I logged on). this account is having full privileges to that shared drive.

I have even checked accessing this shared folder from the secondary server, which I could.

Not sure what is wrong with my log shipping setup, it is still not working:crying:

evelinel1f4r Valued Member Points: 60 More actions · Answer 4

evelinel1f4r

Valued Member

Points: 60

March 14, 2012 at 8:46 pm

#1459597

Once the agent service accounts have sufficient privileges, it should be able to fetch the backups by itself

SS999 SSCommitted Points: 1663 More actions · Answer 5

Hi,

Did u do the following steps

1: Create a Shared folder/directory on both the servers.

2: The Agent Service account of the secondary server should have read/write access to the shared folder on primary server.

3: The Agent Service account of the primary server should also be given access to this shared folder.

4: If this is a dev or some lower environment provide full access rights to both the agent service accounts on both the servers (in-detail) service account of primary server should have full access to the shared drive on secondary server and vice-versa.

If u have accomplished all these steps and still unable to copy the backup files, check the log shipping configuration once again. Place the correct UNC paths on the configuration wizard appropriately. This should fix the issue.

But the error itself says that the "access is denied", which means the service account is not able to access the shared folder.

Just check the privileges and u should be good. Let us know how it worked for u!!

Thanks

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 6

shashi_1409 (3/14/2012)

Hi , As you said I logged on to secondary server and checked the account SQL server Agent is running on ( I mean I went into SQL server Configuration Manager and went into prperties of sql server agent and looked at the log on as and it is Local System, which I think is the Domain account that I logged on). this account is having full privileges to that shared drive.

If the SQL Agent is running under the context of localsystem it will not have access to the remote share. You will either need to have SQL Agent run under a domain account with access on the remote share, or have it run as Network Service and grant the computer account access to the remote share.

Shamless self promotion - read my blog http://sirsql.net

rooierus SSChasing Mays Points: 654 More actions · Answer 7

rooierus

SSChasing Mays

Points: 654

March 15, 2012 at 11:03 am

#1460028

Just to make sure the file isn't in use by another process it might be a good idea to download handle.exe from sysinternals.

If the file is in use you can check which process keeps it locked.

shashi_1409 SSCommitted Points: 1599 More actions · Answer 8

Hi Nicholas, this is what I got to know Local System Account and this has got full access of allresources on the srever and network resources.

Local System Account: This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.

Read more: http://sql-articles.com/articles/general/sql-server-service-accounts/#ixzz1pCyBbO6T

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 9

Nicholas Cain

SSC-Dedicated

Points: 33009

March 15, 2012 at 12:13 pm

#1460078

The account has full access to the local server (which is a security concern) however it has no access outside of that server meaning that you cannot hit the share to copy over the backups. This prevents your process from functioning.

Shamless self promotion - read my blog http://sirsql.net

Perry Whittle SSC Guru Points: 233792 More actions · Answer 10

shashi_1409 (3/14/2012)

Backup location :E:\Backups\

You need to create a share on the folder above, you should not use the administrative share. Use either a local user account or a domain account to secure the sql server agent service and ensure the account has access to the backup share on the primary server. See my guide at this[/url] link for more info

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

shashi_1409 SSCommitted Points: 1599 More actions · Answer 11

shashi_1409

SSCommitted

Points: 1599

March 16, 2012 at 8:38 am

#1460547

Thanks everyone for their inputs onmy issue, I got my Log shipping setup by making SQL server Agent of secondary server to run on my Local Admin account and thus it had access on the network share on the primary server.:-)

shashi_1409 SSCommitted Points: 1599 More actions · Answer 12

Thanks everyone for your inputs on this issue, On secondary server by making SQL Server Agent to run on local Admin Account I could now setup the log shipping with secondary in STANDBY mode and was monitoring it( all the Log shipping jobs were running successuful ):-)

New issue:

BUT to check the log shipping , I have created a new table in the primary database and after the next transaction log restore , I queried the secondary database for that new table , the result NOOOO new table , I even tried updating the data in the Primary datbase that change was also not replicated into secondary database .:crying:

Anyone have idea why is log shipping not working ..