Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have an mdf file of sql server 2005 database now i want it to attach in sql server 2008 R2 but when i try to attach it, it gave me error saying.

Unable to open the physical file "D:\Fraz\Freelance\Database\DBmdf13aug\mbh_pk.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

what can be the problem and how to fix it? Is this folder permission error or sql server 2008 have something missing

share|improve this question
    
Have you ever attached this db before? Go to the parent folder, and reset permissions on the subfolders and files and try again. –  DanBig Aug 13 '10 at 18:29
    
i never attach it before. i have just copy the mdf file which is attach in sql server 2005 and now im trying to attach it in sql server 2008 –  Fraz Sundal Aug 13 '10 at 18:40
    
Have you detached it from MS SQL Server 2005? –  Gennady Vanin Novosibirsk Aug 13 '10 at 18:44
    
If the file is still attached by another database engine, you will not be able to do anything with it. You must detach it from 2005 first. –  DanBig Aug 13 '10 at 18:45
    
i have stop the sql server 2005 and then copy that mdf file, after that i uninstall sql server 2005 and install sql server 2008 and now i want to attach that mdf to sql server 2008. –  Fraz Sundal Aug 13 '10 at 18:53
show 2 more comments

7 Answers

I think we'll need a bit more info about the server to help properly, but have you tried the basics? e.g.

  • Is the VNC server service installed and configured correctly with no errors logged when it runs?
  • can you ping from the client to the server?
  • Is there any firewalls that could be blocking the VNC traffic in the mix?
share|improve this answer
add comment

Usually this means that the account that SQL Server runs under does not have access or permissions to this location

share|improve this answer
    
then how to grant privileges? –  Fraz Sundal Aug 13 '10 at 18:04
add comment

The account which is running the SQL Server instance doesn't have rights to the physical file. Fix the NTFS permissions problem then either bring the database online with...

ALTER DATABASE mbh_pk
SET ONLINE

or restart the SQL Instance.

The account running the SQL instance needs to have modify control of the files or higher.

share|improve this answer
add comment

Just answered a similar question here. Hope it helps.

Cheers Chirag

share|improve this answer
add comment

Probably just a folder permissions issue.

Make sure the SQL service account user has full rights to the path of the file. Or copy (don't move) the file to the default data folder.

share|improve this answer
add comment

Ideal would have been if you had taken a backup of the database before uninstalling SQL 2005 then you could have just restored the database from the backup. It is trickier now and might cause problems.

Try this and see if it works for you, this should rebuild the log file for you. Please replace the DatabaseName, Filepath and FileName as per your requirement.

CREATE DATABASE *[DemoSuspect]* ON
   (NAME = DemoSuspect, FILENAME = *N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf*')
FOR ATTACH_REBUILD_LOG;
GO 
share|improve this answer
add comment

Start MS SQL Server 2005, detach database there before copying, then in MS SQL Server 2008 attach copied mdf file.

Update: You really cannot remove MSSQL Server components completely by uninstall process, especially having attached databases left behind. Besides server components, MSSQL Server setup can install client tools libraries, BIDS (Business Intelligence Development Studio), MDAC, etc.

MS SQL Server database can be used (and locked) not only by MS SQL Server. The lock can be initiated, hold and passed through from many tools.

I can only advise you to iteratively check/clean Windows (for ex., by CCleaner) registry, then manually remove files remaining on hard disk from MSSQLServe2005, then repeat both steps again.

But, my qualified guess is that you screwed it by uninstalling SS2005 before migrating or, at least, detaching database.

Really, I wouldn't advise attaching/detaching even without migrating to another version. What was the necessity to do it?

Also, why do not you copy also LDF file?

share|improve this answer
    
-1 Fraz has already mentioned that he uninstalled SQL 2005. Plus, you don't need to detach to attach to a different instance. –  squillman Aug 13 '10 at 19:16
    
I advised from my many-years of practical experience. I can advise, in this case, only to try manually remove all files left behind from MSSQL2005 and clean registry –  Gennady Vanin Novosibirsk Aug 14 '10 at 5:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.