当前位置:数据库 > SQLServer >>

.net数据库的备份还原方法

程序的安全性很重要,其中表现为防止恶意代码对程序的破坏性及对数据库教程的备份,以防止程序破坏后可以及时还原得回数据.

1.备份

   

     SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["Connection"]);
        string dbFileName = DateTime.Now.ToString("yyyyMMddHHmmss")+".bak";
        try
        {
            SqlCommand command = new SqlCommand("use master;backup database xxdl to disk=@path;", connection);
            connection.Open();
            string path = Server.MapPath("~App_Data") + "" + dbFileName;
            command.Parameters.AddWithValue("@path", path);
            command.ExecuteNonQuery();
            lblBackup.Text = "数据库备份成功";
        }
        catch (Exception ex)
        {

            lblBackup.Text = ex.Message;
        }
        finally
        {
            connection.Close();
        }
       
        Bind();//产生了新备份文件,更新下拉框

2 还原

        SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Connection"]);
        string dbFileName = ddlRestore.SelectedValue;
        if (dbFileName == string.Empty)
        {
            lblRestore.Text = "没有数据库备份文件,请先备份";
            return;
        }
        try
        {
         string sql2 = "Alter Database xxdl Set Offline with Rollback immediate;use master;restore database xxdl  from disk=@path With Replace;Alter Database xxdl  Set OnLine With rollback Immediate;";
         SqlCommand command = new SqlCommand(sql2,conn);
         conn.Open();
         string path = Server.MapPath("~App_Data") + "" + dbFileName;
         command.Parameters.AddWithValue("@path", path);
         command.ExecuteNonQuery();
            lblRestore.Text = "数据库还原成功";
        }
        catch (System.Exception ex)
        {
            lblRestore.Text = ex.Message;
        }
        finally
        {
            conn.Close();
            Session.Abandon();
            Response.Redirect("login.asp教程x");
        }


 

方法二

功能:
   自动识别Web.Config配置信息.
   备份与还原数据库.
代码如下:

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

 

namespace OA.Web
{

    /// <summary>
    /// SqlDbBackUpRestore类,主要应用SQLDMO实现对Microsoft SQL Server数据库 的备份和恢复
    /// 请先给项目添加COM(Mircrosoft SQLDMO Object Library)引用
    /// </summary>

    public class SqlDbBackUpRestore
    {
        /// <summary>
        /// sql服务器名称或者IP地址
        /// </summary>
        private string server;

        /// <summary>
        ///用户名称
        /// </summary>
        private string uid;

        /// <summary>
        /// 用户密码
        /// </summary>
        private string pwd;

        /// <summary>
        /// 数据库名称
        /// </summary>
        private string database;

        /// <summary>
        /// Web.Config连接字符串
        /// </summary>
        private string conn;


        /// <summary>
        /// SqlDbBackUpRestore类的构造函数
        /// </summary>
        public SqlDbBackUpRestore()
        {

            conn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"].ToString();

            server = cut(conn, "server=", ";");

            uid = cut(conn, "uid=", ";");

            pwd = cut(conn, "pwd=", ";");

            database = cut(conn, "database=", ";");

        }

        public string cut(string str, string bg, string ed)
        {

            string sub;

            sub = str.Substring(str.IndexOf(bg) + bg.Length);

            sub = sub.Substring(0, sub.IndexOf(";"));

            return sub;

        }

        /// <summary>
        /// 数据库database备份
        /// </summary>
        public bool DbBackup(string url)
        {

            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();

            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();

            try
            {

                oSQLServer.LoginSecure = false;

                oSQLServer.Connect(server, uid, pwd);

                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;

                oBackup.Database = database;

                oBackup.Files = url;//"d:/Northwind.bak";

                oBackup.BackupSetName = database;

                oBackup.BackupSetDescription = "数据库备份";

                oBackup.Initialize = true;

                oBackup.SQLBackup(oSQLServer);

                return true;
            }

            catch
            {
                return false;
                throw;
            }

            finally
            {
                oSQLServer.DisConnect();
            }

        }

 

        /// <summary>
        /// 数据库database恢复
        /// </summary>

        public string DbRestore(string url)
        {

            if (KillProcesses() != true)//执行存储过程
            {

                return "操作失败";

            }
            else
            {

                SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();

                SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();

                try
                {

                    oSQLServer.LoginSecure = false;

                    oSQLServer.Connect(server, uid, pwd);

                    oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;

                    oRestore.Database = database;

                    oRestore.Files = url;//@"d:/Northwind.bak";

                    oRestore.FileNumber = 1;

                    oRestore.ReplaceDatabase = true;

                    oRestore.SQLRestore(oSQLServer);

                    return "OK";

                }

                catch (Exception e)
                {
                    return "恢复数据库失败";
                    throw;
                }
                finally
                {
                    oSQLServer.DisConnect();
                }

            }

        }

        /// <summary>
        /// 杀死Sql database 连接进程
        /// </summary>
        /// <returns></returns>
        private bool KillProcesses()
        {

            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(server, uid, pwd);
                SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
                int iColPIDNum = -1;
                int iColDbName = -1;
                for (int i = 1; i <= qr.Columns; i++)
                {
                    string strName = qr.get_ColumnName(i);
                    if (strName.ToUpper().Trim() == "SPID")
                    {
                        iColPIDNum = i;
                    }
                    else if (strName.ToUpper().Trim() == "DBNAME")
                    {
                        iColDbName = i;
                    }
                    if (iColPIDNum != -1 && iColDbName != -1)
                        break;
                }

                for (int i = 1; i <= qr.Rows; i++)
                {
                    int lPID = qr.GetColumnLong(i, iColPIDNum);
                    string strDBName = qr.GetColumnString(i, iColDbName);
                    if (strDBName.ToUpper() == database.ToUpper())
                    {

                        svr.KillProcess(lPID);
                    }
                }
                return true;
            }
            catch
            {
                //失败
                return false;
            }
            finally
            {
                svr.DisConnect();
            }

        }

    }

}

access数据库还原备份

Asp.net 备份和还原SQL Server及压缩Access数据库2009年11月17日 星期二 12:27*
* 功能说明:备份和恢复SQL Server数据库
* 作者: 刘功勋;
* 版本:V0.1(C#2.0);时间:2007-1-1
* 当使用SQL Server时,请引用 COM组件中的,SQLDMO.dll组件
* 当使用Access中,请浏览添加引用以下两个dll
*          引用C:/Program Files/Common Files/System/ado/msadox.dll,该DLL包含ADOX命名空间
*          引用C:/Program Files/Common Files/System/ado/msjro.dll,该DLL包含JRO命名空间
* *******************************************************************************/
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using ADOX;//该命名空间包含创建ACCESS的类(方法)--解决方案 ==> 引用 ==> 添加引用 ==> 游览找到.dll
using JRO;//该命名空间包含压缩ACCESS的类(方法)

namespace EC
{
    /// <summary>
    /// 数据库恢复和备份
    /// </summary>
    public class SqlBackObject
    {
        public SqlBackObject()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        #region SQL数据库备份
       /// <summary>
        /// SQL数据库备份
       /// </summary>
       /// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
       /// <param name="LoginName">数据库登录名</param>
       /// <param name="LoginPass">数据库登录密码</param>
       /// <param name="DBName">数据库名</param>
       /// <param name="BackPath">备份到的路径</param>
        public static void SQLBACK(string ServerIP,string LoginName,string LoginPass,string DBName,string BackPath)
        {
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(ServerIP, LoginName, LoginPass);
                oBackup.Database = DBName;
                oBackup.Files = BackPath;
                oBackup.BackupSetName = DBName;
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);

            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }
        #endregion

        #region SQL恢复数据库
        /// <summary>
        /// SQL恢复数据库
        /// </summary>
        /// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
        /// <param name="LoginName">数据库登录名</param>
        /// <param name="LoginPass">数据库登录密码</param>
        /// <param name="DBName">要还原的数据库名</param>
        /// <param name="BackPath">数据库备份的路径</param>

        public static void SQLDbRestore(string ServerIP,string LoginName,string LoginPass,string DBName,string BackPath)
        {
          
            SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(ServerIP, LoginName, LoginPass);
                orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                orestore.Database = DBName;
                orestore.Files = BackPath;
                orestore.FileNumber = 1;
                orestore.ReplaceDatabase = true;
                orestore.SQLRestore(oSQLServer);

            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }


        #endregion

        #region 根据指定的文件名称创建Access数据库
        /// <summary>
        /// 根据指定的文件名称创建数据
        /// </summary>
        /// <param name="DBPath">绝对路径+文件名称</param>
        public static void CreateAccess(string DBPath)
        {
            if (File.Exists(DBPath))//检查数据库是否已存在
            {
                throw new Exception("目标数据库已存在,无法创建");
            }          
            DBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+DBPath;
            //创建一个CatalogClass对象实例
            ADOX.CatalogClass cat = new ADOX.CatalogClass();
            //使用CatalogClass对象的Create方法创建ACCESS数据库
            cat.Create(DBPath);

        }
        #endregion

        #region 压缩Access数据库
        /// <summary>
        /// 压缩Access数据库
        /// </summary>
        /// <param name="DBPath">数据库绝对路径</param>
        public static void CompactAccess(string DBPath)
        {
            if (!File.Exists(DBPath))
            {
                throw new Exception("目标数据库不存在,无法压缩");
            }
          
            //声明临时数据库名称
            string temp = DateTime.Now.Year.ToString();
            temp += DateTime.Now.Month.ToString();
            temp += DateTime.Now.Day.ToString();
            temp += DateTime.Now.Hour.ToString();
            temp += DateTime.Now.Minute.ToString();
            temp += DateTime.Now.Second.ToString() + ".bak";
            temp = DBPath.Substring(0, DBPath.LastIndexOf("//") + 1) + temp;
            //定义临时数据库的连接字符串
            string temp2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+temp;
            //定义目标数据库的连接字符串
            string DBPath2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+DBPath;
            //创建一个JetEngineClass对象的实例
            JRO.JetEngineClass jt = new JRO.JetEngineClass();
            //使用JetEngineClass对象的CompactDatabase方法压缩修复数据库
            jt.CompactDatabase(DBPath2, temp2);
            //拷贝临时数据库到目标数据库(覆盖)
            File.Copy(temp, DBPath, true);
            //最后删除临时数据库
            File.Delete(temp);
        }
        #endregion

        #region 备份Access数据库
        /// <summary>
        /// 备份Access数据库
        /// </summary>
        /// <param name="srcPath">要备份的数据库绝对路径</param>
        /// <param name="aimPath">备份到的数据库绝对路径</param>
        /// <returns></returns>
        public static void Backup(string srcPath,string aimPath)
        {
           
            if (!File.Exists(srcPath))
            {
                throw new Exception("源数据库不存在,无法备份");
            }
            try
            {
                File.Copy(srcPath,aimPath,true);
            }
            catch(IOException ixp)
            {
                throw new Exception(ixp.ToString());
            }
           
        }

        #endregion

        #region 还原Access数据库
        /// <summary>
        /// 还原Access数据库
        /// </summary>
        /// <param name="bakPath">备份的数据库绝对路径</param>
        /// <param name="dbPath">要还原的数据库绝对路径</param>
        public static void RecoverAccess(string bakPath,string dbPath)
        {          
            if (!File.Exists(bakPath))
            {
                throw new Exception("备份数据库不存在,无法还原");
            }
            try
            {
                File.Copy(bakPath, dbPath, true);
            }
            catch (IOException ixp)
            {
                throw new Exception(ixp.ToString());
            }       
        }       
        #endregion
    }
}


 

补充:数据库,Mssql
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,