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

Problem with a Job (detach - copyto - attach)

I have a job (SQL 2005, Win Server 2003 Ent) with a 3 steps:

1. EXEC sp_detach_db 'MyDB'

2. xcopy "C:\Data\*.*" c:\Backup\ /y/s/e/q/h/r               - all credentials and proxies are fine

3. EXEC sp_attach_db 'MyDB', 'C:\Data\MyDB.mdf'

First and third steps work fine, but 2nd steps gives an error "Access denied"
What I've found yet:
after 1st step the file MyDB.mdf has the only 'System' permission left;
so, the 2nd gives an error;
after 3rd step - all permissons are back (I mean 'Admins', 'Users' etc)

Help, please.
0
vadim63
Asked:
vadim63
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Make sure that the user with which u connect to sql server has enough permission to move the data
0
 
Eugene ZCommented:
<I have a job>
is it sql server agent job?

Make sure it own\ run by 'sa'
0
 
CharliePete00Commented:
Have you tried using the Copy database wizard in Management Stdio instead?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
vadim63Author Commented:
Yes, It is sql server agent job, it own\ run by 'sa' and user has 'admin' permissions with a full control. But, as I stated above, after sp_detach_db all permissions (exept 'System') are gone. And I can get it back after sp_attach_db only. Any ideas?
0
 
nmcdermaidCommented:
Does the sql server agent service account have enough permissions to copy the file?
0
 
vadim63Author Commented:
Sorry for the delay. I was out of country. Nmcdermaid is right. It was a permission issue. Thank you.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
@vadim63,

I hope you didn't see my post :(
0
 
nmcdermaidCommented:
Might be some miscomunication here. Strictly speaking it's not the user that connects to SQL Server that needs rights its the SQL Agent.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
hmmm, i really mean that , Seems like i didn't type that ..
0
 
nmcdermaidCommented:
hehe I know the feeling :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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