September 28, 2012 at 5:42 am
Hi
My windows ID has admin rights on the sql server but I am not the administrator of the server. Now there is database of which I am owner. but the mdf and ldf files of this database are in a directory to which I don't have access, means I can't copy those files if database is detached.
is there a way to override OS permission through Sql server so that I may copy those files?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
September 28, 2012 at 5:47 am
I would speak to the network admin about obtaining rights as those folders are locked down for a reason, and either ask someone with the correct privlages to copy them or make a case of having permissions to those folders.
Out of curiosity why isnt a backup/restore a valid option?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 28, 2012 at 7:00 am
we just want to shift files from one drive to another with larger space. So attach/ detach looks simpler...
Jason-299789 (9/28/2012)
I would speak to the network admin about obtaining rights as those folders are locked down for a reason, and either ask someone with the correct privlages to copy them or make a case of having permissions to those folders.Out of curiosity why isnt a backup/restore a valid option?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
September 28, 2012 at 7:14 am
Fair enough, just curious why the backup wasnt an option, and a detach in this situation is the best way of doing it.
I would still find out why access is restricted to those drives/folders, as it sounds more like a security measure than anything else.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 28, 2012 at 12:08 pm
Wouldn't an ALTER DATABASE operation be better for this?
http://msdn.microsoft.com/en-us/library/bb522469.aspx
You would still need access to the drives where the files are located.
October 1, 2012 at 4:33 am
Well one reason is tha tI don't want users to enter any data in this database while I am moving it. I know there are other ways to achive it but attach ditch just does all I needned to achive.
S_Kumar_S (9/28/2012)
we just want to shift files from one drive to another with larger space. So attach/ detach looks simpler...Jason-299789 (9/28/2012)
I would speak to the network admin about obtaining rights as those folders are locked down for a reason, and either ask someone with the correct privlages to copy them or make a case of having permissions to those folders.Out of curiosity why isnt a backup/restore a valid option?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
October 1, 2012 at 8:18 am
You could create a SQL Account with sysadmin permissions and use this to do the detach/attach. It should work if the service account has host admin priviledges but it is a work around to circumvent security.
The correct way should be to get the server admin to grant you permissions to the required folders.
October 1, 2012 at 10:03 am
Sql server should have permissions to copy the file, so xp_cmdshell.
My approach for moving databases:
1. Alter DB set single_user rollback immediate
2. Alter db set offline
3. xp_cmdshell to copy mdf/ldf to new location, if error, goto 6.
4. xp_cmdshell to rename old files from data.mdf to data_old.mdf
5. Alter DB to point to files on new drive.
6. Alter db set online
7. Alter db set multi_user
October 3, 2012 at 3:59 am
Exactly what I did, but the copy permission was not there 🙂
SpringTownDBA (10/1/2012)
Sql server should have permissions to copy the file, so xp_cmdshell.My approach for moving databases:
1. Alter DB set single_user rollback immediate
2. Alter db set offline
3. xp_cmdshell to copy mdf/ldf to new location, if error, goto 6.
4. xp_cmdshell to rename old files from data.mdf to data_old.mdf
5. Alter DB to point to files on new drive.
6. Alter db set online
7. Alter db set multi_user
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
October 3, 2012 at 3:59 am
I second your thoughts. Correct way is to get permissions and I have already asked for that.
MysteryJimbo (10/1/2012)
You could create a SQL Account with sysadmin permissions and use this to do the detach/attach. It should work if the service account has host admin priviledges but it is a work around to circumvent security.The correct way should be to get the server admin to grant you permissions to the required folders.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply