jay-h (4/15/2015)
arrghhhDetails:
Sql2008 R2
Windows 2008 R2
VM
Need to move files to different drive
Detatched successfully. However I physically cannot copy the files to the new folder, continuously getting "You need administrative permissions" (interestingly sometimes it tells me that I need my permission to move this file).
I'm Administrator on this server and the SQL instance. I changed ownership of the files to myself, but that did not help.
Command line copy/move also got 'access denied'
Other, non-SQL files copy freely between the folders. I have full rights on both folders and all the files.
Tried logging into the server using SQL server's user AD account. Still could not copy/move.
My boss also tried the copy and encountered the same issue. I'm not sure if this is an SQL issue or a windows issue.
Don't!
The company I work for now, used to use this method to move databases and SQL files. As mentioned already, the ACL and NTFS permissions may change which makes difficult to re-attach.
What I do instead if putting the database offline, edit the metadata via TSQL, and move the ndf or mdf files. Then put the database back online. This retains permissions, statistics, and other important information already saved inside the database and MSSQL.
Last but not least, sp_attach is deprecated in SQL2014 so it may be a good idea start getting yourself more familiar with a different approach.