SqlBackup from my client app

Question

I have an client app that runs on several machines, and connects to a single SQL database. On the client app, the user has the possiblity to use a local SQL (CE) database or connect to a remote SQL database. There is also a box where a backup location can be set.

For the backup of the remote SQL database I use the following code:

var bdi = new BackupDeviceItem(backupFile, DeviceType.File); var backup = new Backup { Database = "AppDb", Initialize = true };

backup.Devices.Add(bdi);

var server = new Server(connection); backup.SqlBackup(server);

When developing my application, I wasn't aware that the given backupFile is written on the machine where the SQL server runs! And not on the client machine.

What I want is "dump" all the data from my database to a local file.

(Why? Because users may enter a network location in the "backup location" box, and backing up SQL immediately to a network location fails. So need to write local first and then copy to the network in that case.)

I there an alternative to the method above?

Answer 1

Assign a folder from your SQL Server and let all the database backups from your application be written there. Shared this folder then code your application to copy the database backup from this shared folder to the location specified by your user.

You may want to delete the backup file after copying to make sure you don't fill up your disk space. Also, the backup file names on the server may need to be unique or random to avoid problem when multiple clients take backup at the same time.

Answer 2

Found a solution. I perform a "Select * from" query on all the tables in my database and put the result in a DataSet. The DataSet is then serialized (binary or XML) to a stream. Stream is written to the disk.

Code follows.

private static List<Stream> GetDatabaseAsBinary(string[] tablesToBackup, string connectionString)
{
    var connection = new OleDbConnection("Provider=SQLOLEDB; " + connectionString);
    connection.Open();
    var streams = new List<Stream>();
    foreach (var tableName in tablesToBackup) {
        streams.Add(GetTableAsBinary(tableName, connection));
    }

    return streams;
}

private static Stream GetTableAsBinary(string table, OleDbConnection oleDbConnection)
{
    var oleDbDataAdapter = new OleDbDataAdapter(string.Format("select * from {0}", table), oleDbConnection);
    var dataSet = new DataSet();
    oleDbDataAdapter.Fill(dataSet, table);
    dataSet.RemotingFormat = SerializationFormat.Binary;
    var format = new BinaryFormatter();
    var memStream = new MemoryStream();
    format.Serialize(memStream, dataSet);
    return memStream;          
}