CODEWITHSUNDEEP
c#sql-server-2008smo
Ondrej Janacek
Ondrej Janacek

Reputation: 12616

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:

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

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

I want backup these files to *.sql 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"];

Upvotes: 2

Views: 8124

Answers (4)

Nag
Nag

Reputation: 41

Server databaseServer = default(Server);//DataBase Server Name
databaseServer = new Server("ecrisqlstddev");

string strFileName = @"C:\Images\UltimateSurveyMod_" + DateTime.Today.ToString("yyyyMMdd") + ".sql"; //20120720

if (System.IO.File.Exists(strFileName))
    System.IO.File.Delete(strFileName);

List<SqlSmoObject> list = new List<SqlSmoObject>();
Scripter scripter = new Scripter(databaseServer);

Database dbUltimateSurvey = databaseServer.Databases["UltimateSurvey"];//DataBase Name

// Table scripting Writing
DataTable dataTable1 = dbUltimateSurvey.EnumObjects(DatabaseObjectTypes.Table);

foreach (DataRow drTable in dataTable1.Rows)
{
    // string strTableSchema = (string)drTable["Schema"];
    // if (strTableSchema == "dbo")
    //    continue;
    Table dbTable = (Table)databaseServer.GetSmoObject(new Urn((string)drTable["Urn"]));

    if (!dbTable.IsSystemObject)
        if (dbTable.Name.Contains("SASTool_"))
            list.Add(dbTable);
}

scripter.Server = databaseServer;

scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = strFileName;
scripter.Options.DriAll = true;
scripter.Options.AppendToFile = true;

scripter.Script(list.ToArray());     // Table Script completed

// Stored procedures scripting writing
list = new List<SqlSmoObject>();

DataTable dataTable = dbUltimateSurvey.EnumObjects(DatabaseObjectTypes.StoredProcedure);

foreach (DataRow row in dataTable.Rows)
{
    string sSchema = (string)row["Schema"];

    if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
        continue;

    StoredProcedure sp = (StoredProcedure)databaseServer.GetSmoObject(
               new Urn((string)row["Urn"]));

    if (!sp.IsSystemObject)
        if (sp.Name.Contains("custom_"))
            list.Add(sp);
}

scripter.Server = databaseServer;

scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = strFileName;
scripter.Options.DriAll = true;
scripter.Options.AppendToFile = true;

scripter.Script(list.ToArray());    // Stored procedures script completed

Upvotes: 1

Ondrej Janacek
Ondrej Janacek

Reputation: 12616

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;
        SetupOptions();

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

    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;
    }
}

Upvotes: 1

Yahia
Yahia

Reputation: 70369

What you describe is not really a Backup but I understand what your goal is:

For "Backup" of Data you could load the table content via a Reader into a DataTable and store the result as XML...

Upvotes: 0

David Anderson
David Anderson

Reputation: 13670

Using SMO. You will have to play with the options you need.

StringBuilder sb = new StringBuilder();
using (SqlConnection connection = new SqlConnection("connectionString")) {
    ServerConnection serverConnection = new ServerConnection(connection);
    Server server = new Server(serverConnection);
    Database database = server.Databases["databaseName"];
    Scripter scripter = new Scripter(server);
    scripter.Options.ScriptDrops = false;
    scripter.Options.WithDependencies = true;
    scripter.Options.ScriptData = true;
    Urn[] smoObjects = new Urn[1];
    foreach (Table table in database.Tables) {
        smoObjects[0] = table.Urn;
        if (!table.IsSystemObject) {
            foreach (string s in scripter.EnumScript(smoObjects)) {
                System.Diagnostics.Debug.WriteLine(s);
                sb.AppendLine(s);
            }
        }
    }
}
// Write to *.sql file on disk
File.WriteAllText(@".\backup.sql");

Another easy way to do this is by backing the database to xml files. To do this use a DataTable and call WriteXml and WriteXmlSchema. (You need the schema later on so it can be imported/restored using the same method). This method means you are backing up per table.

private bool BackupTable(string connectionString, string tableName, string directory) {
    using (SqlConnection connection = new SqlConnection(connectionString)) {
        try {
            connection.Open();
        }
        catch (System.Data.SqlClient.SqlException ex) {
            // Handle
            return false;
        }
        using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT * FROM {0}", tableName), connection)) {
            using (DataTable table = new DataTable(tableName)) {
                adapter.Fill(table);
                try {
                    table.WriteXml(Path.Combine(directory, string.Format("{0}.xml", tableName)));
                    table.WriteXmlSchema(Path.Combine(directory, string.Format("{0}.xsd", tableName)));
                }
                catch (System.UnauthorizedAccessException ex) {
                    // Handle
                    return false;
                }
            }
        }
    }
    return true;
}

You can later on then push these back into a database us by using ReadXmlSchema and ReadXml, using an adapter to fill and update the table to the database. I assume you are knowledgable in basic CRUD so I shouldn't need to cover that part.

If you want to use SMO, here is a Msdn article on using the Backup and Restore classes to backup and restore a database. The code sample us unformatted, and in VB.NET, but easily translatable.

Lastly, which may be easier, talk to the IT guys and see if they will let you remote in or give you access to do the backup yourself. If you are writing the software and this is a crucial step, speak up and let them know how important it is for you to do this, as this will reduce cost in you having to write a custom tool when great tools already exist. Especially since the database is 100GB, you can use the tools you know already work.

Upvotes: 1

Related Questions