How to detach a sql server 2008 database that is not in database list

sql-server-2008windows 7

I installed SQL Server 2008 on Windows 7. Then I created a database. After 2 days I reinstalled Windows and SQL Server.

Now I am trying to attach my database file, but I have encountered the error below.

I think that the files are like an attached file and I can't attach them. What is difference between an attached file and a non-attached file? How can I attach this file? Please Help Me.

Error Text: TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'AMIR-PC'.
(Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

—————————— ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file "F:\Company.mdf". Operating system
error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

Best Answer

If you're getting that error then your database is not attached and thus won't be in the list of databases on your server instance. And thus, since it is not attached to begin with, it cannot be detached.

The account that your SQL Server instance is running as does not have the file system permissions to access the data file you want to attach. You need to change the permissions on the data file before you can attach that database.

During SQL Server installation several groups are created so that security is set on SQL Server filesystem resources appropriately. Most likely the permissions left on F:\Company.mdf are related to the accounts / groups from your previous installation of Windows. Now that you've reinstalled Windows and SQL Server these accounts have been recreated with new security identifiers (SIDs). Even if the new new accounts / groups are named the same as they were in your previous installation the SIDs will be different and the SID is how a security principal is identified.

Reset the permissions on F:\Company.mdf and the corresponding log (.ldf) file (maybe it's F:\Company.ldf?) and then try to reattach the file.

Related Topic