How to copy mdf file into a folder with c#

Question

I'm using c# and I have a small SQL Server database that I need to copy into a folder C:\databases\, the method name is CreateCopy.

But at File.Copy row appear an error: "The Process cannot Access the File, because it is being use by another Process"

I read that File.Copy can be execute only after shut SqlServer down or Detach that database, create a copy and turn SqlServer on again. But how to do it by code?

This is the method that I was trying to use:

public static void CreateCopy()
{
    try
    {
        DateTime date = DateTime.Now;
        SqlConnection connection = new SqlConnection(MDF_CONNECTION_STRING);

        String dbpath = String.Format(@"C:\databases\{0:yyyyMMdd}.mdf", Cash, date);
        File.Copy(@"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\database.mdf", dbpath);
        String lgpath = String.Format(@"C:\databases\{0:yyyyMMdd}_log.ldf", Cash, date);
        File.Copy(@"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_log.ldf", lgpath);    
    }
    catch(Exception e)
    {
        throw new ApplicationException("Error", e);
    }
}

Thanks in advance!

INFO This is not a duplicate of here because i don't need to do create a bak file, i need to archive this database. I need just to copy these two files (.mdf and .ldf) into a folder. And that answers didn't help me

possible duplicate of [Copy SQL Server MDF and LDF files while server is in use](http://stackoverflow.com/questions/8681009/copy-sql-server-mdf-and-ldf-files-while-server-is-in-use) — Mate, Sep 08 '15 at 06:31
Just to take a bkp is not necessary stop the service. In that question explains how to stop and start the service... read all answers — Mate, Sep 08 '15 at 06:54
You need to stop SQL Server first then copy the data and then start it again. — NASSER, Sep 08 '15 at 06:55
Backup of DB should also be considered as archiving as it will hold the same information as the .mdf and .ldf togeather in a single file? Are you okay to stop SQL Server and copy over the files to your archive location and start SQL Server back? — Rajesh, Sep 08 '15 at 09:54

score 2 · Answer 1 · answered Sep 08 '15 at 06:32

What I understand is that you are looking for the code which will help you to Programmatically Enumerating, Attaching, and Detaching SQL Server Databases So that you can copy the MDF file to the location. You can also have a look on How to Backup and Restore to get the idea of AttachDbFilename mode.

score 1 · Accepted Answer · answered Sep 08 '15 at 06:57

1

Database files .mdf and .ldf files are used by the SQL Server engine.

If you DETACH database from SQL Server instance, then you can copy or move those files.

But when you DETACH db, it will be unaccessible!

So it is better to run a backup command in SQL then use it.

answered Sep 08 '15 at 06:57

Eralper

6,461
2
21
27

Thank you, is better do backup than risk to leave database unacessible! :) – puti26 Sep 09 '15 at 09:05

score 0 · Answer 3 · answered Sep 08 '15 at 06:30

0

Remove SqlConnection connection = new SqlConnection(MDF_CONNECTION_STRING); it will access the .mdf file and give it after the File.Copy() execute. because at the time of execution of File.Copy() the file is being used by SqlConnection that's because you are getting such error

answered Sep 08 '15 at 06:30

sujith karivelil

28,671
6
55
88

The problem is that the SQLServer is running, and remove that connection row doesn't help me – puti26 Sep 08 '15 at 06:44

How to copy mdf file into a folder with c#

3 Answers3