How to backup a database in c# through code?

For example, I deleted a record on a table on the database and my database is开发者_开发问答 MS Aaccess. Any backup mechanisms that I can refer to? So that when I need a rollback of the database I just restore it quickly from code.

---

MS Access is the file based database, right? in my understanding, that means, when the connection is closed and the file is not in use, you can copy that file to another location.

Here I assume the application has such privileges on the file system.

Also, I agree with Morten Martner's answer, if the database type is MS SQL Server, then you will definitely need SMO library use.

---

I'm using the following code to backup SQL server databases:

using System;
using System.Collections.Generic;
using System.Data;
using System.Collections;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Text;

namespace Codeworks.SqlServer.BackupDatabase
{
    public class BackupCore
    {
        public static void Execute( string instance, string database, string outputFile )
        {
            BackupDeviceItem bdi = new BackupDeviceItem( outputFile, DeviceType.File );
            Backup bu = new Backup();
            bu.Database = database;
            bu.Devices.Add( bdi );
            bu.Initialize = true;

            // add percent complete and complete event handlers
            bu.PercentComplete += new PercentCompleteEventHandler(Backup_PercentComplete);
            bu.Complete +=new ServerMessageEventHandler(Backup_Complete);

            Server server = new Server( instance );
            bu.SqlBackup( server );
        }

        protected static void Backup_PercentComplete( object sender, PercentCompleteEventArgs e )
        {
            // Console.WriteLine( e.Percent + "% processed." );
        }

        protected static void Backup_Complete( object sender, ServerMessageEventArgs e )
        {
            Console.WriteLine( e.ToString() );
        }
    }
}

You'll need the management libraries from MS for the correct SQL server version, but those are available for download.

---

If you're a single user of your database, you just need to close your connection and copy it with the file system.

If there are multiple users, then you should use a different method. If you actually have Access available, there's an undocumented command that will make a backup of the tables a Jet/ACE file:

  Application.SaveAsText 6, vbNullString, strTargetMDB 

Now, since this can only be done with the database open in the Access UI, it requires automating Access and operating on the CurrentDB. Here's an implementation that runs within Access:

  Public Function CreateBackup(strMDBName As String, strBackupPath As String, _
    Optional bolCompact As Boolean = False) As Boolean
  On Error GoTo errHandler
    Dim objAccess As Object
    Dim strBackupMDB As String
    Dim strCompactMDB As String

    If Len(Dir(strBackupPath & "\*.*")) = 0 Then ' alternative: use File System Object for this
       MkDir strBackupPath
    End If

    Set objAccess = New Access.Application
    objAccess.Application.OpenCurrentDatabase strMDBName
    strBackupMDB = "Backup" & Format(Now(), "YYYYMMDDhhnnss") & ".mdb"
    Debug.Print strBackupPath & "\" & strBackupMDB
    objAccess.Application.SaveAsText 6, vbNullString, strBackupPath & "\" & strBackupMDB
    objAccess.Application.Quit
    Set objAccess = Nothing

    If bolCompact Then
       strCompactMDB = strBackupPath & "\" & "c_" & strBackupMDB
       Name strBackupPath & "\" & strBackupMDB As strCompactMDB
       DBEngine.CompactDatabase strCompactMDB, strBackupPath & "\" & strBackupMDB
       Kill strCompactMDB
    End If

    CreateBackup = (Len(Dir(strBackupPath & "\" & strBackupMDB)) > 0)

  exitRoutine:
    If Not (objAccess Is Nothing) Then
       On Error Resume Next
       objAccess.Application.Quit
       On Error GoTo 0
       Set objAccess = Nothing
    End If
    Exit Function

  errHandler:
    Select Case Err.Number
      Case 75 ' Path/File access error -- tried to MkDir a folder that already exists
        Resume Next
      Case Else
        MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in CreateBackup()"
        Resume exitRoutine
    End Select
  End Function

To run that from C# you'd have to automate Access, and you likely don't want a dependency on Access.

Since I work in Access exclusively, that's the method I use, so I've never programmed the more complicated methods.

If you have exclusive access to the database, you could use JRO CompactDatabase command to compact to a new filename, but if you have exclusive access, you can also use the file system.

So, basically, you've got choices about how to export the data tables to a backup database. You could use DoCmd.TransferDatabase to copy all the data tables, and then copy the relationships, or you could create an empty template database and append the data from each table in turn to a copy of the template (in an order that won't violate RI, of course).

Neither of those sounds anything but messy to me, and that's why I use the SaveAsText method! But if I wasn't running Access, the other two alternatives would be worth doing.