c#实现附加分离备份还原数据库（绝对原创）

c#实现附加分离备份还原数据库（绝对原创）

 

http://hi.baidu.com/feiyangqingyun/blog/item/454669ca1599048ec91768d0.html

 

 

c#实现附加分离备份数据库，无非就是调用SQL Server 里面的存储过程来实现，我把这几项操作都写到一个类当中去了，下面给出源代码，其中已经有些解释，这里不必多说了。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient ;
using System.Data ;

namespace 附加分离备份还原数据库
{
    class BaseClass
    {
        //自定义函数参数形式获取连接数据库字符串并返回
        public static SqlConnection getCon(string connectionString)
        {
            SqlConnection con = new SqlConnection(connectionString);
            return con;
        }
        //自定义函数获取连接数据库字符串并返回
        public static SqlConnection getCon()
        {
            string str="server=(local);database=master;uid=sa;pwd=";
            SqlConnection con = new SqlConnection(str);
            return con;
        }       
        //附加数据库参数dbName、db_MDF、db_LDF分别表示附加数据库后的数据库名称，要附加的数据库的路径及名称，要附加的数据库的日志文件路径及名称
        public static bool addDb(string dbName, string db_MDF, string db_LDF)
        {
            SqlConnection con = getCon();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;//要执行的对象是存储过程
            cmd.CommandText = "sp_attach_db";
            cmd.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
            cmd.Parameters.Add(new SqlParameter(@"filename1", SqlDbType.NVarChar));
            cmd.Parameters.Add(new SqlParameter(@"filename2", SqlDbType.NVarChar));
            cmd.Parameters[@"dbname"].Value = dbName;
            cmd.Parameters[@"filename1"].Value = db_MDF;
            cmd.Parameters[@"filename2"].Value = db_LDF;
            try
            {
                con.Open();
                int i = cmd.ExecuteNonQuery();
                if (i == 0)//如果执行不成功则返回假
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }
            return true;//执行成功则返回真
        }
        //分离数据库函数，参数dbName为要分离的数据库的名称
        public static bool sepDb(string dbName)
        {
            SqlConnection con = getCon();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;//要执行的对象为存储过程
            cmd.Connection = con;
            cmd.CommandText = "sp_detach_db";
            cmd.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
            cmd.Parameters[@"dbname"].Value = dbName;
            try
            {
                con.Open();
                int i = cmd.ExecuteNonQuery();
                if (i == 0)//如果执行不成功则返回假
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }
            return true;//执行成功则返回真
        }
        //备份数据库函数，参数dbName,backupName分别代表要备份的数据库和备份数据库的位置及名称
        public static bool backupDb(string dbName, string backupName)
        {
            SqlConnection con = getCon();
            string sql = "use master;backup database @dbname to disk=@backupname;";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
            cmd.Parameters.Add(new SqlParameter(@"backupname", SqlDbType.NVarChar));
            cmd.Parameters[@"dbname"].Value = dbName;
            cmd.Parameters[@"backupname"].Value = backupName;
            try
            {
                con.Open();
                int i = cmd.ExecuteNonQuery();
                if (i == 0)//如果执行失败返回假
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }
            return true;//执行成功返回真
        }
        //还原数据库函数，参数dbName,backupFile分别代表还原后数据库的名称和要还原的数据库路径及名称
        public static bool restoreDb(string dbName, string backupFile)
        {
            SqlConnection con = getCon();
            string sql = "use master;restore database @dbname from disk=@backupfile with replace;";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
            cmd.Parameters.Add(new SqlParameter(@"backupfile", SqlDbType.NVarChar));
            cmd.Parameters[@"dbname"].Value = dbName;
            cmd.Parameters[@"backupfile"].Value = backupFile;
            try
            {
                con.Open();
                int i = cmd.ExecuteNonQuery();
                if (i == 0)//如果执行失败返回假
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }
            return true;//执行成功返回真
        }

    }
}

 

用的时候直接传参数进去就是了。

1.附加数据库

private void btnAdddb_Click(object sender, EventArgs e)//附加数据库
        {
            string filename1 = txtDbmdf.Text.Trim();
            string filename2 = txtDbldf.Text.Trim();
            if (this.check1())
            {
                if (BaseClass.addDb("附加", filename1, filename2))//如果执行成功，则弹出提示
                {
                    MessageBox.Show("附加数据库成功！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("附加数据库失败！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

2.分离数据库

private void btnSepdb_Click(object sender, EventArgs e)//分离数据库
        {
            string dbName = cboxDbname.Text;
            if (BaseClass.sepDb(dbName))
            {
                MessageBox.Show("分离数据库成功！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("分离数据库失败！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

3.备份数据库

private void btnBackUp_Click(object sender, EventArgs e)//备份数据库
        {
            string dbName = cboxDataBaseName.Text.Trim();
            string backupFile = txtBackupPath.Text.Trim();
            if (this.check2())
            {
                if (BaseClass.backupDb(dbName, backupFile))
                {
                    MessageBox.Show("备份数据库成功！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("备份数据库成功！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

4.还原数据库

private void btnRestore_Click(object sender, EventArgs e)//还原数据库
        {
            string dbName = cboxRestoreDataBaseName.Text.Trim();
            string backupFile = txtRestorePath.Text.Trim();
            if (this.check2())
            {
                if (BaseClass.restoreDb(dbName, backupFile))
                {
                    MessageBox.Show("还原数据库成功！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("还原数据库失败！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

再给出一下其它辅助的代码：

//检验是否已经选择了要附加的数据库及日志文件
        private bool check1()
        {
            if (txtDbldf.Text.Trim() == "" || txtDbmdf.Text.Trim() == "")
            {               
                MessageBox.Show("请选择你要附加的数据库", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtDbmdf.Focus();
                return false;
            }          
            return true;
        }
        private bool check2()
        {
            if (txtBackupPath.Text.Trim() == "" && rbtnBackup .Checked ==true)
            {
                MessageBox.Show("请选择你要备份数据库的路径", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtBackupPath.Focus();
                return false;
            }
            if (txtRestorePath.Text.Trim() == "" && rbtnRestore .Checked ==true)
            {
                MessageBox.Show("请要还原的数据库及选择原备份数据库文件！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtRestorePath.Focus();
                return false;
            }
            return true;
        }
        //从服务器中获取所有数据库名称
        private void getDbname()
        {
            SqlConnection con = BaseClass.getCon();//获取数据库连接
            SqlCommand cmd = new SqlCommand("exec sp_helpdb", con);//执行存储过程遍历出所有数据库名称
            try
            {
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    cboxDbname.Items.Add(dr.GetValue(0));
                    cboxDataBaseName.Items.Add(dr.GetValue(0));
                    cboxRestoreDataBaseName.Items.Add(dr.GetValue(0));
                }
                if (cboxDbname.Items.Count > 0)//如果下拉框存在值的话，则让其选中第一项
                {
                    cboxDbname.SelectedIndex = 0;
                }
                if (cboxDataBaseName.Items.Count > 0)//如果下拉框存在值的话，则让其选中第一项
                {
                    cboxDataBaseName.SelectedIndex = 0;
                }
                if (cboxRestoreDataBaseName.Items.Count > 0)//如果下拉框存在值的话，则让其选中第一项
                {
                    cboxRestoreDataBaseName.SelectedIndex = 0;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("获取数据库时出错，原因可能是:" + ex, "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                con.Close();
            }
        }

弄了几个小时，调试了好久，终于弄出来了，大家也可以把类生成DLL文件，以后直接用就是咯！