在前几日的项目中我使用的SQL语句方法,本来是用SQLDMO的,但就为了这小功能多出现个DLL,感觉有点大材小用了。
所以有了以下代码的产生:
备份:
SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID="用户名";Password="密码");
SqlCommand cmdBK = new SqlCommand();
cmdBK.CommandType = CommandType.Text;
cmdBK.Connection = conn;
cmdBK.CommandText = @"backup database 数据库名to disk='" + 备份路径 + "'";
try
{
conn.Open();
cmdBK.ExecuteNonQuery();
MessageBox.Show("备份成功");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
conn.Dispose();
}
还原:
SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID="用户名";Password="密码";Trusted_Connection=False");
try
{
conn.Open();
string setOffline = "Alter database 数据库名 Set Offline With rollback immediate ";
string setOnline = " Alter database 数据库名 Set Online With Rollback immediate";
string sqlstr = @"restore filelistonly from disk='" + 还原文件路径 + "'";
SqlCommand cmdRT = new SqlCommand(setOffline + sqlstr + setOnline, conn);
cmdRT.ExecuteNonQuery();
MessageBox.Show("还原成功");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
这里:SqlCommand一共执行了三条SQL语句。先是,setOffline,作用是使数据库离线,然后就是执行还原操作:restore filelistonly ,最后是setOnline,使数据库进程在线。