SQLSERVER SQL备份还原代码C#

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!