October 24, 2011 at 2:44 pm
Dear all,
I just have finished to set the logshipping process between two SQL servers (remote and local) both I have set two folders (DOS) with all permissions (r/w) for my domain user that is also 'sa' in both SQL instances.
As you know there is 3 main jobs:
1.- backup log into the folder (ok)
2.- the copy files (trn) from remote folder to local folder (fail)
3.- the restore log from local folder (fail)
But Im getting an error in the second Job (when try to copy the log files) ---"error: Access to the path '\\10.0.0.9\LOGSHIP_Remote_Folder' is denied.(mscorlib) ***" ---- even when my domain user have all permissions in both folders and is 'sa' in both SQL instances.
I have read about to create a special "system credential" but Im not sure about that... ¿do you have another solution for this issue?
I will appreciate your comments,
October 24, 2011 at 2:59 pm
Does SQL server agent in both instances “Remote and local” using the same service account or different services account.
October 24, 2011 at 3:03 pm
check the share permissions as well as the NTFS permissions. A share defaults to Everyone - Read!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 3:18 pm
I use the same Domain/user for all jobs because also is the 'sa' en both SQL instances and additionally for each folder (OS) I have set "Share Permission" and "Security" with "everyone" - Full control but I get the same error.
October 24, 2011 at 7:14 pm
SQL server agent service accounts must have read and write privileges on the share.
October 24, 2011 at 9:22 pm
I have a default installation in both SQL instance and I have granted permission in each folder for my domain user and "Domain Administrators".. ¿what user I would to grant permission in folder? All installation run by default. Thanks.
October 24, 2011 at 10:10 pm
The account by which SQL Server Agent is running should have the read write permission on the secondary server.
This error generally comes when you do not have read write permission on Secondary server.
So check whether your account is having read write permission.
October 25, 2011 at 9:25 am
¿How can i see the user is running the SQL Server Agent? from Enterprise Manager the properties just have SQL server connection - Windows Authentication.
However in SQL Configuration Manager this services have - Log On as "Local System"
¿What user i need to grant in remote folder to do the copy of trn files to my local server? My logshipping process stop just in this Job when try to copy files to my local server and next execute the restore.
I have granted to my Domain/user (member of Domain Administrator) and Administrators but it seems not correct.
thanks.
October 25, 2011 at 3:27 pm
Give “Read and Write” permissions on the share for this account "Local System"’ (based on your post it looks that is what your SQL agent is running under). Check your SQL agent is running as well.
Quick question, are you log shipping to the same server or you have primary and secondary server.
October 25, 2011 at 3:35 pm
You must run SQL Agent of at least the secondary server (but preferably both) under a service account - that is a domain user account. That type of account will be able to access resources over the network.
Local system will have full rights on the local server, but no rights at all over the network.
---------------------------------------------------------------------
October 26, 2011 at 8:13 am
When setting up log shipping, the SQL Server Agent SERVICE owner needs to be a login with enough rights on the other server to be able to copy files to the secondary location. This is usually done with a dedicated Windows Domain service account as the owner of the service. See -> Start -> Administrative Tools -> Services to see the Service information.
October 26, 2011 at 11:36 am
Ok. I did that, I have created the "service account" as a part of my domain and member of "Administrators" next, I have added the user in the service properties of SQL Server Agent (MSSQLSERVER) - "Log On" and now the JobCopy works fine BUT in the last job when try to restore the TRN backup (log) I get this error..
*** Error: The restore operation cannot proceed because the secondary database 'DBNAME' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) ***
¿Do you know how can i see the mode in this db and how can I change it?
October 26, 2011 at 11:50 am
The error message about not in recovery mode is telling you to start again :(.
In log shipping the secondary database is usually either in standby, ( users can access the database in read only mode) or in recovery mode, ( database is not accessible to users, but can restore more logs ).
If the database is ever restored and online, which yours now seems to be, you cannot restore any more transaction logs to it. Log shipping is a process of constantly restoring logs to a database which is never brought online until log shipping is stopped because the database is online and no more log files can be restored.....
In my production log shipped database, I copy the logs every 15 minutes so that they are in a location that they can be restored from, (standby mode for database with users running reports and queries) and then I restore logs from 22:00 - 06:00 hours ( when logs are restoring database is not accessible)
October 26, 2011 at 3:50 pm
Thanks for everything, the logshipping process work fine now and all suggestions was very usefull. I just made a new backup from source db (remote instance) and applied a restore in destination (local instance) in "NORECOVERY mode" according to this document:
http://support.microsoft.com/kb/301049
Now the destination DB is in "StandBy/Read-Only mode" and all transactions log are being applied according to the schedule.
¿What do you do when you need to leave ready to use it this database in destination? imagine the source instance fall down.
Thanks,
October 26, 2011 at 4:04 pm
Bring the database online.
apply the last log available to you with the recovery option, or just run
restore database mydb with recovery
You may also need to resynch logins, activate agent jobs, whatever else was defined for the database at server level (these are not automatically copied across by logshipping and need to be handled separately)
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply