Copy SQL Server MDF and LDF files while server is in use

Question

I am using the following code to copy files from one folder to another...

Public Shared Sub CopyFlashScriptFile(ByVal SourceDirectory As String, ByVal DestinationDirectory As String)
   Try
      Dim f() As String = Directory.GetFiles(SourceDirectory)
      For i As Integer = 0 To UBound(f)
         File.Copy(f(i), DestinationDirectory & "\" & System.IO.Path.GetFileName(f(i)),True)
         Next
   Catch ex As Exception
      MsgBox(ex.Message)
   End Try
End Sub

The files I am copying are database files, .mdf and .ldf. Which are being used by the application. Now the problem is when I try to copy the files it throws an error

file is being used by another process

Can anyone help me with this?

Is there anyway I can programmatically stop SQL Server and copy the files, then start the server again?

Answer 1

Depending on which version of SQL you are using you could make use of the Microsoft.SqlServer.Management.Smo.Wmi.Service objects to Start and Stop the Service that runs the SQL instance.

After doing this you should be able to simply copy the files as needed.

For SQL Server 2008

{ 
   //Declare and create an instance of the ManagedComputer 
   //object that represents the WMI Provider services. 
   ManagedComputer mc; 
   mc = new ManagedComputer(); 
   //Iterate through each service registered with the WMI Provider. 

   foreach (Service svc in mc.Services)
   { 
      Console.WriteLine(svc.Name); 
   } 
//Reference the Microsoft SQL Server service. 
  Service Mysvc = mc.Services["MSSQLSERVER"]; 
//Stop the service if it is running and report on the status
// continuously until it has stopped. 
   if (Mysvc.ServiceState == ServiceState.Running) { 
      Mysvc.Stop(); 
      Console.WriteLine(string.Format("{0} service state is {1}", Mysvc.Name, Mysvc.ServiceState)); 
      while (!(string.Format("{0}", Mysvc.ServiceState) == "Stopped")) { 
         Console.WriteLine(string.Format("{0}", Mysvc.ServiceState)); 
          Mysvc.Refresh(); 
      } 
      Console.WriteLine(string.Format("{0} service state is {1}", Mysvc.Name, Mysvc.ServiceState)); 
//Start the service and report on the status continuously 
//until it has started. 
      Mysvc.Start(); 
      while (!(string.Format("{0}", Mysvc.ServiceState) == "Running")) { 
         Console.WriteLine(string.Format("{0}", Mysvc.ServiceState)); 
         Mysvc.Refresh(); 
      } 
      Console.WriteLine(string.Format("{0} service state is {1}", Mysvc.Name, Mysvc.ServiceState));
      Console.ReadLine();
   } 
   else { 
      Console.WriteLine("SQL Server service is not running.");
      Console.ReadLine();
   } 
}

From msdn

Answer 2

To expand on my comment - I would build a .sql file with the T-SQL command to backup the database to another location, and then I can use sqlcmd from the command line in order to run the backup .sql file.

So to build the .sql file I would go through the process of backing up the database via SQL Server Management Studio. Here is a tutorial on how to do this:

http://www.serverintellect.com/support/sqlserver/database-backup-ssmse.aspx

Then, before clicking OK to perform the backup, click on the "Script" button on the backup window and choose "Script Action To New Query Window". This will generate the SQL of your settings from the backup database window. Save that SQL into a file and you're done.

Next is to use sqlcmd.exe to execute the .sql file to backup the database whenver you want. There is a very good example of using sqlcmd.exe from C# code here:

http://geekswithblogs.net/thomasweller/archive/2009/09/08/automating-database-script-execution.aspx

I always prefer doing stuff like this without affecting the running SQL Server (unless it's one running on my dev machine, where I'll happily stop/start the service). You just never what might happen if you stop a production SQL Server service to copy some files. Could be very costly, so better to be safe.

Answer 3

I am using the .mdf file in my application...in case of a system crash or format the user is going to loose the data..so if the user copies the data(.mdf) to some other drive ..he/she can replace the new .mdf file with the old one which has all there data...correct me if i am wrong...thanks.

That's exactly what "normal" backups are for.
As you noticed yourself, you can backup a SQL Server database by simply copying the .mdf and .ldf files, but the downside is that you can only do this when the SQL Server service is not running.

And stopping the SQL Server service just to backup the database is not a good idea, because your users can't access the database while the service is stopped.

Taking a "normal" backup (usually a .bak file) can be done while the database is running, so there's no need to stop SQL Server every time you want to make a backup.

There are several ways how to do a backup:

a) Manually in SQL Server Management Studio:
see the first link in Jason Evans' answer

b) If you want to take a backup regularly (say, once a day) you need to use sqlcmd.
Jason Evans described this in his answer as well, but IMO there's an easier way - you need only two files with one line each. See How to create jobs in SQL Server Express edition.

(if you were using a full SQL Server edition and not only Express, you could set up a Maintenance Task in Management Studio instead, but that's not possible in SQL Server Express, so you have to do it manually like described above).

Answer 4

If it is possible to havesome downtime on your db you could use:

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

copy the mdf and ldf files and put the database online.

ALTER DATABASE foo SET ONLINE;