using System;    using System.Data;    using System.Data.SqlClient;using System.Collections;namespace BackUpDataBase    {        /// <summary>        /// 备份和还原sql server 2000数据库,在asp.net中文正式版和sql server 2000系统上通过        /// </summary>        public class BackupData    {        private SqlConnection conn;        string name;        string id;        string pass;        string sqlstr;        public BackupData(string name, string id, string pass)        {            this.name = name;            this.id = id;            this.pass = pass;            sqlstr = "data source=" + name + ";" + "initial catalog=master;password=" + pass + ";" + "persist security info=True;user id=" + id + ";" + "workstation id=TOPS03496;packet size=4096";//注意默认数据库不要和恢复的数据库同名            }         /// <summary>            /// 备份数据库            /// </summary>            /// <param name="databasename">要备份的数据源名称</param>            /// <param name="backuptodatabase">备份到的数据库文件名称及路径</param>            /// <returns></returns>            public bool BackUpDataBase(string databasename, string backuptodatabase)        {            string procname;            string name = databasename + "-" + DateTime.Now.Year.ToString()+"年" + DateTime.Now.Month.ToString()+"月" + DateTime.Now.Date.Day.ToString()+"日" + DateTime.Now.Hour + "时" + DateTime.Now.Minute.ToString()+"分";            string sql;                   //打开数据库连接                //删除逻辑备份设备，但不会删掉备份的数据库文件                procname = "sp_dropdevice";            conn = new SqlConnection(sqlstr);            conn.Open();             SqlCommand sqlcmd1 = new SqlCommand(procname, conn);            sqlcmd1.CommandType = CommandType.StoredProcedure;             SqlParameter sqlpar = new SqlParameter();            sqlpar = sqlcmd1.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);            sqlpar.Direction = ParameterDirection.Input;            sqlpar.Value = databasename;             try        //如果逻辑设备不存在，略去错误                {                sqlcmd1.ExecuteNonQuery();            }            catch            {                //throw;            }             //创建逻辑备份设备                procname = "sp_addumpdevice";            SqlCommand sqlcmd2 = new SqlCommand(procname, conn);            sqlcmd2.CommandType = CommandType.StoredProcedure;             sqlpar = sqlcmd2.Parameters.Add("@devtype", SqlDbType.VarChar, 20);            sqlpar.Direction = ParameterDirection.Input;            sqlpar.Value = "disk";             sqlpar = sqlcmd2.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);//逻辑设备名                sqlpar.Direction = ParameterDirection.Input;            sqlpar.Value = databasename;             sqlpar = sqlcmd2.Parameters.Add("@physicalname", SqlDbType.NVarChar, 260);//物理设备名                sqlpar.Direction = ParameterDirection.Input;//            sqlpar.Value = backuptodatabase + name + ".bak";            sqlpar.Value = name+".bak";             try            {                int i = sqlcmd2.ExecuteNonQuery();            }            catch (Exception err)            {                string str = err.Message;            }             //备份数据库到指定的数据库文件和日志(完全备份)                            sql = "BACKUP DATABASE " + databasename + " TO " + databasename + " WITH INIT";            SqlCommand sqlcmd3 = new SqlCommand(sql, conn);            sqlcmd3.CommandType = CommandType.Text;            try            {                sqlcmd3.ExecuteNonQuery();            }            catch (Exception err)            {                string str = err.Message;                conn.Close();                return false;            }             conn.Close();//关闭数据库连接                return true;        }        /// <summary>            /// 还原指定的数据库文件            /// </summary>            /// <param name="databasename">要还原的数据库</param>            /// <param name="databasefile">数据库备份文件及路径</param>            /// <returns></returns>            public bool RestoreDatabase(string databasename, string databasefile) //杀死数据库进程        {             conn = new SqlConnection(sqlstr);            conn.Open();            string sql = "SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='" + databasename + "'";            SqlCommand cmd1 = new SqlCommand(sql, conn);            SqlDataReader dr;            ArrayList list = new ArrayList();            try            {                dr = cmd1.ExecuteReader();                while (dr.Read())                {                    list.Add(dr.GetInt16(0));                }                dr.Close();                 for (int i = 0; i < list.Count; i++)                {                    cmd1 = new SqlCommand(string.Format("KILL {0}", list[i].ToString()), conn);                    cmd1.ExecuteNonQuery();                }            }            catch            {                return false;            }            //finally            //{            conn.Close();             //}            DbRestore(databasename, databasefile);            return true;        }        /// <summary>            /// 还原指定的数据库文件            /// </summary>            /// <param name="databasename">要还原的数据库</param>            /// <param name="databasefile">数据库备份文件及路径</param>            /// <returns></returns>            //还原指定的数据库文件            public bool DbRestore(string databasename, string databasefile)        {                        conn = new SqlConnection(sqlstr);            conn.Open();            string sql = "RESTORE DATABASE " + databasename + " from DISK = '" + databasefile + "'" + "WITH REPLACE";            SqlCommand sqlcmd = new SqlCommand(sql, conn);            sqlcmd.CommandType = CommandType.Text;             try            {                sqlcmd.ExecuteNonQuery();            }            catch (Exception err)            {                string str = err.Message;                conn.Close();                return false;            }             conn.Close();//关闭数据库连接                return true;        }           }}

发表于 @ 2008年06月25日　17:28:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:电子签章控件开发要点整理 | 新一篇:浅谈神华集团资源平台项目实施关键因素