1.第一種方式: using SQLDMO;//Microsoft SQLDMO Object Library 8.0
/// <summary>
/// 數據庫的備份
/// 塗聚文注:數據庫的備份和實時進度顯示代碼：(遠程備份在數據庫原本地,如果在數據庫安裝的電腦上備份,就可以自行選擇文件夾地址,不能備份在客戶端的電腦上)
/// 20150205
/// 默認: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup (我裝了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;
}
}
