Esempio n. 1
0
        /// <summary>
        /// 生成备份包
        /// </summary>
        public static void CreateBackup()
        {
            string dbname = DBTools.GetDbName();
            //要备份的位置
            string dbfullname = DBTools.GetDbPath() + string.Format("{0}_{1}.mdf", dbname, DateTime.Now.ToString("yyyyMMddhhmmss"));

            //判断文件是否存在
            if (File.Exists(dbfullname))
            {
                throw new Exception(dbfullname + "的备份文件已经存在,请稍后再试");
            }
            try
            {
                SqlConnection con = DBTools.GetConnection();

                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 @kid=@kid+'KILL '+CAST(spid as Varchar(10))  FROM master..sysprocesses    
                    WHERE dbid=DB_ID(@dbName)    
                    PRINT @kid    
                    EXEC(@kid);  
                    backup database te to disk=@dbFullName;");

                    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();
                }
                finally
                {
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("创建数据库备份出错:" + ex);
            }
        }
Esempio n. 2
0
        /// <summary>
        /// 还原数据库文件
        /// </summary>
        /// <param name="restoreFileName">数据库名字</param>
        public static void Restore(string restoreFileName)
        {
            //1.获取还原数据库和文件
            string dbName     = DBTools.GetDbName();
            string dbFullName = DBTools.GetDbPath() + restoreFileName;

            try
            {
                //2.执行还原操作
                SqlConnection con = DBTools.GetConnection();
                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 '+@dbName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');  
                --1.2结束链接进程  
                    DECLARE @kid varchar(max)    
                    SET @kid=''    
                    SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10))  FROM master..sysprocesses    
                    WHERE dbid=DB_ID(@dbName)  ;  
                    EXEC(@kid) ;  
                --2.执行还原语句  
                    restore database @dbName from  disk=@dbFullName  
                    with replace  --覆盖现有的数据库  
                --3.重置数据库为多用户模式  
                    exec(N'ALTER DATABASE '+@dbName+' 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();
                }
                finally
                {
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("还原数据库出错" + ex);
            }
        }