Why am I losing data when debugging SQL Server Express?

This question comes up frequently enough (exhibit A),  that I thought I should blog about it to help raise the Google-juice and add some self-help to confused customers.

When you add a local SQL Server Express database to a Visual Studio Express project, VS will by default set the “Copy to Output Directory” property for your SQL Server Express database equal to “Always”.

Scenario that appears broken to users

  • Run app

  • Make changes to data

  • Save Data changes

  • Close app

  • Run app again

  • All data changes are lost

How to fix it
Luckily, this is a pretty easy fix, simply open your favorite copy of Visual Studio Express, in the solution explorer, select your database, and change the “Copy to Output Directory” to something like “Copy if newer”. Screenshot below 

We’re considering changing the default for this in the future, what do you all think? Will changing the default confuse things more?  Should this be a (wizard) option when creating the database?

Comments (9)

  1. Comment accidentally deleted, copy/pasted below:

    This works fine. But if I have done any modification in the structure of the database file, what will happen?  

    The VS will overwrite the last copy in the bin directory i.e. loosing the change of the data made by the program.  

  2. None of the options are suitable for me. "Copy always" option, always overwrites database copy in the output directory, what would not be cumbersome if VS Express copied it back when debugging session finished. "Copy if newer" will fall in the same trap when a change occur in the database (create a new field, create a new table, create a new index, etc.) and "Do Not Copy", well, what do you think of creating a new table, use it on your coding and when you run the debugging session the runtime pops up error message box stating the table does not exist?

    Moreover, imagine deploying a new version of your project and short after uncover the deployment package has overwritten or not overwritten (depending on the option set) the MDF database on the production environment.

    Microsoft really needs to rethink SQL Server Express use on Visual Studio Express for sure.

    Luciano Evaristo Guerche

    Taboao da Serra, SP, Brazil

  3. Richard Watkins says:

    I don’t understand why the data access was set up with the "Copy to ouput" scenario.  I understand how it works but for me it is cumbersome.  For example, I create an SQL database.  That database is copied to the output directory.  I start debugging the application and add data to the database.  Say I encounter a problem and I need to see the data in the database to figure it out.  I can’t use the data designer preview data tool because it looks at the original database which is different from the one that the app was working on.  Wouldn’t it be better if I could see that current data so I could debug my application?

  4. Biodave says:

    T H A N K  Y O U !

    This was driving me nuts. Relief at last!

  5. Amdres says:

    I have tried this… but it still doesn’t work for me, please help!!!

  6. Gary says:

    I spent all day on this. Google is no longer the search engine it once was. But finally an answer. Thanks so much.

  7. Alexander Muellner says:


    I’m not quite sure if that’s ripe for decision, but I read that’s possible to temporary or constantly save SQL databases to an externl provider.

    It might be a good idea to check this site from time to time, they always offer the newest information and developement in  this category:


  8. Steve Silva says:

    The major problem with the settings is that it is not clear what is happening.

    The worst part is for the enthusiastic beginners who go through the "Abolute Beginner’s Series VB lesson 9" on databinding.  The video shows that the data is actually being saved and the presenter calls up the data to show that it was saved and can be retrieved.  Of course, that will never happen to the person going through the series.  There is a good chance that the person will just give up.  Obviously, that person is not going to be a fan of Microsoft development environments.

  9. Steve says:

    I’ve also tried this to no avail.  I even have an  "AcceptChanges" for the dataset I am using, but it still does not save.  I’m even checking the various databases in the debug and app folders, but I cannot insert.  Here’s the code:

    Dim newInventoryRow As DataSet.InvRow

    newInventoryRow = DataSet1.Inv.NewInvRow

    newInventoryRow.ProductID = ProductIDVal

    newInventoryRow.ProductName = splitout(1)



    This is right from the MSDN tutorial for inserting data using datasets.  Does anyonoe think it is permissions?  I read somewhere that the aspnet user should have access to the database.  Should this be set up in IIS?  Could it be the SQLAgentCommandExec user?  Do you have to be a user of the group(s):  SQLServer2005MSSQLServerADHelperUser$PCNAME, SQLServer2005MSSQLUser$PCNAME$SQLEXPRESS, and SQLServer2005SQLBrowserUser$PCNAME  ????