2

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

  • 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 Геннадий Ванин 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
1

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?
1

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

  • then how to grant privileges? – Fraz Sundal Aug 13 '10 at 18:04
1

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.

0

Just answered a similar question here. Hope it helps.

Cheers Chirag

0

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.

0

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 
-2

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?

  • -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 Геннадий Ванин Aug 14 '10 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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