C#实现SQL数据库备份与恢复(zzfrom:http://www.cnblogs.com/icwin/articles/1285657.html)
来源:互联网 发布:无冬之夜2 mac 编辑:程序博客网 时间:2024/05/16 10:37
有两种方法,都是保存为.bak文件。一种是直接用Sql语句执行,另一种是通过引用SQL Server的SQLDMO组件来实现:
1.通过执行Sql语句来实现
注意,用Sql语句实现备份与还原操作时,最好不要使用需要备份或还原的数据库连接,而使用master,否则可能会出现如下三个问题:(1)超时时间已到。在操作完成之前超时时间已过或服务器未响应。(2) 在向服务器发送请求时发生传输级错误。(provider:共享内存提供程序,error:0-系统无法打开文件。) (3)从服务器接收结果时发生传输级错误。(provider:共享内存提供程序,error:0 - 系统无法打开文件。) ,如果一定要用这个连接的话,要注意在执行Sql语句前加个Sql语句:use master,这样可能会解决以上问题。
(1)数据备份语句:backup database 数据库名 to disk='保存路径/dbName.bak'
(2)数据恢复语句:restore database 数据库名 from disk='保存路径/dbName.bak' WITH MOVE 'dbName_Data' TO 'c:/tcomcrm20041217.mdf', --数据文件还原后存放的新位置
MOVE 'dbName_Log' TO 'c:/comcrm20041217.ldf' ----日志文件还原后存放的新位置
MOVE 'dbName_Log' TO 'c:/comcrm20041217.ldf' ----日志文件还原后存放的新位置
关于这两个语句还有更详细的介绍:http://blog.csdn.net/holyrong/archive/2007/08/29/1764105.aspx
//数据库备份与恢复实例
private void btnBak_Click(object sender, EventArgs e) //备份
{
string saveAway = this.tbxBakLoad.Text.ToString().Trim();
string cmdText = @"backup database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " to disk='" + saveAway + "'";
BakReductSql(cmdText,true);
}
{
string saveAway = this.tbxBakLoad.Text.ToString().Trim();
string cmdText = @"backup database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " to disk='" + saveAway + "'";
BakReductSql(cmdText,true);
}
private void btnReduct_Click(object sender, EventArgs e) //恢复
{
string openAway = this.tbxReductLoad.Text.ToString().Trim();//读取文件的路径
string cmdText = @"restore database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " from disk='" + openAway + "'";
BakReductSql(cmdText,false);
}
{
string openAway = this.tbxReductLoad.Text.ToString().Trim();//读取文件的路径
string cmdText = @"restore database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " from disk='" + openAway + "'";
BakReductSql(cmdText,false);
}
/// <summary>
/// 对数据库的备份和恢复操作,Sql语句实现
/// </summary>
/// <param name="cmdText">实现备份或恢复的Sql语句</param>
/// <param name="isBak">该操作是否为备份操作,是为true否,为false</param>
private void BakReductSql(string cmdText,bool isBak)
{
SqlCommand cmdBakRst = new SqlCommand();
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=master;uid=sa;pwd=;");
/// 对数据库的备份和恢复操作,Sql语句实现
/// </summary>
/// <param name="cmdText">实现备份或恢复的Sql语句</param>
/// <param name="isBak">该操作是否为备份操作,是为true否,为false</param>
private void BakReductSql(string cmdText,bool isBak)
{
SqlCommand cmdBakRst = new SqlCommand();
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=master;uid=sa;pwd=;");
try
{
conn.Open();
cmdBakRst.Connection = conn;
cmdBakRst.CommandType = CommandType.Text;
if (!isBak) //如果是恢复操作
{
string setOffline = "Alter database GroupMessage Set Offline With rollback immediate ";
string setOnline = " Alter database GroupMessage Set Online With Rollback immediate";
cmdBakRst.CommandText = setOffline + cmdText + setOnline;
}
else
{
cmdBakRst.CommandText = cmdText;
}
cmdBakRst.ExecuteNonQuery();
if (!isBak)
{
MessageBox.Show("恭喜你,数据成功恢复为所选文档的状态!", "系统消息");
}
else
{
MessageBox.Show("恭喜,你已经成功备份当前数据!", "系统消息");
}
}
catch (SqlException sexc)
{
MessageBox.Show("失败,可能是对数据库操作失败,原因:" + sexc, "数据库错误消息");
}
catch (Exception ex)
{
MessageBox.Show("对不起,操作失败,可能原因:" + ex, "系统消息");
}
finally
{
cmdBakRst.Dispose();
conn.Close();
conn.Dispose();
}
}
{
conn.Open();
cmdBakRst.Connection = conn;
cmdBakRst.CommandType = CommandType.Text;
if (!isBak) //如果是恢复操作
{
string setOffline = "Alter database GroupMessage Set Offline With rollback immediate ";
string setOnline = " Alter database GroupMessage Set Online With Rollback immediate";
cmdBakRst.CommandText = setOffline + cmdText + setOnline;
}
else
{
cmdBakRst.CommandText = cmdText;
}
cmdBakRst.ExecuteNonQuery();
if (!isBak)
{
MessageBox.Show("恭喜你,数据成功恢复为所选文档的状态!", "系统消息");
}
else
{
MessageBox.Show("恭喜,你已经成功备份当前数据!", "系统消息");
}
}
catch (SqlException sexc)
{
MessageBox.Show("失败,可能是对数据库操作失败,原因:" + sexc, "数据库错误消息");
}
catch (Exception ex)
{
MessageBox.Show("对不起,操作失败,可能原因:" + ex, "系统消息");
}
finally
{
cmdBakRst.Dispose();
conn.Close();
conn.Dispose();
}
}
另外,如果出现:“尚未备份数据库的日志尾部”错误,可以在还原语句后加上 With Replace 或 With stopat
2.用SQLDMO实现(下面代码引用别人的)
//数据库备份
string backaway =textbox1.Text.Trim();
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
//下面设置登录sql服务器的ip,登录名,登录密码
oSQLServer.Connect(serverip, serverid, serverpwd);
oBackup.Action = 0;
//下面两句是显示进度条的状态
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step2);
oBackup.PercentComplete += pceh;
//数据库名称:
oBackup.Database = "k2";
//备份的路径
oBackup.Files = @backaway;
//备份的文件名
oBackup.BackupSetName = "k2";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
MessageBox.Show("备份成功!", "提示");
}
catch
{
MessageBox.Show("备份失败!", "提示");
}
finally
{
oSQLServer.DisConnect();
}
//数据库恢复
//获取恢复的路径
string dbaway = textbox2.Text.Trim();
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
server.Connect(serverip, serverid, serverpwd);
//KILL DataBase Process
conn = new 工资管理系统.CCUtility.connstring();
conn.DBOpen();
SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='k2'", conn.Connection);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
server.KillProcess(Convert.ToInt32(dr[0].ToString()));
}
dr.Close();
conn.DBClose();
try
{
restore.Action = 0;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
restore.PercentComplete += pceh;
restore.Database = "k2";
restore.Files = @dbaway;
restore.ReplaceDatabase = true;
restore.SQLRestore(server);
MessageBox.Show("数据库恢复成功!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
恢复相关的参数和备份相同,不再解释,自己看一下.
上面两个函数调用到了更改进度条的两个函数:
private void Step2(string message, int percent)
{
progressBar2.Value = percent;
}
private void Step(string message, int percent)
{
progressBar1.Value = percent;
}
setp对应备份,,setp2对应恢复....
string backaway =textbox1.Text.Trim();
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
//下面设置登录sql服务器的ip,登录名,登录密码
oSQLServer.Connect(serverip, serverid, serverpwd);
oBackup.Action = 0;
//下面两句是显示进度条的状态
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step2);
oBackup.PercentComplete += pceh;
//数据库名称:
oBackup.Database = "k2";
//备份的路径
oBackup.Files = @backaway;
//备份的文件名
oBackup.BackupSetName = "k2";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
MessageBox.Show("备份成功!", "提示");
}
catch
{
MessageBox.Show("备份失败!", "提示");
}
finally
{
oSQLServer.DisConnect();
}
//数据库恢复
//获取恢复的路径
string dbaway = textbox2.Text.Trim();
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
server.Connect(serverip, serverid, serverpwd);
//KILL DataBase Process
conn = new 工资管理系统.CCUtility.connstring();
conn.DBOpen();
SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='k2'", conn.Connection);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
server.KillProcess(Convert.ToInt32(dr[0].ToString()));
}
dr.Close();
conn.DBClose();
try
{
restore.Action = 0;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
restore.PercentComplete += pceh;
restore.Database = "k2";
restore.Files = @dbaway;
restore.ReplaceDatabase = true;
restore.SQLRestore(server);
MessageBox.Show("数据库恢复成功!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
恢复相关的参数和备份相同,不再解释,自己看一下.
上面两个函数调用到了更改进度条的两个函数:
private void Step2(string message, int percent)
{
progressBar2.Value = percent;
}
private void Step(string message, int percent)
{
progressBar1.Value = percent;
}
setp对应备份,,setp2对应恢复....
放这儿,以后学习用
- C#实现SQL数据库备份与恢复(zzfrom:http://www.cnblogs.com/icwin/articles/1285657.html)
- c#与WMI使用技巧集http://www.cnblogs.com/Sandheart/articles/1568636.html
- C#一个字符串的加密与解密 Copy: http://www.cnblogs.com/wifi/articles/2482350.html
- Oracle数据库备份与恢复之二:SQL*Loader(原文http://www.cnblogs.com/sungod/archive/2011/03/13/1983091.html)
- STL(from:http://www.cnblogs.com/henryzc/articles/298882.html)
- 转自http://www.cnblogs.com/flashelf/articles/522348.html
- http://www.cnblogs.com/caihongjin/articles/1296004.html
- http://www.cnblogs.com/yayagepei/articles/1285091.html
- http://www.cnblogs.com/hanfei8987/articles/1320380.html
- http://www.cnblogs.com/mcodec/articles/2122675.html
- http://www.cnblogs.com/bronte/articles/2269786.html
- http://www.cnblogs.com/BlueTzar/articles/1223313.html
- http://www.cnblogs.com/pengyingh/articles/2356827.html
- http://www.cnblogs.com/dudu/articles/4449.html
- http://www.cnblogs.com/onlys/articles/jQuery.html
- http://www.cnblogs.com/devilmsg/articles/1266336.html
- http://www.cnblogs.com/flashsky/articles/642720.html
- fcntl使用 http://www.cnblogs.com/andtt/articles/2178875.html
- Irrlicht 的 IrrEdit 工作路径(workspack)的设置
- flex使用小技巧
- Ubuntu9.04自动升级到9.10 导致系统启动不了(解决)
- 撒打算到
- oracle服务启动(startup),关闭(shutdown)几种方式,及参数详解
- C#实现SQL数据库备份与恢复(zzfrom:http://www.cnblogs.com/icwin/articles/1285657.html)
- 至理名言
- java计算两个数是否误差在百分之几
- Power Designer 12.5下载破解与汉化
- 在信息化中该如何去选择适当的软件
- Visual C++ 预定义宏
- A potentially dangerous Request.QueryString value was detected from the client 的解决办法
- GEF 学习系列之三:缩放、位置和尺寸对齐
- Symbian 中各种提示、输入对话框的使用