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.
LVL 10
vadim63Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nmcdermaidConnect With a Mentor Commented:
Does the sql server agent service account have enough permissions to copy the file?
0
 
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
CharliePete00Commented:
Have you tried using the Copy database wizard in Management Stdio instead?
0
 
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
 
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.

All Courses

From novice to tech pro — start learning today.