/// <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);
}
}
/// <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);
}
}