May 13, 2010 at 8:34 am
I have a stored procedure that is intended to create a clone of an existing database. We have a requirement that all of our client's data be stored in distinctly separate databases.
My stored procedure consists of four steps:
1. I detach the master database to be cloned
2. I want to then copy the MDF and LDF files to a new folder using xp_cmdshell and the DOS copy commands
3. Attached the new cloned (copy) to the server
4. Re-attach my master database to the server.
For some reason that I cannot figure out, when I try to execute the xp_cmdShell, I always get an "Access Denied" message on both file copy commands. The strange thing is if I just detach the database then via a DOS command in the CMD window, I can copy the files with no problem at all.
Can someone tell me why I cannot copy the files using the xp_cmdShell? Is there some sort of lock still on the MDF/LDF files even though the database is logically detached from the server?
Here is a snippet of the command I am using to copy the file(s)
SET @Cmd = 'COPY C:\Code3Billing\ClientSQLData\Master\C3SBillingCore.mdf C:\Code3Billing\ClientSQLData\Clients\' + @newdb + '.mdf'
EXEC xp_cmdshell @cmd
@newdb is a varchar parameter passed to the stored procedure with the name to be used for the new database.
Thanks in advance to all who reply.
Bryan
May 13, 2010 at 8:39 am
does the account that you are running sql service under have write permissions to that folder?
May 13, 2010 at 8:43 am
The connection is a trusted connection and the folders where the files are located have full control permissions for Administrators, myself, System, and users on the PC. For some reason I cannot set permissions to the Creator/Owner group/user name
May 13, 2010 at 8:52 am
Sorry, I meant the account that is running SQL server needs to have permissions to the folder.
are you running SQL server on a domain account ?
May 13, 2010 at 8:55 am
I am running SQLExpress on my local PC. I checked in Services for the SQL Server properties and the login is the Local system Account which I presume is me.
I am connected to a domain network but my application is purely local and not using any network resources.
Sorry if I sound a bit dumb, but I am pretty new to SQL server and not an expert.
May 14, 2010 at 8:23 am
Local system account in special account just for SQL server to run, it is a powerful account that should have admin rights to all the folders.
Ideally you should use a windows network account to run SQL server but as you are running this locally the localsystem account 'should' work, perhaps you could try using a specific account that you know has permissions to run sql to see if this helps.
When you change the accounts that SQL runs under you should always do this under SQL configuration manager and not through windows services, as there are changes that need to be made that the service manager can not make.
May 14, 2010 at 8:40 am
I did some digging around and found a tip that suggested that I need to have serveradmin rights in order for the copy to work.
I assigned the ServerAdmin role to my SQL login name and lo and behold it appears to have solved my problem.
Thank you for your help.
May 14, 2010 at 8:44 am
Okay i was unaware of that thanks for sharing the solution and I am glad that you managed to get your problem solved.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply