调用sql语句实现SqlServer的备份和还原,包括完整备份和差异备份

浏览数：24 / 时间：2015年06月12日

调用sql语句实现SqlServer的备份还原,包括完整备份和差异备份
,因为执行备份还原需要一定的时间,因此需要设定 CommandTimeout参数.
        /// <summary>
        /// 备份数据库调用SQL语句
        /// </summary>
        /// <param name="strFileName">备份文件名</param>
        /// <param name="BackUpType">0表示完整备份,为1表示差异备份</param>
        /// <returns></returns>
        public bool BackUPDB(string strFileName, int BackUpType)
        {
            //如果是差异备份,就是看一下文件是否存在,如果不存在,就不执行
            if (BackUpType == 1 && File.Exists(strFileName) == false)
            {
                return false;
            }

            bool result = false;

            try
            {
                string[] strConnSqlArr = strConnSql.Split(‘;‘);

string DBName = strConnSqlArr[4].ToString().Split(‘=‘)[1].ToString();//数据库名称

string backUp_full = string.Format("backup database {0} to disk = ‘{1}‘ ;", DBName, strFileName);

string backUp_Diff = string.Format("backup database {0} to disk=‘{1}‘ WITH DIFFERENTIAL ;", DBName, strFileName);

WKK.DBUtility.DbHelperSQL.ExecuteSql(BackUpType == 0 ? backUp_full : backUp_Diff, 600);

                result = true;
            }
            catch (Exception ex)
            {
                Common.Log.WriteLog(string.Format("备份{0}数据库失败", BackUpType == 0 ? "完整" : "差异"), ex);

                // System.Diagnostics.Debug.WriteLine(string.Format("备份{0}数据库失败", BackUpType == 0 ? "完整" : "差异"));
                result = false;
            }
            finally
            {

                if (result == true)
                {
                    string str_InfoContent = string.Format("备份{0}数据库成功", BackUpType == 0 ? "完整" : "差异");

                    // System.Diagnostics.Debug.WriteLine(str_InfoContent);
                }
            }
            return result;
        }

        /// <summary>
        /// 还原数据库使用Sql语句
        /// </summary>
        /// <param name="strDbName">数据库名</param>
        /// <param name="strFileName">备份文件名</param>
        public bool RestoreDB(string strDbName, string strFileName)
        {


            bool result = false;
            try
            {
                string strConnSql = ConfigurationSettings.AppSettings["ConnectionString"].ToString();

string[] strConnSqlArr = strConnSql.Split(‘;‘);

string DBName = strConnSqlArr[4].ToString().Split(‘=‘)[1].ToString();//数据库名称

                #region 关闭所有访问数据库的进程,否则会导致数据库还原失败闫二永 17:39 2014/3/19
                string cmdText = String.Format("EXEC sp_KillThread @dbname=‘{0}‘", DBName);
                WKK.DBUtility.DbHelperSQL.connectionString = strConnSql.Replace(DBName, "master");
                WKK.DBUtility.DbHelperSQL.ExecuteSql(cmdText);
                #endregion

                string Restore = string.Format("RESTORE DATABASE {0} FROM DISK=‘{1}‘WITH replace", DBName, strFileName);
                WKK.DBUtility.DbHelperSQL.ExecuteSql(Restore, 600);
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("还原数据库失败\r\n" + ex.Message, "系统提示!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                Common.Log.WriteLog(string.Format("还原数据库失败--{0}", DateTime.Now.ToString()), ex);
                result = false;
            }
            finally
            {
                //恢复成功后需要重启程序
                if (result)
                {

                  //
                }
            }
            return result;
        }

        /// <summary>
        /// 执行一条SQL语句
        /// </summary>
        /// <param name="SQLStringList">sql语句</param>
        /// <param name="SetTimeout"> 等待连接打开的时间（以秒为单位）。默认值为 15 秒。 </param>
        /// <returns></returns>
        public static int ExecuteSql(string SQLString, int setTimeOut)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = setTimeOut;
                        int rows = cmd.ExecuteNonQuery();
                        connection.Close();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }