[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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