Solved

Can I edit a MDF file?

Posted on 2010-09-03
23
1,522 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:rickmills
  • 9
  • 8
  • 4
  • +2
23 Comments
 
LVL 5

Expert Comment

by:Greg Jacknow
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
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.
0
 

Author Comment

by:rickmills
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>SQL 2008 Express edition which has a 10GB limit<<
You may want to double check that...
0
 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
> 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*
0
 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
*sigh*  I shouldn't post before drinking my coffee... meant to include this image.
SQL2008Compare.JPG
0
 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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.
0
 

Author Comment

by:rickmills
Comment Utility
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.
0
 
LVL 4

Expert Comment

by:monicai
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rickmills
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>> 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.
0
 

Author Comment

by:rickmills
Comment Utility
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.
0
 

Author Comment

by:rickmills
Comment Utility
Can you tell me how I can delete one field for every record in the database?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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)

0
 

Author Comment

by:rickmills
Comment Utility
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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)
0
 

Author Comment

by:rickmills
Comment Utility
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."
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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'

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
Incidentally you can also do this from the GUI (as in right click on your database, Tasks, Shrink, Files).  It is just that it is usually better doing it from the query window as you ahve more control.

I had a mental lapse earlier on it is not 3.5, but rather 3500.

Also, if you use the script I posted earlier, make sure you are in the right database before trying to execute DBCC SHRINKFILE as in:

USE YourDatabaseNameGoesHere
DBCC SHRINKFILE('The data logical file name goes here', 3500)
0
 

Author Closing Comment

by:rickmills
Comment Utility
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now