October 12, 2009 at 4:22 am
SQL 2008 SP 1 server.
We've got some DBs on here that were restored from SQL 2000 backups.
They were running fine.
I've recently detached them OK but when I try to re-attach I get the message below.
Detach/attach works fine for SQL 2008 DBs on the same server in the same directory.
SQL running under a domain user account with full Windows Admin and SQL admin priviledges.
MS link mentioned returns nothing.
Any ideas much appreciated folks ?
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 1815) while attempting to open or create the physical file 'E:\SQLData\FAS_UAT_SIF.mdf'. (Microsoft SQL Server, Error: 5123)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476
October 12, 2009 at 4:25 am
What was the compatiblity level of the databases before you deattached them and did you do the usual dbcc checks on the attached databases after you attached them.
I take it as well, that the physical location for these databases does exist on this server?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 12, 2009 at 6:18 am
Operating system error 5 is Access Denied. It means that the SQL Server service account does not have full permission on the directory or the SQL Server Service account does not have full permission on the file. Check the NTFS permissions for both the directory and the mdf and ldf files, make sure whatever account SQL is running under has full (read and write) permission on all.
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
October 12, 2009 at 6:22 am
p.s. you are trying to attach them back to the SQL 2008 instance?
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
October 12, 2009 at 10:40 am
FYI - when you detach a database from SQL Server 2005 or greater, the file level permissions on the mdf/ldf file are changed to the user to detached the file.
If you later try to attach the file - SQL Server cannot attach the file because the service account does not have privileges.
Reset the file permissions and you will be able to attach the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 12, 2009 at 3:36 pm
Thanks for the replies folks.
The SQL Service user is in the Admin group on the server so should have ample rights ?
I can't attach the mdf to run any dbcc on it.
@Jeffrey I'll try that (just do it in Windows Explorer ?) I couldn't even delete the mdf and ldf - even after stopping the SQLServer service.
@Silverfox DB Compat level was set for SQL 2000. And yes they exist. In fact they super exist (see above !)
@gilamonster I'm trying to attach back to the SQL 2008 instance alright.
October 12, 2009 at 3:56 pm
Yes, check the permissions for the mdf/ldf files and grant access back to the Administrator. You will find that the only one who has any permissions to the file is the account that was used to detach the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 13, 2009 at 1:15 am
Joseph Fallon (10/12/2009)
The SQL Service user is in the Admin group on the server so should have ample rights ?
Not necessarily. It's possible to deny to an administrator
@Jeffrey I'll try that (just do it in Windows Explorer ?) I couldn't even delete the mdf and ldf - even after stopping the SQLServer service.
Last time I ran into this I had to first take ownership of the files (anyone with local admin can do that) and then go in and reset the permissions.
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
October 13, 2009 at 1:53 am
I could kiss this forum ... seriously 🙂
Changing the permissions seems to have done the trick.
Is this a new "feature" in 2008 I wonder ?
Thanks people - you're the best (and I really mean that)
October 13, 2009 at 3:11 am
Joseph Fallon (10/13/2009)
Is this a new "feature" in 2008 I wonder ?
No. It was there in 2005 as well. That's where I ran into it.
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
March 30, 2012 at 12:29 pm
Gail:
This issue just came to my attention when trying to attach a database on a SQL Server 2008 R2 server.
Usually I am restoring databases from a backup instead of attaching the files .... so I had not looked closely at the permissions of the data & log files after a restore. But after seeing this I checked several other filesystems and see that every database that has been created by restoring a backup has the files owned by the person who did the restore.
I don't want to leave all of these database files owned by a "person" .... so my question is: for best practice what user should own the files and folders for the database data & log files? Would it be the server administrator or the domain user that runs the SQL server service?
Thank you for your good advice and opinion.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply