/// <summary>
/// 备份数据库
/// </summary>
public bool BackupDataBase(string strDbName, string strFileName, ProgressBar proBar)
{
SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServer();
try
{
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PSAP.Properties.Settings.PSAPConnectionString"].ConnectionString;
string tmpStr = "Data Source=";
string DataSource = connectionString.Substring(connectionString.IndexOf(tmpStr) + tmpStr.Length);
DataSource = DataSource.Substring(0, DataSource.IndexOf(";"));
tmpStr = "User ID=";
string UserID = connectionString.Substring(connectionString.IndexOf(tmpStr) + tmpStr.Length);
if (UserID.IndexOf(";") == -1)
{
UserID = UserID.Substring(0);
}
else
{
UserID = UserID.Substring(0, UserID.IndexOf(";"));
}
tmpStr = "Password="******";") == -1)
{
Password = Password.Substring(0);
}
else
{
Password = Password.Substring(0, Password.IndexOf(";"));
}
sqlServer.Connect(DataSource, UserID, Password);
SQLDMO.Backup backup = new SQLDMO.Backup();
proBar.Value = 0;
backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
backup.Initialize = true;
backup.PercentComplete += new SQLDMO.BackupSink_PercentCompleteEventHandler(Backup_PercentComplete);
backup.Files = strFileName;
backup.Database = strDbName;
backup.PercentCompleteNotification = 1;
backup.SQLBackup(sqlServer);
proBar.Value = 100;
backup.PercentComplete -= new SQLDMO.BackupSink_PercentCompleteEventHandler(Backup_PercentComplete);
backup = null;
return(true);
}
catch (Exception err)
{
throw (new Exception("备份数据库失败" + err.Message));
}
finally
{
sqlServer.DisConnect();
}
}
/// <summary>
/// DisConnects this.Connection
/// </summary>
public void DisConnect()
{
Connection.DisConnect();
}
private string GetTablesScript(string dtName)
{
try
{
SQLDMO.SQLServer oserver = new SQLDMO.SQLServer();
string connStr = KingTop.Common.SQLHelper.ConnectionStringLocalTransaction;
string[] arrConn = connStr.Split(';');
string server = string.Empty;
string database = string.Empty;
string login = string.Empty;
string password = string.Empty;
for (int i = 0; i < arrConn.Length; i++)
{
string[] itemArr = arrConn[i].Split('=');
if (itemArr[0].ToLower() == "server")
{
server = itemArr[1];
}
else if (itemArr[0].ToLower() == "database")
{
database = itemArr[1];
}
else if (itemArr[0].ToLower() == "uid")
{
login = itemArr[1];
}
else if (itemArr[0].ToLower() == "pwd")
{
password = itemArr[1];
}
}
oserver.Connect(server, login, password);
SQLDMO._Database mydb = oserver.Databases.Item(database, "owner");
SQLDMO._Table mytable = mydb.Tables.Item(dtName, "dbo");
string tableScript = mytable.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, null, null, SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);
tableScript = tableScript.Replace("[nvarchar] (0)", "[nvarchar] (max)");
tableScript = tableScript.Replace("[varchar] (-1)", "[varchar] (max)");
//去掉GO
int lastPosNum = tableScript.LastIndexOf("GO");
if (lastPosNum > 0)
{
tableScript = tableScript.Substring(0, lastPosNum);
}
oserver.DisConnect();
//注释掉不需复制的字段
string[] arrFields = noCopyFields.Split(',');
for (int i = 0; i < arrFields.Length; i++)
{
if (!string.IsNullOrEmpty(arrFields[i]) && tableScript.ToLower().IndexOf("[" + arrFields[i].ToLower() + "]") != -1)
{
tableScript = Regex.Replace(tableScript, "\\[" + arrFields[i] + "\\]", "--[" + arrFields[i] + "]", RegexOptions.IgnoreCase);
}
}
return(tableScript.Replace("'", "''"));
}
catch
{
Response.Write("<div align=center style='padding:20px'>复制失败，原因是sqldmo.dll未注册，注册方法如下:<br><br> 打开开始，在运行中输入 regsvr32 \"C:\\Program Files\\Microsoft SQL Server\\80\\Tools\\Binn\\sqldmo.dll\" 注册sqldmo.dll。<br><br>在注册前请确认sqldmo.dll是否存在，不存在请从网上下载sqldmo.dll到相应目录，再进行注册");
Response.Write("<br><br><a href=# onclick='history.back();'>[返回]</a></div>");
Response.End();
return("");
}
}
/// <summary>
/// 使用SQL DMO恢复数据库
/// </summary>
/// <param name="serverName"></param>
/// <param name="userName"></param>
/// <param name="pwd"></param>
/// <param name="dbName"></param>
/// <param name="dbBakFileName"></param>
public void RestoreSqlDb(string serverName, string userName, string pwd, string dbName, string dbBakFileName)
{
string dbFileDir = string.Empty;
SQLDMO.SQLServer sqlServerObj = new SQLDMO.SQLServer();
SQLDMO.Database dbObj = new SQLDMO.Database();
SQLDMO.DBFile dbFileObj = new SQLDMO.DBFile();
SQLDMO.LogFile logFileObj = new SQLDMO.LogFile();
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sqlServerObj.Connect(serverName, userName, pwd);
dbFileDir = sqlServerObj.Registry.SQLDataRoot + @"\DATA\";
if (!Directory.Exists(dbFileDir))
{
Directory.CreateDirectory(dbFileDir);
}
}
catch (Exception exp)
{
throw new Exception("无法连接到Sql Server，" + exp.Message, exp);
}
#region 属性设置
try
{
dbObj.Name = dbName;
dbFileObj.Name = dbBakFileName;
dbFileObj.PhysicalName = dbFileDir + dbName + ".mdf";
dbFileObj.PrimaryFile = true;
dbFileObj.Size = 3;
dbFileObj.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
dbFileObj.FileGrowth = 1;
dbObj.FileGroups.Item("PRIMARY").DBFiles.Add(dbFileObj);
logFileObj.Name = dbName + "Log";
logFileObj.PhysicalName = dbFileDir + dbName + "_log.ldf";
logFileObj.Size = 3;
dbObj.TransactionLog.LogFiles.Add(logFileObj);
sqlServerObj.Databases.Add(dbObj);
}
catch (Exception exp)
{
throw new Exception("数据库属性设置失败，" + exp.Message, exp);
}
#endregion
#region 数据库恢复操作
try
{
SQLDMO.Restore restoreObj = new SQLDMO.RestoreClass();
restoreObj.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
restoreObj.Database = dbName;
//是否覆盖原数据库
restoreObj.ReplaceDatabase = true;
restoreObj.Files = dbBakFileName;// @"\data\mdernode.bak";
//restoreObj.FileNumber = 1;
restoreObj.UnloadTapeAfter = true;
restoreObj.SQLRestore(sqlServerObj);
}
catch (Exception exp)
{
throw new Exception("数据库恢复失败，" + exp.Message, exp);
}
finally
{
sqlServerObj.DisConnect();
}
#endregion
}