none
Copy to Output / Copy if Newer database perennial question

    Question

  • Copy to Output / Copy if Newer database perennial question

    I had a database in a certain folder XYZ in Vista, running Visual Studio 2008.  I went offline VS08, and played around with the database, destroying it, and then, deleting it.  Then I got the database to reappear again--but playing around with the settings Do Not Copy / Copy if Newer / Copy Always, I get the database to reappear everytime VS 08 boots up, but only with the latter two settings (program crashes when "Do Not Copy" is selected--"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." is the error message).

    Can I get "Do Not copy" restored if I copy the Debug/Release version of the database to my folder XYZ?  I tried this, and it still fails to recognize this (I think it's a security problem built into SQL Server Express 2008)--so do I now "repoint" my sqldataconnectors to this new database (having the same name as before)? 

    Also, it seems I cannot make permanent (beyond one rebuild cycle) changes to the database in my /Debug / Release folders of my project.  What to do?  Is this a big deal, or, can I simply not worry about it--or, can I simply replace some database in the /Debug/Release folder with another name...but what?

    It's not a big deal now, since I'm still in development, but just curious about if anybody has had this happen.  I've not had this happen before after about a year of playing around with ADO.NET--usually, a corrupt database is deleted, a new one is put in place in your XYZ folder, and it seems to me (from past experience) everything goes back to normal.

    RC

    Tuesday, May 12, 2009 8:02 PM

Answers

  • Wednesday, May 13, 2009 4:25 AM
  • I figured it out.

    First, back up everything.

    Next, delete the database and log that resides on your "local" app drive.  My database in the below example is called "FolktaleDB.mdf" and has a log file "FolktaleDB_log.ldf".

    Third, take a copy of what you deleted in step two, and place both these Folktale*.* files into your "new" directory, where your other databases reside. ***That is, your "C:\Users\YOURNAMEHRE\YOURPATHHERE\" directory.

    Then, do a global search for this keyword:  |DataDirectory|.  This must be replaced with the complete path *** as below.

    Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\FolktaleDB.mdf;Integrated Security=True

    //replace "|DataDirectory|\FolktaleDB.mdf" with the complete path *** C:\Users\YOURNAMEHRE\YOURPATHHERE\FolktaleDB.mdf

    Finally, also, do a global search and replace of all strings in your app, but be careful to get it right, to change the database from the local (old) "|DataDirectory|" drive to the 'new' place, which in this example is "C:\Users\YOURNAMEHRE\YOURPATHHERE\".

    "typically" you'll have several of these.

    Further, watch out for excape character tricks, for example, "\\", as here is one such one example:

                // sqlConnection1Frm2
                //
                this.sqlConnection1Frm2.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=\"C:\\Users\\MYNAME\\MYPATHHERE\\FolktaleDB.mdf\";Integrated Security=True;Connect Timeout" +                "=30;User Instance=True";


    Then, finally, change all .xsd schemas (the icon in Solution Explorer that has the .xsd extension) (if you use Strongly Typed Datasets) so that under the "property" tab, you select under the "Copy to Output Directory" property, the following: "Do Not Copy".  Other options are "Copy Always" and "Copy if Newer".  Search the net for what this means, but for me, if you make backup copies of your database, I prefer not to have a "local" copy, hence I select "Do Not Copy".  In fact, for me, the problems all began when I selected the latter two options, then made the fatal mistake of copying a database from my directory MYPATHHERE to the 'local' directory where the app resides.  The app started choking and making, upon reboot, a .mdf1 carbon copy database--hence this post and this thread.  BTW, this reverse copying mistake is warned against in some autogenerated code in Visual Studio I saw when making the above changes.  Apparently you always copy from your app directory to your MYPATHHERE directory, not the other way around, or you'll reproduce this problem.  But this warning was too little too late for me.  In any event, like I say, I prefer "Do Not Copy", but that's my choice.  If you pick "Do Not Copy" make sure you always make a backup of your database (.zip file it is one quick option) before running your app, since if you screw up your database you'll have to resort to an old copy.  As you know the way databases work is with foreign key and constraints you can easily break a database and not easily repair it, unless you have a backup copy.

    In conclusion, it's both better and worse than you think.  It will take you about an hour or two to figure this out, if you haven't seen this thread first.  There are a few well-intentioned but false / deadend bits of advice on the net, including one moderator who suggests that you should not do the above, in general (and he doesn't tell you what to do instead), but I don't see any other way around it.

    Good luck.

    • Marked as answer by RonConger09 Wednesday, May 13, 2009 7:20 PM
    Wednesday, May 13, 2009 7:20 PM

All replies

  • Wednesday, May 13, 2009 4:25 AM
  • Thanks.  I've seen this thread, and the key phrase for my app is this sentence:  "Alternatively, you can change the connection string *and* the copy property, and point to the file you actually want to connect to."

    So I have to go back into my application and change the connection string to a new one, that renames my old database and uses as a copy some version of the database that does work.  I think there's a security permission in play here...

    RC
    Wednesday, May 13, 2009 2:27 PM
  • I figured it out.

    First, back up everything.

    Next, delete the database and log that resides on your "local" app drive.  My database in the below example is called "FolktaleDB.mdf" and has a log file "FolktaleDB_log.ldf".

    Third, take a copy of what you deleted in step two, and place both these Folktale*.* files into your "new" directory, where your other databases reside. ***That is, your "C:\Users\YOURNAMEHRE\YOURPATHHERE\" directory.

    Then, do a global search for this keyword:  |DataDirectory|.  This must be replaced with the complete path *** as below.

    Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\FolktaleDB.mdf;Integrated Security=True

    //replace "|DataDirectory|\FolktaleDB.mdf" with the complete path *** C:\Users\YOURNAMEHRE\YOURPATHHERE\FolktaleDB.mdf

    Finally, also, do a global search and replace of all strings in your app, but be careful to get it right, to change the database from the local (old) "|DataDirectory|" drive to the 'new' place, which in this example is "C:\Users\YOURNAMEHRE\YOURPATHHERE\".

    "typically" you'll have several of these.

    Further, watch out for excape character tricks, for example, "\\", as here is one such one example:

                // sqlConnection1Frm2
                //
                this.sqlConnection1Frm2.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=\"C:\\Users\\MYNAME\\MYPATHHERE\\FolktaleDB.mdf\";Integrated Security=True;Connect Timeout" +                "=30;User Instance=True";


    Then, finally, change all .xsd schemas (the icon in Solution Explorer that has the .xsd extension) (if you use Strongly Typed Datasets) so that under the "property" tab, you select under the "Copy to Output Directory" property, the following: "Do Not Copy".  Other options are "Copy Always" and "Copy if Newer".  Search the net for what this means, but for me, if you make backup copies of your database, I prefer not to have a "local" copy, hence I select "Do Not Copy".  In fact, for me, the problems all began when I selected the latter two options, then made the fatal mistake of copying a database from my directory MYPATHHERE to the 'local' directory where the app resides.  The app started choking and making, upon reboot, a .mdf1 carbon copy database--hence this post and this thread.  BTW, this reverse copying mistake is warned against in some autogenerated code in Visual Studio I saw when making the above changes.  Apparently you always copy from your app directory to your MYPATHHERE directory, not the other way around, or you'll reproduce this problem.  But this warning was too little too late for me.  In any event, like I say, I prefer "Do Not Copy", but that's my choice.  If you pick "Do Not Copy" make sure you always make a backup of your database (.zip file it is one quick option) before running your app, since if you screw up your database you'll have to resort to an old copy.  As you know the way databases work is with foreign key and constraints you can easily break a database and not easily repair it, unless you have a backup copy.

    In conclusion, it's both better and worse than you think.  It will take you about an hour or two to figure this out, if you haven't seen this thread first.  There are a few well-intentioned but false / deadend bits of advice on the net, including one moderator who suggests that you should not do the above, in general (and he doesn't tell you what to do instead), but I don't see any other way around it.

    Good luck.

    • Marked as answer by RonConger09 Wednesday, May 13, 2009 7:20 PM
    Wednesday, May 13, 2009 7:20 PM
  • The error message you'll get, btw, is often this one:

    An attempt to attach an auto-named database for file C:\YOURUSERNAME\YOURPATH\YOURDATABASEHERE failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
    Wednesday, May 13, 2009 7:25 PM
  • In general, AttachDbFileName is a feature to tell SQL to load and attach to the database file specified.  Fairly simple concept and feature.  However the VS.NET IDE and various Wizards tend to cloud how all this works by adding this "auto-copy" feature that wipes out your database.  Put the two together and everyone gets confused.  I guess this is a general downside of Wizard code and smart IDE's you don't know what it is doing unless you really dig into it.

    Wednesday, May 13, 2009 8:09 PM