SQLSERVER SQL备份还原代码C#_sql

概述public class BakDBHelper { /// <summary> /// 创建数据库备份 /// </summary> public string CreateBackup(string dbname, string backname) { string

 public class BakDBHelper    {        /// <summary>        /// 创建数据库备份        /// </summary>        public string CreateBackup(string dbname,string backname)        {            string res = "";            //要备份的位置            //string dbfullname = GetDbPath() + string.Format("{0}_{1}.bak",dbname,DateTime.Now.ToString("yyyyMMddhhmmss"));            string dbfullname = GetDbPath() + backname;            //判断文件是否存在            if (file.Exists(dbfullname))            {                file.Delete(dbfullname);                //MessageBox.Show(dbfullname + "的备份文件已经存在，请稍后再试");            }            try            {                sqlConnection con = new sqlConnection(GetConn());                sqlCommand cmd = con.CreateCommand();                con.open();                try                {                    cmd.CommandText = "use master";                    cmd.ExecuteNonquery();                    //1. 执行备份 *** 作                    StringBuilder sql = new StringBuilder();                    //sql.Append("exec master.dbo.proc_Backup @dbname,@dbFullname");                    sql.Append(@"DECLARE @kID varchar(100)                      SET @kID=‘‘                      SELECT @[email protected]+‘KILL ‘+CAST(spID as Varchar(10))  FROM master..sysprocesses                      WHERE dbID=DB_ID(@dbname)                      PRINT @kID                      EXEC(@kID);                    backup database "+ dbname + " to [email protected];");                    sqlParameter[] parameters = new sqlParameter[]{            new sqlParameter("@dbname",sqlDbType.NVarChar,200),new sqlParameter("@dbFullname",};                    parameters[0].Value = dbname;                    parameters[1].Value = dbfullname;                    cmd.Parameters.Clear();                    cmd.Parameters.AddRange(parameters);                    cmd.CommandText = sql.ToString();                    cmd.ExecuteNonquery();                    res = "备份完成";                }                finally                {                    con.Close();                }            }            catch (Exception ex)            {                res = "创建数据库备份出错：" + ex;            }            return res;        }        /// <summary>        /// 还原数据库        /// </summary>        public string Restore(string dbname,string backname)        {            string res = "";            //1.获取还原数据库和文件            string dbFullname = GetDbPath() + backname;            try            {                //2.执行还原 *** 作                sqlConnection con = new sqlConnection(GetConn());                sqlCommand cmd = con.CreateCommand();                con.open();                try                {                    cmd.CommandText = "use master";                    cmd.ExecuteNonquery();                    StringBuilder sql = new StringBuilder();                    //sql.Append("exec proc_Restore @dbFullname,@dbname");                    sql.Append(@"--1.1修改为单用模式                    exec(N‘ALTER DATABASE ‘[email protected]+‘ SET SINGLE_USER WITH RolLBACK IMMEDIATE‘);                --1.2结束链接进程                    DECLARE @kID varchar(max)                      SET @kID=‘‘                      SELECT @[email protected]+‘KILL ‘+CAST(spID as Varchar(10))  FROM master..sysprocesses                      WHERE dbID=DB_ID(@dbname)  ;                    EXEC(@kID) ;                --2.执行还原语句                    restore database @dbname from  [email protected]                    with replace  --覆盖现有的数据库                --3.重置数据库为多用户模式                    exec(N‘ALTER DATABASE ‘[email protected]+‘ SET MulTI_USER WITH RolLBACK IMMEDIATE‘);");                    sqlParameter[] parameters = new sqlParameter[]{                new sqlParameter("@dbname",};                    parameters[0].Value = dbname;                    parameters[1].Value = dbFullname;                    cmd.CommandText = sql.ToString();                    cmd.Parameters.AddRange(parameters);                    cmd.ExecuteNonquery();                    res = "还原数据库完成";                }                finally                {                    con.Close();                }            }            catch (Exception ex)            {                res = "还原数据库出错" + ex;            }            return res;        }        private static string GetDbPath()        {            return ConfigurationManager.AppSettings["bak_url"].ToString();        }        private static string GetConn()        {            return ConfigurationManager.AppSettings["bak_con"].ToString();        }        public int execsqlCommand(string sql)        {            int res;            try            {                //2.执行还原 *** 作                sqlConnection con = new sqlConnection(GetConn());                sqlCommand cmd = con.CreateCommand();                con.open();                try                {                    cmd.CommandText = sql;                    res = cmd.ExecuteNonquery();                }                finally                {                    con.Close();                }            }            catch (Exception)            {                return -1;            }            return res;        }    }