October 28, 2008 at 7:52 am
I am currently studying for my SQL server exams. I am currently going though McGraw Hill.MCITPDatabase.Developer.All.in.One.Exam.Guide. I am currently doing an exercise on log shipping. I have created 2 instances of SQL Server on my home pc. I have set up 2 folders called LogPrimary and Log Secondary. LogPrimary is the folder for the primary database back up and LogSecondary is folder where the back up is restored to. I have shared both folders giving permissions to everyone and the user group. When I click ok to begin log shipping the primary database is backed up fine. Then an error fires from sql management studio saying the following:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\LogPrimary\AWLogShipping.bak'.
I am currently using SQL server 2005 Evaluation edition. Could anybody give some info on why this is happening and a possible resolution.
October 28, 2008 at 8:10 am
eseosaoregie (10/28/2008)
I am currently studying for my SQL server exams. I am currently going though McGraw Hill.MCITPDatabase.Developer.All.in.One.Exam.Guide. I am currently doing an exercise on log shipping. I have created 2 instances of SQL Server on my home pc. I have set up 2 folders called LogPrimary and Log Secondary. LogPrimary is the folder for the primary database back up and LogSecondary is folder where the back up is restored to. I have shared both folders giving permissions to everyone and the user group. When I click ok to begin log shipping the primary database is backed up fine. Then an error fires from sql management studio saying the following:Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\LogPrimary\AWLogShipping.bak'.
I am currently using SQL server 2005 Evaluation edition. Could anybody give some info on why this is happening and a possible resolution.
It could be permissions related (that's my first instinct).
You say that you shared C:\LogPrimary\ and you gave permissions to "everyone". In addition to giving share permissions you have to also give NTFS permissions (at the folder level) to the account that runs the SQL Server services on the secondary server. Also, make sure that the owner of the jobs is "sa" - this is just to make sure that the jobs run under the context of the service account.
October 28, 2008 at 9:12 am
Could you explain briefly how I give NTFS permissions (at the folder level) to the account that runs the SQL Server services on the secondary server. What is NTFS permission? Also how do i ensure that the owner of the jobs is "sa"?
October 28, 2008 at 9:26 am
eseosaoregie (10/28/2008)
Could you explain briefly how I give NTFS permissions (at the folder level) to the account that runs the SQL Server services on the secondary server. What is NTFS permission? Also how do i ensure that the owner of the jobs is "sa"?
NTFS permissions relate to the permissions needed to access NT File System files and folders. In addition to granting share permissions on a share you also need to make sure that users have direct access to the folders directly on the computer.
I'm assuming you've already setup the shares and security on the shares so I'll just go through setting up NTFS permissions on the folder:
1. Browse to the C:\ drive on the machine.
2. Locate the LogPrimary folder, right-click on it and select Properties.
3. Click on the security tab.
4. Click Add.
5. Enter the name of the account that is running the SQL Server services.
6. Click OK, and then Click OK again.
To ensure the job owner is sa:
1. Locate the log shipping jobs created by the wizard and double-click to open the job.
2. Check the owner field is: sa.
3. If not, change the owner and click on OK.
Hope that helps.
October 28, 2008 at 9:50 am
Many Thanks. It works now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply