c# when copying file(sql localdb)_IOEXCEPTION_ Process cannot access file because the file is being used by another process

Question

I have simple SQL application which uses MS-SQL localdb file(.mdf). And I want to copy this localdb file(.mdf) to another folder whenever the application is closing as backup purpose.

However, below simple code brought IOException as titled of this question. My application always stays unconnected with localdb file(.mdf) if there's no user's specific button click.

I've found other cases but my poor knowledge is not enough to understand even what is similar.

I always highly appreciate your excellence. Thank you so much !

private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e)
    {
        if (MessageBox.Show("really want to exit?", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.No)
        {
            e.Cancel = true;
        }
        else
        {
            var greendbfileName = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), string.Format("greendb_{0}.mdf", personID));
            var copied_greendbfileName = string.Format(@"C:\greendb_{0}.mdf", personID);
            File.Copy(greendbfileName, copied_greendbfileName);

            Environment.Exit(0);
        }
    }

score 1 · Answer 1 · edited May 23 '17 at 12:15

1

For someone who is looking for own solution for situation like this. I recommend a standard BACKUP database command(and RESTORE). Thank you !

Just as reference
How to backup a SQL Server 2014 Express Localdb (.mdf) file programmatically

edited May 23 '17 at 12:15

Community

1
1

answered Mar 04 '16 at 05:59

Kay Lee

922
1
12
40

Thanks, this helped me. It did not work straight away, but pointed me in the right direction. I needed to copy my database, and for restore I had to use "RESTORE FILELISTONLY" additionally because the names of my MDF and LDF files were different. Maybe this helps someone else: see "E. Copying a database using BACKUP and RESTORE" here https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15 – Butterfly Mar 16 '21 at 20:34

Kay Lee · Answer 2 · 2016-03-06T04:11:46.483

When I terminated the process, The primary key ID column of table increased strangely like 1, 2, 3, 4, 5010, 6010, 7010. At the first time, it seemed working fine(the localdb mdf file was copied successfully but, when I ran my application next time, maybe the abnormal termination of localdb process seem to affect its functions. I think forcing a termination of localdb has to be very careful and to be studied by experts unlike me.

I hope this small experience helps someone who is looking for solution like me.

When SQL connection to localdb opens, a process(sqlservr.exe) start working. The file extension should not be with.

foreach (var process in Process.GetProcessesByName("sqlservr"))
{
    process.Kill();
}

I gave up above way and returning back to the standard backup command of SQL Localdb.

score 0 · Answer 3 · edited May 23 '17 at 12:15

0

You should be able to do the following: 1. close all connections to the db 2. connect to the local master db 3. using that connection take your db offline, then detach it, then copy the file. The next time you connect to it, it should simply re-attach your db.

You might want to take a look at my answer to my own questions here: When does LocalDB unlock the mdf file?

... which is really just a modern-ized update to this answer: https://stackoverflow.com/a/22791484/999256

edited May 23 '17 at 12:15

Community

1
1

answered Mar 07 '16 at 02:43

C.List

657
8
16

I highly appreciate your kindness and excellence. However, as I posted as a self-answer, I tried several ways but finally succeeded with standard backup command and much satisfied with its stability and conveniences. Thank you, I learned from your answer. – Kay Lee Mar 07 '16 at 02:51

c# when copying file(sql localdb)_IOEXCEPTION_ Process cannot access file because the file is being used by another process

3 Answers3