.net备份与还原sql server数据库

本文写了一个类(C#)，可以远程来备份与还原数据库了。主要思路是利用xml来保存数据库中的表，还原是读取xml文件，保存到远程主机上。

代码：

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Text;
using System.IO;
using System.Threading;

namespace SQLSetup
{
    /// <summary>
    /// 用来对Sql Server中的用户表进行备份与还原
    /// </summary>
    public class SqlServer
    {
        /// <summary>
        /// 默认构造函数
        /// </summary>
        public SqlServer()
        {
            this.m_bBackup = false;
            this.m_bRevive = false;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="server"></param>
        /// <param name="user"></param>
        /// <param name="pwd"></param>
        /// <param name="filePath"></param>
        public SqlServer(string dbName, string server, string user, string pwd, string filePath)
        {
            this.m_dbName = dbName;
            this.m_Server = server;
            this.m_User = user;
            this.m_Pwd = pwd;
            this.m_FilePath = filePath;
            this.m_bBackup = false;
            this.m_bRevive = false;
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <returns></returns>
        public bool Backup()
        {
            bool bRet = true;

            try
            {
                Thread t = new Thread(new ThreadStart(ThreadBackupWork));
                t.Start();
            }
            catch
            {
                bRet = false;
            }

            return bRet;
        }

        /// <summary>
        /// 备份工作线程
        /// </summary>
        private void ThreadBackupWork()
        {
            string[] strTables = null;
            string strFileName = null;
            string strFilePath = null;
            string strTableFileName = null;
            StreamWriter sw;
            int i = 0;
            int iTablesCount = 0;
            int iTablesRealCount = 0;
            DataTable dt = null;

            //先获取所有表
            strTables = GetSQLDBTables();
            iTablesCount = strTables.Length;
            if (m_dbName == "DXSYSYB")
                strFilePath = Path.GetDirectoryName(m_FilePath) + "//ybsqldata";
            else
                strFilePath = Path.GetDirectoryName(m_FilePath) + "//nbsqldata";

            if (iTablesCount > 0)
            {
                //创建用来保存xml文件所在的目录
                if (!Directory.Exists(strFilePath))
                {
                    Directory.CreateDirectory(strFilePath);
                }
            }

            //针对该数据库,生成一个全局文件(如DXSYSNB.dx)
            //针对每一张表,生成一个xml文件
            strFileName = m_FilePath;
            if (File.Exists(strFileName))
            {
                File.Delete(strFileName);
            }
            sw = File.CreateText(strFileName);
            sw.WriteLine(m_dbName);

            try
            {
                for (i = 0; i < iTablesCount; i++)
                {
                    //把每个表名写入文件
                    if (strTables[i].Substring(0, 2) == "DX")
                    {
                        sw.WriteLine(strTables[i]);
                        iTablesRealCount++;
                        //把每个表结构与数据写入xml文件
                        dt = GetDataTable(strTables[i]);
                        strTableFileName = strFilePath + "//" + dt.TableName + ".xml";
                        dt.WriteXml(strTableFileName, XmlWriteMode.WriteSchema);
                        dt = null;
                    }
                }
                sw.WriteLine(iTablesRealCount.ToString());
                sw.Close();
                m_bBackup = true;
                MessageBox.Show("备份成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("备份失败！/r/n" + ex.Message);
            }
        }

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <returns></returns>
        public bool Revive()
        {
            bool bRet = true;

            //先创建数据库
            CreateDB();

            try
            {
                Thread t = new Thread(new ThreadStart(ThreadReviveWork));
                t.Start();
            }
            catch
            {
                bRet = false;
            }
           
            return bRet;
        }

        /// <summary>
        /// 还原数据库
        /// </summary>
        private void ThreadReviveWork()
        {
            string[] strAll = null;
            string[] strTables = null;
            string strFileName = null;  //备份数据库所保存的文件名称
            string strFilePath = null;  //数据库xml文件所在的路径
            int i = 0;
            int iTablesCount = 0;
            DataTable dt = null;

            //先获取所有表
            if (m_dbName == "DXSYSYB")
                strFilePath = Path.GetDirectoryName(m_FilePath) + "//ybsqldata";
            else
                strFilePath = Path.GetDirectoryName(m_FilePath) + "//nbsqldata";

            //针对该数据库,生成一个全局文件(如DXSYSNB.dx)
            //针对每一张表,生成一个xml文件
            strFileName = m_FilePath;
            if (!File.Exists(strFileName))
            {
                return;
            }
            strAll = File.ReadAllLines(strFileName);
            iTablesCount = Convert.ToInt32(strAll[strAll.Length - 1]);
            strTables = new string[iTablesCount];
            for (i = 0; i < iTablesCount; i++)
            {
                strTables[i] = strAll[i + 1];
            }

            try
            {
                for (i = 0; i < iTablesCount; i++)
                {
                    dt = new DataTable(strTables[i]);
                    dt.ReadXml(strFilePath + "//" + strTables[i] + ".xml");
                    CreateDataTable(dt);
                }
                m_bRevive = true;
                MessageBox.Show("还原成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("还原失败！/r/n" + ex.Message);
            }
        }

        /// <summary>
        /// 普通字段到sql server字段的转换
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        private SqlDbType Type2SqlType(Type t)
        {
            switch (t.ToString())
            {
                case "System.Boolean": return System.Data.SqlDbType.Bit;
                case "System.Single": return System.Data.SqlDbType.Real;
                case "System.Decimal": return System.Data.SqlDbType.Float;
                case "System.Double": return System.Data.SqlDbType.Float;
                case "System.Int16": return System.Data.SqlDbType.SmallInt;
                case "System.Int32": return System.Data.SqlDbType.Int;
                case "System.Int64": return System.Data.SqlDbType.BigInt;
                case "System.DateTime": return System.Data.SqlDbType.DateTime;
                case "System.Byte[]": return System.Data.SqlDbType.Image;
                case "System.String": return System.Data.SqlDbType.NVarChar;
                default:
                    throw (new NotSupportedException("Unsupported datatype '" + t.Name + "' found in datasource"));
            }
        }

        /// <summary>
        /// 在SQL Server的默认数据库中创建一个与dt相同的表
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="dt"></param>
        private void CreateDataTable(DataTable dt)
        {
            string connectionString = GetSQLConnectionString();
            DataColumnCollection columns = dt.Columns;
            int counter = -1;
            string tablename = dt.TableName;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                conn.Open();
                //Try to drop table if it exists
                try
                {
                    command.CommandText = "DROP TABLE /"" + tablename + "/";";
                    command.ExecuteNonQuery();
                }
                catch { }
                //Create new table for storing the datasource
                string sql = "CREATE TABLE " + tablename + " (";
                foreach (DataColumn col in columns)
                    if (col.DataType != typeof(String))
                        sql += "[" + col.ColumnName + "] " + Type2SqlType(col.DataType).ToString() + ",";
                    else
                        if (col.ColumnName.Trim() == "ReportCols")
                            sql += col.ColumnName.Trim() + " NVARCHAR(3000),";
                        else
                            sql += col.ColumnName.Trim() + " NVARCHAR(256),";
                command.CommandText = sql.Remove(sql.Length - 1, 1) + ");";
                command.ExecuteNonQuery();
                counter++;
                int rowCount = dt.Rows.Count;
                int iParameter = 0;
                for (int i = 0; i < rowCount; i++)
                {
                    if (counter == 0)
                    {
                        string strParameters = " (";
                        string strSQL = " (";

                        foreach (DataColumn col in columns)
                        {

                            strSQL += "[" + col.ColumnName + "],";
                            strParameters += string.Format("@Parameter{0},", iParameter++);

                        }
                        strSQL = strSQL.Remove(strSQL.Length - 1, 1);
                        strSQL += ")";
                        strParameters = strParameters.Remove(strParameters.Length - 1, 1);
                        strParameters += ")";
                        strSQL = "INSERT INTO " + tablename + strSQL + " VALUES" + strParameters;

                        command.CommandText = strSQL;
                        command.Parameters.Clear();
                        //Add datacolumn parameters
                        for (int j = 0; j < iParameter; j++)
                            command.Parameters.Add("@Parameter" + j.ToString(), Type2SqlType(columns[i].DataType));

                    }
                    //Set values
                    for (int j = 0; j < iParameter; j++)
                        command.Parameters["@Parameter" + j.ToString()].Value = dt.Rows[i][j];

                    //Insert row
                    command.ExecuteNonQuery();
                    counter++;
                }

                command.Parameters.Clear();

                //TODO:Create indexes
                //command.CommandText = "CREATE INDEX [IDX_Envelope_MinX] ON " + tablename + " (Envelope_MinX)";
                //command.ExecuteNonQuery();
                //command.CommandText = "CREATE INDEX [IDX_Envelope_MinY] ON " + tablename + " (Envelope_MinY)";
                //command.ExecuteNonQuery();
                //command.CommandText = "CREATE INDEX [IDX_Envelope_MaxX] ON " + tablename + " (Envelope_MaxX)";
                //command.ExecuteNonQuery();
                //command.CommandText = "CREATE INDEX [IDX_Envelope_MaxY] ON " + tablename + " (Envelope_MaxY)";
                //command.ExecuteNonQuery();

                conn.Close();
            }
        }

        /// <summary>
        /// 通过表名读取数据,返回一个tableName
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private DataTable GetDataTable(string tableName)
        {
            DataSet ds = null;
            string strConn = GetSQLConnectionString();
            string strSQL = null;

            strSQL = "select * from [" + tableName + "]";

            ds = new DataSet();
            ds = SqlHelper.ExecuteDataset(strConn, CommandType.Text, strSQL);
            ds.Tables[0].TableName = tableName;

            return ds.Tables[0];
        }

        /// <summary>
        /// 在sql server中创建数据库
        /// </summary>
        /// <param name="dbName"></param>
        private bool CreateDB()
        {
            string strSQL = null;
            string strConn =
             @"Persist Security Info=False;Connection Timeout=30;Server=" + m_Server
                + @";Uid=" + m_User
                + @";Pwd=" + m_Pwd
                + @";database=master";

            strSQL = "IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + m_dbName + "')  CREATE   DATABASE " + m_dbName;
            try
            {
                SqlHelper.ExecuteNonQuery(strConn, CommandType.Text, strSQL);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 返回数据库中所有的数据表名
        /// </summary>
        /// <returns></returns>
        private string[] GetSQLDBTables()
        {
            string[] arrStrTables = null;
            string strConnection = GetSQLConnectionString() + ";Provider=SQLOLEDB.1";

            //处理OleDbConnection
            try
            {
                OleDbConnection cn = new OleDbConnection(strConnection);
                cn.Open();
                //利用OleDbConnection的GetOleDbSchemaTable来获得数据库的结构
                DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                arrStrTables = new string[dt.Rows.Count];
                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    arrStrTables[i++] = dr["TABLE_NAME"].ToString();
                }
                cn.Close();
            }
            catch
            {
                arrStrTables = null;
            }

            return arrStrTables;
        }

        /// <summary>
        /// 获取数据库中表的数目
        /// </summary>
        /// <returns></returns>
        private int GetSQLDbCount()
        {
            int iRet = 0;
            string strConnection = GetSQLConnectionString() + ";Provider=SQLOLEDB.1";

            //处理OleDbConnection
            OleDbConnection cn = new OleDbConnection(strConnection);
            cn.Open();
            //利用OleDbConnection的GetOleDbSchemaTable来获得数据库的结构
            DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            iRet = dt.Rows.Count;
            cn.Close();

            return iRet;
        }

        /// <summary>
        /// 生成数据库连接字符串
        /// </summary>
        /// <returns></returns>
        private string GetSQLConnectionString()
        {
            return @"Persist Security Info=False;Connection Timeout=30;Server=" + m_Server
                + @";Uid=" + m_User
                + @";Pwd=" + m_Pwd
                + @";database=" + m_dbName;
        }

        #region 数据
        private bool m_bBackup;

        public bool bBackup
        {
            get { return m_bBackup; }
            set { m_bBackup = value; }
        }
        private bool m_bRevive;

        public bool bRevive
        {
            get { return m_bRevive; }
            set { m_bRevive = value; }
        }

        private string m_dbName;

        public string dbName
        {
            get { return m_dbName; }
            set { m_dbName = value; }
        }
        private string m_Server;

        public string Server
        {
            get { return m_Server; }
            set { m_Server = value; }
        }
        private string m_User;

        public string User
        {
            get { return m_User; }
            set { m_User = value; }
        }
        private string m_Pwd;

        public string Pwd
        {
            get { return m_Pwd; }
            set { m_Pwd = value; }
        }
        private string[] m_Tables;

        public string[] Tables
        {
            get { return m_Tables; }
            set { m_Tables = value; }
        }
        private int m_TablesCount;

        public int TablesCount
        {
            get { return m_TablesCount; }
            set { m_TablesCount = value; }
        }

        private string m_FilePath;

        public string FilePath
        {
            get { return m_FilePath; }
            set { m_FilePath = value; }
        }

        #endregion
    }
}