• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 869
  • Last Modified:

MDF File Association missin

Somehow I managed to disassociate the .mdf file extension with a SQL Server Database.  I have not been able to find anywhere to restore the association.  I have checked on other computers and they all indicate that the program is "unavailable"

This becomes a problem when I am trying to re-attach a database for upgrade purposes from 2000 to 2005 as it will not recognize it as a valid file format.

Do I have to go back into 2000 and script everything out in order to bring it into the higher versions of SQL?  I was hoping for a faster cleaner upgrade and not losing any of my table settings -- mostly my default field settings for the 75+ tables that are there.

My overall goal is to move everything over to SQL 2008 and my ADP's to 2007.  Anyone have suggestions?
0
JasBrad
Asked:
JasBrad
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>This becomes a problem when I am trying to re-attach a database ...

to reattach, you don't need file associations.
you just "attach", either via the sql server gui (context menu of the database folder), or the stored procedure sp_attach_db.

0
 
reb73Commented:
What do you mean, .MDF is not being recognized as a valid file format? Is the file a valid MDF that was properly detached from the 2000 server??

.MDF files are not associated with any explicit applications as such, provided the content is valid, SQL Server will be able to use the file when attached..

Have you tried backing up the database in SQL 2000 and restoring the backup to 2005/2008?
0
 
JasBradAuthor Commented:
I detached the database from one server, copied it to my laptop which has Vista Ultimate and SQL 2005.  Vista Ultimate does not show that  an mdf is a SQL Database file.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
reb73Commented:
Open the SQL Server Management Studio, connect to your local 2005 server on Vista In the dialog box displayed, Click 'Add' and browse to the MDF file to attach..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Vista Ultimate does not show that  an mdf is a SQL Database file.
as mentioned, it does not matter. attaching the file to sql server is what matters.
0
 
Anthony PerkinsCommented:
>>I detached the database from one server, copied it to my laptop which has Vista Ultimate and SQL 2005. <<
I trust you did not miss copying the LDF as well or it will fail.
0
 
JasBradAuthor Commented:
Yes, I have done all of the above as well and I receive the following error message

TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'JAYSONBEE5\JAYSONBEE5'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&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 "C:\DANBSQL_DATA\MedMaint_Data.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.00.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476 
------------------------------

After getting the error message, I have tried the help links and they have no information and I cannot seem to find anything else understandable on the internet..
I havetaken the assumption that my databases originally had errors, so I went back to the originals and verified them to start all over again.  I still get the same message which is why I was assuming that it had more to do with the file association.
0
 
reb73Commented:
Check the NTFS permissions on the .MDF file (not just the folder containing the .MDF file) and take ownership of the file, if necessary before attempting the Attach operation.

Also review the following link to the section with information on Detach/Attach -

http://msdn.microsoft.com/en-us/library/ms189128.aspx
0
 
JasBradAuthor Commented:
Changing the permissions worked like a charm.  Thanks!!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now