0

I have an application that checks to see if it's the most recent version. If not, it updates itself by using File.Copy to replace the DB attached to the application with a fresh one (that may or may not have had changes made to it). In an attempt to keep the data from being deleted, I created a backup system that writes all the data to an XML file before the database is deleted and restores the data once the database has been copied.

I am having a problem with the File.Copy method, however, in that an error pops up telling me the .MDF is being used by another process.

I was told that stopping SQL Server would work, but it hasn't. I've also been told I can use SMO, but also have not been able to make that work. With this seeming so close to complete, SMO also seems like it won't be necessary.

My code is this:

'This is the backup. I make sure to close the SQL Connection when the process is complete.

        Dim db As String = "C:\ACE DB\localACETest.mdf"
        Dim dbLog As String = "C:\ACE DB\localACETest_log.ldf"

        If File.Exists(db) = True Then
              'Backup process
              '...
        End If


       '"Data/localACETest.mdf" referenced below is the file located inside of my application that is used to overwrite the other MDF; it is NOT the .MDF I'm looking to replace.

       Directory.CreateDirectory("C:\Random Directory\")
       File.Copy("Data/localACETest.mdf", db, True) 'This is the line where I get the error
       File.Copy("Data/localACETest_log.ldf", dbLog, True)
       success = False
       ...

EDITS: I have narrowed the issue down to the method that backs up my data. I'm using the following connection string:

Private Const _sqlDB As String = "Data Source=(localdb)\v11.0;Initial Catalog=localACETest;Integrated Security=True; _
AttachDbFileName=C:\ACE DB\localACETest.mdf"

I open SQL, run a command, and then close it:

Using connection = New SqlConnection(_sqlDB)
            connection.Open()
            ...
            connection.close()

Why does this not release the MDF from the process? (When I don't run it, I have no problems)

user
  • 1,261
  • 2
  • 21
  • 43
  • 1
    You can't and you **shouldn't** just copy around `.mdf` files in the first place! SQL Server has defined procedure for doing this - either use the backup/restore mechanism (create backup `.bak` file on source, restore on target) or use the detach/attach method. But in any case - don't just try to copy files behind SQL Server's back! That's a sure-fire recipe for disaster.... - just **don't do it!** – marc_s Aug 20 '13 at 04:53
  • Some explanation as to why would server me here. The MDF file is one that I know the ins and outs of and that only my application accesses. – user Aug 20 '13 at 19:18
  • 1
    Do not - that is, EVER, kill the SQL Server process like that. Stop the service using the service management APIs. Crazy! – Kieren Johnstone Aug 20 '13 at 19:45
  • Thanks for the comments. I've gotten rid of the process kill, but I still can't get this resolved. For some reason, the method I use to backup my DB using a SQL connection isn't fully releasing the MDF, despite making sure to close the connection at the end of the method. – user Aug 21 '13 at 15:31
  • The backup isn't going to release the .mdf... it's going to simply create a .bak. You're still going at this completely wrong. Either restore/backup or attach/detach like was previously stated. – Matt Aug 21 '13 at 17:47
  • Matt, will you please look at my comment for "tgolish's" answer. – user Aug 21 '13 at 18:04

1 Answers1

0

You are better off sending a command to SQL server telling it to make a backup for you. This SO article has a great (command line) script that you can copy/paste: SQL Server command line backup statement

Put that into a batch and launch it like this.

System.Diagnostics.Process.Start("C:\Ace Db\MakeADBBackup.bat")

If you would prefer to make your program wait till the backup finishes, read about launching processes: http://support.microsoft.com/kb/305368

If you are REALLY insistent on making a copy of the .mdf (which is not a good idea), then you need to ask the server to stop the SQL service before you make a copy. You could run a batch that says

NET STOP MSSQLSERVER

Assuming that your SQL Server is running under the name "MSSQLSERVER". To check the names of running services, open a command prompt and type-in "NET START". It will give a list of services that are running. One of them will be the "process name" of your SQL Server's running service.

Better still, here is an article (for VB.NET) that shows the source code for starting/stopping SQL Server. http://msdn.microsoft.com/en-us/library/ms162139(v=SQL.90).aspx

I strongly recommend that you try the first approach that I suggested.

Community
  • 1
  • 1
tgolisch
  • 6,549
  • 3
  • 24
  • 42
  • Thanks for your comments. My question really is: how do I backup all of my SQL data so that, when I've made updates to the application's database (eg updated specific tables), I can restore it (the data) in the new database. I realize that by adjusting tables I could create problems, but, assuming the tables I'm editing are not ones that will be affected by the reinsert, how can I accomplish this? – user Aug 21 '13 at 18:00
  • Many people find it useful to make a backup and then restore the database under a different name. Then they can copy data around as needed (and they have a backup, just in case things go awry). The same can be done with a table (btw, that is how SSMS does it). Instead of replacing a table, just rename it. Then create the new one and copy your data back in. If things go bad, it is easier to undo. But, before you do any of it, please make a DB backup anyway! – tgolisch Aug 21 '13 at 18:16