C#实现SQL SERVER数据库备份的两种方法比较

最新推荐文章于 2022-07-08 08:49:35 发布

dezhen

最新推荐文章于 2022-07-08 08:49:35 发布

阅读量1.6k

点赞数

分类专栏： SQL Server数据库

SQL Server数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
namespace DBAdmin
{
public class DbBackUpAndRestore
{
/// <summary>
/// 服务器
/// </summary>
private string server = "";
public string Server
{
get { return this.server; }
set { this.server = value; }
}
/// <summary>
/// 登录名
/// </summary>
private string uid = "";
public string UID
{
get { return this.uid; }
set { this.uid = value; }
}
/// <summary>
/// 登录密码
/// </summary>
private string pwd = "";
public string PWD
{
get { return this.pwd; }
set { this.pwd = value; }
}
/// <summary>
/// 要操作的数据库
/// </summary>
private string database = "";
public string Database
{
get { return this.database; }
set { this.database = value; }
}
/// <summary>
/// 数据库连接字符串
/// </summary>
private string conn = "";
/// <summary>
/// 备份路经
/// </summary>
private string backPath = "";
public string BackPath
{
get { return this.backPath; }
set { this.backPath = value; }
}
/// <summary>
/// 还原文件路经
/// </summary>
private string restoreFile = "";
public string RestoreFile
{
get { return this.restoreFile; }
set { this.restoreFile = value; }
}
private ProgressBar bar;
public ProgressBar Bar
{
get { return bar; }
set { bar = value; }
}
/// <summary>
/// DbBackUpAndRestore类的构造函数
/// </summary>
public DbBackUpAndRestore()
{
}
/// <summary>
/// 切割字符串
/// </summary>
/// <param name="str"></param>
/// <param name="bg"></param>
/// <param name="ed"></param>
/// <returns></returns>
public string StringCut(string str, string bg, string ed)
{
string sub;
sub = str.Substring(str.IndexOf(bg) + bg.Length);
sub = sub.Substring(0, sub.IndexOf(";"));
return sub;
}
/// <summary>
/// 构造文件名
/// </summary>
/// <returns>文件名</returns>
private void CreatePath()
{
string CurrTime = System.DateTime.Now.ToString();
CurrTime = CurrTime.Replace("-", "");
CurrTime = CurrTime.Replace(":", "");
CurrTime = CurrTime.Replace(" ", "");
CurrTime = CurrTime.Substring(0, 12);
backPath += "//_db_" + CurrTime + ".BAK";
}
private void Step(string message, int percent)
{
Bar.Value = percent;
}
/// <summary>
/// 数据库备份
/// </summary>
/// <returns>备份是否成功</returns>
public bool DbBackup()
{
CreatePath();
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
oBackup.PercentComplete += pceh;
oBackup.Database = database;
oBackup.Files = backPath;
oBackup.BackupSetName = database;
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return true;
}
catch (Exception ex)
{
return false;
throw ex;
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// 数据库恢复
/// </summary>
public string DbRestore()
{
if (exepro() != true)
{
return "操作失败";
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
exepro();
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
oRestore.PercentComplete += pceh;
oRestore.Database = database;
///自行修改
oRestore.Files = restoreFile;
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return "数据库恢复成功";
}
catch (Exception e)
{
return "恢复数据库失败，原因:" + e.Message;
throw e;
}
finally
{
oSQLServer.DisConnect();
}
}
}
/// <summary>
/// 杀死当前库的所有进程
/// </summary>
/// <returns></returns>
private bool exepro()
{
bool success = true;
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(server, uid, pwd);
//取得所有的进程列表
SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
int iColPIDNum = -1;
int iColDbName = -1;
//找到和要恢复数据库相关的进程
for (int i = 1; i <= qr.Columns; i++)
{
string strName = qr.get_ColumnName(i);
if (strName.ToUpper().Trim() == "SPID")
{
iColPIDNum = i;
}
else if (strName.ToUpper().Trim() == "DBNAME")
{
iColDbName = i;
}
if (iColPIDNum != -1 && iColDbName != -1)
break;
}
//将相关进程关闭
for (int i = 1; i <= qr.Rows; i++)
{
int lPID = qr.GetColumnLong(i, iColPIDNum);
string strDBName = qr.GetColumnString(i, iColDbName);
if (strDBName.ToUpper() == database)
svr.KillProcess(lPID);
}
}
catch (Exception ex)
{
success = false;
}
return success;
}
public bool Operate(bool isBackup)
{
//备份：use master;backup database @name to disk=@path;
//恢复：use master;restore database @name from disk=@path;
SqlConnection connection = new SqlConnection("Data Source=" + server + ";initial catalog=" + database + ";user id=" + uid + ";password=" + pwd + ";");
if (!restoreFile.EndsWith(".bak"))
{
restoreFile += ".bak";
}
if (isBackup)//备份数据库
{
SqlCommand command = new SqlCommand("use master;backup database @name to disk=@path;", connection);
connection.Open();
command.Parameters.AddWithValue("@name", Database);
command.Parameters.AddWithValue("@path", restoreFile);
command.ExecuteNonQuery();
connection.Close();
}
else//恢复数据库
{
SqlCommand command = new SqlCommand("use master;restore database @name from disk=@path;", connection);
connection.Open();
command.Parameters.AddWithValue("@name", Database);
command.Parameters.AddWithValue("@path", restoreFile);
command.ExecuteNonQuery();
connection.Close();
}
return true;
}
}
}

第一种方法是利用库SQLDMO来进行数据库的备份和恢复。这种方法在恢复时，原数据不能正在使用当中，可以先把原数据库删除，适用于数据库误删除或者sql server数据库软件重装的情况下进行.当然这种情况下，发布时，必须把库SQLDMO打包。

第二种方式是通过调用sql指令来完成数据库的备份和还原。这种方法在恢复时，原数据必须存在，恢复时覆盖掉原数据库的数据。

备份的情况当然是数据库必须存在而且已经附加到数据库管理器中。

可以根据需要采用这两种方式

dezhen

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
C#实现SQL SERVER数据库备份的两种方法比较

using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; using System.Windows.Forms; namespace DBAdmin { public cl
复制链接

扫一扫