Ondrej Janacek Ondrej Janacek - 1 year ago 104
C# Question

Database backup via SMO (SQL Server Management Objects) in C#

I need to backup database (using SQL Server 2008 R2). Size of db is about 100 GB so I want backup content only of important tables (containing settings) and of course object of all tables, views, triggers etc.

For example:

  • db:

  • tables:
    Food, Clothes, Cars

There is too much cars in
, so I will only backup table definition (
) and complete
(including its content).

Advise me the best solution, please. I will probably use SMO (if no better solution). Should I use
class? Or
class? Or another (if there is any)? Which class can handle my requirements?

I want backup these files to
files, one per table if possible.

I would appreciate code sample. Written in answer or somewhere (post url), but be sure that external article has solution exactly for this kind of problem.

You can use this part of code

ServerConnection connection = new ServerConnection("SERVER,1234", "User", "User1234");
Server server = new Server(connection);
Database database = server.Databases["DbToBackup"];

Answer Source

This arcitle was enough informative to solve my problem. Here is my working solution. I decided script all objects to one file, it's better solution because of dependencies, I think. If there is one table per on file and there is also some dependencies (foreign keys for example) it would script more code than if everything is in one file.

I omitted some parts of code in this sample, like backuping backup files in case wrong database backup. If there is no such a system, all backups will script to one file and it will go messy

public class DatabaseBackup
    private ServerConnection Connection;
    private Server Server;
    private Database Database;
    private ScriptingOptions Options;
    private string FileName;
    private const string NoDataScript = "Cars";

    public DatabaseBackup(string server, string login, string password, string database)
        Connection = new ServerConnection(server, login, password);
        Server = new Server(Connection);
        Database = Server.Databases[database];

    public void Backup(string fileName)
        FileName = fileName;

        foreach (Table table in Database.Tables)
             if (!table.IsSystemObject)
                  if (NoDataScript.Contains(table.Name))
                       Options.ScriptData = false;
                       Options.ScriptData = true;

    private void SetupOptions()
         Options = new ScriptingOptions();
         Options.ScriptSchema = true;
         Options.ScriptData = true;
         Options.ScriptDrops = false;
         Options.WithDependencies = true;
         Options.Indexes = true;
         Options.FileName = FileName;
         Options.EnforceScriptingOptions = true;
         Options.IncludeHeaders = true;
         Options.AppendToFile = true;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download