• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • 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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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