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

namespace bachupsqlserver
{
    /// <summary>
    /// 备份和还原sql server 2000数据库,在asp.net中文正式版和sql server 2000系统上通过
    /// </summary>
    public class BackupData
    {
        private SqlConnection conn;
        public BackupData()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            string sql="data source=localhost;initial catalog=master;password=;persist security info=True;user id=sa;workstation id=TOPS03496;packet size=4096";//注意默认数据库不要和恢复的数据库同名

            init(sql);
        }

        /// <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.Minute.ToString();
            string sql;

            conn.Open();        //打开数据库连接

            //删除逻辑备份设备，但不会删掉备份的数据库文件
            procname="sp_dropdevice";
            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
            {
            }

            //创建逻辑备份设备
            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";

            
            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 )
        {

            //还原指定的数据库文件
            string sql="RESTORE DATABASE "+databasename +" from DISK = '"+databasefile +"' ";
            SqlCommand sqlcmd=new SqlCommand(sql,conn);
            sqlcmd.CommandType =CommandType.Text;

            conn.Open();

            try
            {
                sqlcmd.ExecuteNonQuery();
            }
            catch(Exception err)
            {
                string str=err.Message ;
                conn.Close();

                return false;
            }

            conn.Close();//关闭数据库连接
            return true;
        }

        /// <summary>
        /// 初始化数据库的连接
        /// </summary>
        /// <param name="strconn"></param>
        private void init(string strconn)
        {
            conn=new SqlConnection(strconn);

        }
    }
}