SqlServer T-Sql实现数据库完整备份和还原

一、SqlServer T-Sql实现数据库完整备份和还原

本中方式整理总结为3.0的版本

本次整理说明：

1.不再使用存储过程模式，直接使用t-sql语句处理

2.解决数据库还原“因为数据库正在使用，所以无法获得对数据库的独占访问权”的问题，更多参考：Sql server还原失败(数据库正在使用,无法获得对数据库的独占访问权)

二、Sql整理

1.备份语句

use master;
go
---声明变量
declare @dbName nvarchar(max)='Mvc_HNHZ';
declare @dbFullName nvarchar(max)='E:\NewWork\Web\backup\test1.bak';
---备份数据库
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 Mvc_HNHZ to disk=@dbFullName;

2.还原语句

use master
go
---声明变量
declare @dbName nvarchar(max)='Mvc_HNHZ';
declare @dbFullName nvarchar(max)='E:\NewWork\Web\backup\test1.bak';
--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');

三、Ado.Net 关键代码

1.备份操作

/// <summary>
/// 创建数据库备份
/// </summary>
public static void CreateBackup()
{
    string dbname = GetDbName();
    //要备份的位置
    string dbfullname = GetDbPath() + string.Format("{0}_{1}.bak", dbname, DateTime.Now.ToString("yyyyMMddhhmmss"));
    //判断文件是否存在
    if (File.Exists(dbfullname))
    {
        throw new Exception(dbfullname + "的备份文件已经存在，请稍后再试");
    }
    try
    {
        SqlConnection con = DbHelperSQL.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 Mvc_HNHZ 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);
    }
}

2.还原操作
/// <summary>
/// 还原数据库
/// </summary>
public static void Restore(string restoreFileName)
{
    //1.获取还原数据库和文件
    string dbName = GetDbName();
    string dbFullName = GetDbPath() + restoreFileName;
    try
    {
        //2.执行还原操作
        SqlConnection con = DbHelperSQL.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);
    }
}

更多：
Sql server还原失败(数据库正在使用,无法获得对数据库的独占访问权)
SqlServer消息 6107，级别 14 只能终止用户进程。