郑德才博客

//点击恢复按钮，执行ReBakData恢复方法，传入数据库名和要恢复数据库的路径
protected void btnOK_Click(object sender, EventArgs e)
{
try
{
if (DropDownList1.Text.Trim() != "")
{
//string dbFileName = txtDbFileName.Text.Trim();
string dbFileName = DropDownList1.Text.Trim();
if (!dbFileName.EndsWith(".bak"))
{
dbFileName += ".bak";
}
string Path = Server.MapPath("~//DbFile") + "\\" + dbFileName;

string dbName = ddlDatabaseList.SelectedValue;
bakServer bak = new bakServer();
if (bak.ReBakData(dbName, Path))
{
MessageBox.Show(this, "恢复数据成功！");
}
else
{
MessageBox.Show(this, "恢复数据失败！");
}
}
else
{
MessageBox.Show(this, "请选择要恢复数据库！");
}
}
catch
{
MessageBox.Show(this, "恢复数据出错！");
}
}
//备份数据库，返回备份是否成功
public bool ReBakData(string DbName, string Path)
{
bool bl = false;
SqlConnection connection = null;
try
{
if (BakData())
{
connection = new SqlConnection(ConnHelper.getConnectionString());
connection.Open();
string sql = "Alter Database " + DbName + " Set Offline with Rollback immediate;restore database " + DbName + " from disk='" + Path + "' with REPLACE;Alter Database " + DbName + " Set OnLine With rollback Immediate;";
SqlCommand command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() != 0)
bl = true;
else
bl = false;
}
else
{
bl = false;
}
}
catch
{
bl = false;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return bl;
}
//建立存储过程和备份一样
public bool BakData()
{
string DqStr = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[killspid ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[killspid ];";

string Str = "create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status<>-1 begin exec('kill '+@spid) fetch next from getspid into @spid end close getspid deallocate getspid end";
bool bl = false;
SqlConnection connection = null;
try
{
connection = new SqlConnection(ConnHelper.getConnectionString());
connection.Open();
SqlCommand command = new SqlCommand(DqStr, connection);
if (command.ExecuteNonQuery() != 0)
{
command = new SqlCommand(Str, connection);
if (command.ExecuteNonQuery() != 0)
{
bl = true;
}
else
{
bl = false;
}
}
else
{
bl = false;
}

}
catch
{
bl = false;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return bl;
}