Link to home
Start Free TrialLog in
Avatar of Rick Mills
Rick MillsFlag for United States of America

asked on

Can I edit a MDF file?

I have a client with a SQL-based application.  They are running SQL 2005 Express on a WindowsXP workstation (it wasn't my installation, just my mess to try to clean up).  One of the data files stores pictures and it has reached the 4GB limit.  I cannot open the application, as it errors out, so I can't delete records that way.
The application vendor has quadrupled their maintenance/support fees, so the client has not renewed.
The data file with the pictures is not critical data.
Is there any good way to be able to delete records from this file?
If not, is there any good way to "refresh" the data file, delting its contents?
Avatar of Greg Jacknow
Greg Jacknow
Flag of United States of America image

You can't edit an MDF file.
You should be able to copy it to a SQL 2005 std/EE server and add the database to the server using that file.
Avatar of Rich Weissler
Rich Weissler

I assume the error is coming from the 4GB limit imposed by SQL 2005 Express.

First, make a copy of the MDF file, and work from the copy.  Last thing you want to do it corrupt your database.

I'm not aware of a non-SQL Server tool that would allow you to open the file to clean it up.  I can think of two possible avenues off the top of my head though.  You can obtain another version of SQL 2005 (Standard or Workgroup or maybe Developer for example), which don't have the limit.  Or, if price is a concern, get the SQL 2008 Express edition which has a 10GB limit... and should be backwards compatible if you leave the database in SQL 9 compatibility mode.  (Note once again, work with a copy of the database -- I've never tested backwards compatibility by actually trying to attach a database to an older version of the software.)

Once you are in, you should be able to manipulate it as you like.
Avatar of Rick Mills

ASKER

Thanks for the quick response, guys.
I will be working from a copy (have already made multiple backups).
I will try editing the file on my SQL Server back to my office and see what comes of it.  I'll let you know the results and I'll leave this open in case anyone has a miracle up their sleeve for me.
>>SQL 2008 Express edition which has a 10GB limit<<
You may want to double check that...
> You may want to double check that...

http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

I haven't loaded/built a 10 GB in Express... but unless Microsoft is lying.  *shrug*
*sigh*  I shouldn't post before drinking my coffee... meant to include this image.
SQL2008Compare.JPG
Okay, acperkins might be right.  The page here: http://www.microsoft.com/sqlserver/2008/en/us/express.aspx and in the product datasheet, it lists in the limit as 4GB.
(But there is a second chart on the website which also lists the limit as 10GB.)
I've opened communication with Microsoft, to get an answer on which is correct.
>>but unless Microsoft is lying.<<
Why don't we just call it a typo? It is less confrontational.  If this is the first documentation error you have discovered in MS literature you have not been around very long.
Need some more help.
I've copied the .MDF file to my SQL server.  I do not know SQL very well.
I run SQL Server Management Studio and login using Windows Authentication.  When I attempt to open the MDF file that I copied to my server, it tells me "The operation could not be completed.  Access Denied."  Any suggestions on how to get around the security issue?  I'm not sure whether they use Windows or SQL Authentication on their server, but can find out Tuesday.
can you explain more how did you copy the MDF file over?  I presume that you are copying the file over a newly installed SQL server.
>>I've copied the .MDF file to my SQL server. <<
What about the .LDF file?  You will also need that.

>>I do not know SQL very well.<<
Don't take this the wron way, but you should find someone who does.

>>When I attempt to open the MDF file that I copied to my server, it tells me "The operation could not be completed.  Access Denied."  <<
You do not open an MDF file.  This is not MS Access. You attach the database (.MDF file(s) and .LDF file(s).  Notice the potential plural) to your server and then connect to it.

>>Any suggestions on how to get around the security issue?<<
It is not a "security issue".  See above.
No offense taken.  I have attempted to find someone local, but have not been successful.  I don't know if this is appropriate for Experts Exchange, but if someone can help me remotely and/or via phone, I would be happy to pay for the assistance.  My goal is to get this up and running by tomorrow morning.  This company is a recycling business and they are going to be slammed Tuesday morning following a 3-day weekend.
To answer the other questions, I have a SQL 2005 server running Connectwise.  I copied the entire DATA folder, including all MDF and LDF files, from the client's SQL Server (which is an XP Pro desktop) to a folder on my SQL server.  Can you provide some more info on attaching the database?
Let me make sure I explain my ultimate goal.  There is a single MDF file that is 4GB.  It contains nothing but pictures.  These pictures have very limited value and it is my goal to either delete all records over a certain age or simply delete the entire contents of the database, so they client can begin using the application again.
>> I don't know if this is appropriate for Experts Exchange, but if someone can help me remotely and/or via phone, I would be happy to pay for the assistance. <<
Unfortunately it is not.  The best you can do is contacting people directly by getting their email from their profile.

>>I copied the entire DATA folder, including all MDF and LDF files, from the client's SQL Server (which is an XP Pro desktop) to a folder on my SQL server. <<
That is good.  Assuming that your database only has one MDF and one LDF, then you should then be able to attach the database as follows from the query window:

EXEC sp_attach_db @dbname = 'YourNewDatabaseNameGoesHere',
    @filename1 = 'TheFullPathToYourMDFDataFileGoesHere',
    @filename2 = 'TheFullPathToYourLDFDataFileGoesHere'

Here is an example from BOL:
EXEC sp_attach_db @dbname = N'AdventureWorks',
    @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf',
    @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_log.ldf';

Once you have it attached you can do whatever you need to the database.
acperkins,
Thanks.  I now have access to the file.  How can I view and delete records within the database now?
I also sent you an email.
Can you tell me how I can delete one field for every record in the database?
>>I also sent you an email.<<
I am afraid that is also covered in the Guidelines, so I will have to ignore it. See here:
About using email to solve your problem
The use of email to resolve a question at Experts Exchange is prohibited, because it gives the emailing Expert an unfair advantage over the other participants in a question. The same applies to the use of Remote Access to resolve a problem. Points cannot be awarded for a solution that is not posted on the site.

A related issue is the posting of email addresses in questions (if you want to post your email address in your profile, that is allowed.) The only people at Experts Exchange who have access to your email address are the office staff and the Administrators -- the Moderators and Zone Advisors do NOT have access to it. By posting your email address, you are subject to receiving email from anyone. The Moderators and Zone Advisors will automatically remove email addresses from questions.

>>I now have access to the file.<<
If I may correct you.  You now have access to the database.

>>How can I view and delete records within the database now?<<
You can view and delete rows with the tables of the database by using the SQL language.  For example:
SELECT *
FROM YourTable

Will display all the columns ad all the rows in a given table (and yes for performance reasons SELECT * is not as efficient as naming the columns, but this is an ad-hoc query)

>>Can you tell me how I can delete one field for every record in the database?<<
Not sure what you mean here.  There are columns (aka fields) and rows (aka records) in tables (not databases)

acperkins,
Thanks for pointing out the guidelines.  I did not intend to bypass the rules, but haven't taken the time to review them in detail.  Guess I should do so.
OK, with your help I've been able access the database and delete all records in the table that contained over 3GB of pictures.  The pictures are not necessary and we simply want to delete them.
I've been able to delete them using the TRUNCATE TABLE command.  I can close the database, re-open it and query all records (SELECT * FROM table) and it shows no records.
However, the size of the file on my hard drive is still 4GB.  How can I compact the database to reduce the size of the file?
>>How can I compact the database to reduce the size of the file?<<
First make sure it is in Simple Recovery Model.
ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;

Next shrink the databases:
DBCC SHRINKFILE('The data logical file name goes here', 3.5)
I apologize for my lack of SQL knowledge, but what is the "data logical file name"?
I've tried a variety of possibilites, but continue to get "parameter2 is incorrect for this DBCC statement."
It is not a problem.  You can find out the logical names this way:

SELECT DB_NAME(database_id), name
FROM sys.master_files
WHERE DB_NAME(database_id) = 'YourDatabaseNameGoesHere'

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I played around a bit and found the Shrink option in the GUI.  My 4GB file is now just under 1GB.
Thanks so much for your help and your patience.