SQLSERVER SQL備份還原代碼C#

標籤：RoCE   delete   sse   nbsp   range   arch   執行   private   color   

 public class BakDBHelper    {        /// <summary>        /// 建立Database Backup        /// </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",SqlDbType.NVarChar,200),        };                    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 = "建立Database Backup出錯：" + 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",SqlDbType.NVarChar,200),                new SqlParameter("@dbFullName",SqlDbType.NVarChar,200),            };                    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;        }    }

 

SQLSERVER SQL備份還原代碼C#