先附上参考网站:https://www.cnblogs.com/zk-zhou/p/6808070.html
sql数据库备份恢复语句:
(1)数据备份语句:backup database 数据库名 to disk='[保存路径]/[名字].bak'
(2)数据恢复语句:restore database 数据库名 from disk='[保存路径]/[名字].bak' WITH MOVE 'dbName_Data' TO 'c:/tcomcrm20041217.mdf', --数据文件还原后存放的新位置
MOVE 'dbName_Log' TO 'c:/comcrm20041217.ldf' ----日志文件还原后存放的新位置
下划线部分不是很能理解,T^T,但是单纯把还原语句放入sqlcommand,也会出现错误;
然后通过各种尝试做出了一下调整:
首先是备份:
界面:
确定备份的按钮代码
private void button2_Click(object sender, EventArgs e)
{
string strDB = textBox2.Text.ToString().Trim();
if (MessageBox.Show("是否备份数据", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
if (textBox1.Text.ToString() !="")
{
//设置连接字符串
SqlConnection conn = GetConn(); //调用连接数据库的
//实例化SQL可执行的存储过程
SqlCommand cmdBK = new SqlCommand();
//SQL文本
cmdBK.CommandType = CommandType.Text;
cmdBK.Connection = conn;
// DateTime dtm = new DateTime();
string x = textBox1.Text.ToString().Trim();
string strRiQi = DateTime.Now.Year.ToString() + (DateTime.Now.Month.ToString().Length < 2 ? 0 + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString()) + (DateTime.Now.Day.ToString().Length < 2 ? 0 + DateTime.Now.Day.ToString() : DateTime.Now.Day.ToString()) + (DateTime.Now.Hour.ToString().Length < 2 ? 0 + DateTime.Now.Hour.ToString() : DateTime.Now.Hour.ToString()) + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
cmdBK.CommandText = @"backup database OccupationalDisease to disk = '" + x + '\\' + strDB+ ".bak'";
try
{
//进入SQL
conn.Open();
//返回影响行数
cmdBK.ExecuteNonQuery();
MessageBox.Show("备份成功!");
this.Dispose();//释放资源
this.Close();//关闭
}
catch (Exception)
{
MessageBox.Show("备份失败");
}
finally
{
conn.Close();//关闭与SQL的连接
}
}
else
{
MessageBox.Show("请选择保存路径!");
}
}
然后还原数据库:
sql还原数据库代码
use master;
Alter database OccupationalDisease Set Offline With rollback immediate;
RESTORE DATABASE OccupationalDisease FROM DISK = 'C:\Users\57069\Desktop\测试1.bak' With Replace ; --存放你备份文件的位置及文件名
Alter database OccupationalDisease Set Online With Rollback immediate
注意两点:
①在代码前面用use master;
②还原语句后面加上with replace 就是如果重名了就覆盖不然会出错。
简单的界面:
代码:
private void button3_Click(object sender, EventArgs e)
{
string x = textBox3.Text.ToString().Trim();
try
{
string sql = @"use master;Alter database OccupationalDisease Set Offline With rollback immediate;restore database OccupationalDisease from disk='" + x + "' With Replace;Alter database OccupationalDisease Set Online With Rollback immediate;";
SqlConnection con = GetConn();
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
MessageBox.Show("还原成功!");
con.Close();
}
catch (Exception ex)
{
MessageBox.Show("还原失败");
}
}