February 28, 2012 at 7:16 pm
Hi Everyone,
I am trying to move my DB data files into different drive. I used Alter DB and Modify File Commands. But After I changed the Location of File. I conformed by looking in sys.master_files table to see if the location has been changed. It did change. But The problem is when I am trying to make the DB back to Online I get an error. It Says The DB files are Inaccessible. I just looked at the Service account of the Database Service. Its running on LOcal system account which has access to all the drives. But Still the error. I know its the issue of permissions. If at all my issue is that Local system account doest not have permissions how do I give that service account permissions. I am running this on my local PC. Its not a server of any kind. The exact error is as follows.
Set offline failed for Database An exception occurred while executing a Transact-SQL statement or batch. Unable to open the physical file Operating system error 3: "3(The system cannot find the path specified.)". Database 'IndexTest' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5120)
THANK YOU IN ADVANCE FOR THE HELP
February 28, 2012 at 11:11 pm
have you checked data and log files are exist in new location? if not copy it manually and try to put database online.
February 29, 2012 at 8:49 am
Yeah I did that to. After changing the location in catalogue I did change the files physically. Still the problem exists. I am not able to get the db back online. When I try to is says either inaccsible files or insufficient memory on disk. I am 100% sure its not a memmory issue.
I just dont get it.
How do I check if the service acct has access to the drives?
February 29, 2012 at 10:45 am
There should be a group for the service account. Go into the Computer Management, Users and Groups, and Groups. The group for the SQL Server database engine service should be granted explicit rights to the new folder
February 29, 2012 at 11:01 am
Double check that the path in the system tables and the actual path is identical and that the SQL Server service account has full permissions to the folder.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 29, 2012 at 4:21 pm
na1774 (2/28/2012)
Hi Everyone,I am trying to move my DB data files into different drive. I used Alter DB and Modify File Commands. But After I changed the Location of File. I conformed by looking in sys.master_files table to see if the location has been changed. It did change. But The problem is when I am trying to make the DB back to Online I get an error. It Says The DB files are Inaccessible. I just looked at the Service account of the Database Service. Its running on LOcal system account which has access to all the drives. But Still the error. I know its the issue of permissions. If at all my issue is that Local system account doest not have permissions how do I give that service account permissions. I am running this on my local PC. Its not a server of any kind. The exact error is as follows.
Set offline failed for Database An exception occurred while executing a Transact-SQL statement or batch. Unable to open the physical file Operating system error 3: "3(The system cannot find the path specified.)". Database 'IndexTest' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5120)
THANK YOU IN ADVANCE FOR THE HELP
Error 5 is access denied, error 3 is path does not exist
As Gail said, read sys.master_files and double-check and confirm the paths you are copying the files to.
As Steve has said check the local group for the SQL server 2005 instance has permissions to the folder where the data files are placed. right click the folder and select the security tab, what entries do you see there?
Also right click each file and select the security tab, what permissions ACLs do you see there?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply