SQL Backing up with SMO & C# ?

Question

I'm using the following code to back up a SQL Database :

void BackupDatabase(string sConnect, string dbName, string backUpPath)
{
    using (SqlConnection cnn = new SqlConnection(sConnect))
    {
        cnn.Open();
        dbName = cnn.Database.ToString();

        ServerConnection sc = new ServerConnection(cnn);
        Server sv = new Server(sc);

        // Create backup device item for the backup
        BackupDeviceItem bdi = new BackupDeviceItem(backUpPath, DeviceType.File);

        // Create the backup informaton
        Microsoft.SqlServer.Management.Smo.Backup bk = new Backup();
        bk.PercentComplete += new PercentCompleteEventHandler(percentComplete);
        bk.Devices.Add(bdi);
        bk.Action = BackupActionType.Database;
        bk.PercentCompleteNotification = 1;
        bk.BackupSetDescription = dbName;
        bk.BackupSetName = dbName;
        bk.Database = dbName;
        //bk.ExpirationDate = DateTime.Now.AddDays(30);
        bk.LogTruncation = BackupTruncateLogType.Truncate;
        bk.FormatMedia = false;
        bk.Initialize = true;
        bk.Checksum = true;
        bk.ContinueAfterError = true;
        bk.Incremental = false;

        // Run the backup
        bk.SqlBackup(sv);
    }
}

In my system (Win7 x64) it works fine but in destination system (WinXP SP3 x86) I receive the below error :

How can I fix it ?
Thanks.

Answer 1

The sql server dll's for 32bit and 64bit are different.

It looks like your project is referencing the 64 bit dll's. When you try and run it on a machine with only 32bit dll's available you get a "file not found" / "could not load dll" type error.

If you want this to work on a 32 bit machine you should reference the 32bit version of sql server dll's.

Answer 2

If you check out the assembly directory on the Win7 machine (GAC) you'll see an entry called Microsoft.SqlServer.ConnectionInfo. (Browse to %windir%\assembly) On my machine it looks like this:

In my case I'm using version 10.0.0.0. In your case, you will at least see version 9.0.242.0 as that is what your program is compiled against (I find it unlikely that you're not referencing the dll from the GAC). If you don't have the same version installed on both machines, you've spotted the problem and you need to update the client library accordingly. I think it's likely that you have a newer version running on the XP machine, since you just installed 2008 there.

If you need more help after checking this out, you can comment here.