Making a full data backup of SQL Server in XML via .Net

I have often worked with clients whose data is on a hosted SQL Server database. They have often encountered problems getting a copy of the database on their local machine or have experienced pain when the hosting service failed. They can script the database fine, but often scripting the data will not work.

 

The code below does a full database TABLE backup into a collection of XML files. The logic is simple:

• Get all of the tables in the database (aka CATALOG)
• Do a SELECT * on each table specifying that the data should be formatted as XML
• This will write BINARY and IMAGE data correctly.
• This will write XML data correctly.
• Read the XML (which many sites have bad code for).
• Write it to a folder with today’s date.

Nice and simple! Just code your configuration file and away you go!!!

 

using System;
using System.Configuration;
using System.IO;
using System.Collections.Generic;
using System.Xml;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace BackupDatabase
{
class Program
{
static void Main(string[] args)
{
    List<string> dbTables=new List<string>();
    SqlConnection conn=new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString);
    conn.Open();
    DirectoryInfo Backup=new DirectoryInfo(Path.Combine(@"C:\SqlXmlBackups",DateTime.Now.ToString("yyyyMMdd")));
        if(! Backup.Exists)
        {
            Backup.Create();
        }
    //Step 1 get the tables. Make sure to put [ ] around the names
    SqlCommand cmd=new SqlCommand("Select '['+TABLE_SCHEMA+'].['+TABLE_NAME+']' from INFORMATION_SCHEMA.Tables where TABLE_TYPE='BASE TABLE' ",
        conn){CommandTimeout=50000,CommandType= CommandType.Text };
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            dbTables.Add(rdr.GetString(0));
        }
    }
    foreach (var table in dbTables)
    {
        // remove the [ ] around names
        FileInfo file=new FileInfo(
            Path.Combine(
            Backup.FullName,
            String.Format("{0}.Xml",
            table.Replace("[",string.Empty).Replace("]",string.Empty))));
        cmd.CommandText = String.Format("Select * from {0} for Xml Auto, Root('Backup')",table);
        using (XmlTextWriter wrt = new XmlTextWriter(file.FullName, Encoding.UTF8))
        {
            using (var xrdr = cmd.ExecuteXmlReader())
            {
                xrdr.Read();
                while (xrdr.ReadState != ReadState.EndOfFile)
                {
                    wrt.WriteRaw(xrdr.ReadOuterXml());
                    Console.Write(".");
                }
            }
        }
        Console.WriteLine(table);
    }
}
}
}