1.第一种方式: using SQLDMO;//Microsoft SQLDMO Object Library 8.0
/// <summary>
/// 数据库的备份
/// 涂聚文注:数据库的备份和实时进度显示代码：(远程备份在数据库原本地,如果在数据库安装的电脑上备份,就可以自行选择文件夹地址,不能备份在客户端的电脑上)
/// 20150205
/// 默认: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup (我装了2000,20005)
/// </summary>
/// <param name="ServerName"></param>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <param name="strDbName"></param>
/// <param name="strFileName"></param>
/// <param name="pgbMain"></param>
/// <returns></returns>
public bool BackUPDB(string ServerName, string UserName, string Password, string strDbName, string strFileName, ProgressBar pgbMain)
{
PBar = pgbMain;
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;// 0;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
bak.PercentComplete += pceh;
bak.BackupSetDescription = "数据库备份";
bak.Files = strFileName;
bak.Database = strDbName;
bak.Initialize = true;
bak.SQLBackup(svr);
return true;
}
catch (Exception err)
{
throw (new Exception("备份数据库失败" + err.Message));
//return false ;
//MessageBox.Show("备份数据库失败"+err.Message);
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 数据库的恢复的代码：
/// </summary>
/// <param name="ServerName"></param>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <param name="strDbName"></param>
/// <param name="strFileName"></param>
/// <param name="pgbMain"></param>
/// <returns></returns>
public bool RestoreDB(string ServerName, string UserName, string Password, string strDbName, string strFileName, ProgressBar pgbMain)
{
PBar = pgbMain;
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
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() == strDbName.ToUpper())
svr.KillProcess(lPID);
}
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; //0;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
res.PercentComplete += pceh;
res.Files = strFileName;
res.Database = strDbName;
res.ReplaceDatabase = true;
res.SQLRestore(svr);
return true;
}
catch (Exception err)
{
throw (new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message));
//return false ;
//MessageBox.Show("恢复数据库失败,请关闭所有和该数据库连接的程序！"+err.Message);
}
finally
{
svr.DisConnect();
}
}
2.第二种方式:
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx
/// <summary>
/// https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx
/// https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx
/// </summary>
public class RestoreHelper
{
/// <summary>
///
/// </summary>
public RestoreHelper()
{
}
/// <summary>
/// 还原数据库
/// 涂聚文
/// </summary>
/// <param name="databaseName"></param>
/// <param name="filePath"></param>
/// <param name="serverName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <param name="dataFilePath"></param>
/// <param name="logFilePath"></param>
public void RestoreDatabase(String databaseName, String filePath, String serverName, String userName, String password, String dataFilePath, String logFilePath)
{
try
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = dataFilePath + databaseName + ".mdf";
String logFileLocation = logFilePath + databaseName + "_Log.ldf";
db = sqlServer.Databases[databaseName];
RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 10;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
}
catch (SqlServerManagementException ex)
{
ex.Message.ToString();
}
}
public event EventHandler<PercentCompleteEventArgs> PercentComplete;
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void sqlRestore_PercentComplete(object sender, PercentCompleteEventArgs e)
{
if (PercentComplete != null)
PercentComplete(sender, e);
}
public event EventHandler<ServerMessageEventArgs> Complete;
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void sqlRestore_Complete(object sender, ServerMessageEventArgs e)
{
if (Complete != null)
Complete(sender, e);
}
}
/// <summary>
///
/// </summary>
public class BackupHelper
{
/// <summary>
///
/// </summary>
public BackupHelper()
{
}
/// <summary>
/// 备份数据库
/// 涂聚文
///
/// </summary>
/// <param name="databaseName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <param name="serverName"></param>
/// <param name="destinationPath"></param>
public bool BackupDatabase(String databaseName, String userName, String password, String serverName, String destinationPath)
{
bool isok = false;
try
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database = databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
isok = true;
}
catch (SqlServerManagementException ex)
{
ex.Message.ToString();
isok = false;
}
return isok;
}
}