private static void OffAndOnLine(string DBName)
{
string sql = "use master;alter database [" + DBName + "] set offline with ROLLBACK IMMEDIATE;alter database " + DBName + " set online with ROLLBACK IMMEDIATE";
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
SqlHelper.ExecuteSql(sql);
}
public static DataTable GetAllBackupHistory()
{
try
{
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
string sql = "select * from Record order by ID desc";
DataTable dt = SqlHelper.Query(sql, true);
return(dt);
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "GetAllBackupHistory", "error", "获取备份的历史记录失败：" + e.Message);
return(null);
}
}
public static bool ShrinkDB()
{
try
{
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(TopFashion.Configs.ConnString);
string sql = "DBCC SHRINKDATABASE('" + scsb.InitialCatalog + "')";
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
SqlHelper.ExecuteSql(sql);
return(true);
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "ShrinkDB", "error", "收缩数据库失败：" + e.Message);
return(false);
}
}
public static string GetBackupPathById(int id)
{
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
object obj = SqlHelper.GetSingle("select Path from Record where ID=" + id, true);
if (obj != null && obj != DBNull.Value)
{
string filename = obj.ToString();
FileInfo fi = new FileInfo(filename);
if (fi.Directory != null)
{
return(fi.Directory.Parent.FullName);
}
}
return("");
}
private static void KillProcess(string DbName)
{
string sql = "select spid from sys.sysprocesses where dbid in (select dbid from master..sysdatabases where name = '" + DbName + "')";
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
DataTable dt = SqlHelper.Query(sql);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
SqlHelper.ExecuteSql("kill " + dt.Rows[i][0].ToString());
}
catch { }//避免KILL自身进程造成错误！
}
}
}
public static bool DeleteDB(int year, int month, int year2, int month2, List <string> tables)
{
int count = 0;
DateTime start = new DateTime(year, month, 1);
DateTime end = new DateTime(year2, month2, 1);
if (end >= start)
{
int months = 0;
if (end.Year > start.Year)//2013.10-2014.8
{
months += 12 - start.Month + 1;
months += 12 * (end.Year - start.Year - 1);
months += end.Month;
}
else//2013.8-2013.10
{
months = end.Month - start.Month + 1;
}
for (int i = 0; i < months; i++)
{
DateTime current = start.AddMonths(i);
string YYYYMM = current.Year.ToString().PadLeft(4, '0') + current.Month.ToString().PadLeft(2, '0');
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
foreach (string table in tables)
{
string sql = "DROP TABLE " + table + YYYYMM;
try
{
SqlHelper.ExecuteSql(sql);
count++;
}
catch { }//不存在的表出错时略过...
}
}
}
return(count > 0);
}
/// <summary>
/// SQL数据库备份
/// </summary>
/// <param name="path">备份到的路径< /param>
/// <param name="Backup_PercentComplete">进度</param>
/// <param name="oBackup">数据库备份服务对象</param>
/// <param name="remark">备份备注</param>
public static bool SQLDbBackup(string path, SQLDMO.BackupSink_PercentCompleteEventHandler Backup_PercentComplete, out SQLDMO.Backup oBackup, string remark = null)
{
SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(TopFashion.Configs.ConnString);
string ServerIP = connStrBuilder.DataSource;
string LoginUserName = connStrBuilder.UserID;
string LoginPass = connStrBuilder.Password;
string DBName = connStrBuilder.InitialCatalog;
string dir = path + "\\" + DBName;
dir = dir.Replace("\\\\", "\\");
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string locale = string.Empty;
string username = string.Empty;
string password = string.Empty;
string authority = string.Empty;
string RemoteAuth = TopFashion.Configs.RemoteAuth;
if (!string.IsNullOrEmpty(RemoteAuth))
{
string[] ra = RemoteAuth.Split("|".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
if (ra.Length == 4)
{
locale = ra[0];
username = ra[1];
password = ra[2];
authority = ra[3];
}
}
System.Management.ManagementScope scope = new System.Management.ManagementScope("\\\\" + ServerIP + "\\root\\cimv2", new System.Management.ConnectionOptions(locale, username, password, authority, System.Management.ImpersonationLevel.Impersonate, System.Management.AuthenticationLevel.Default, true, null, TimeSpan.MaxValue));
if (ServerIP == "." || ServerIP == "(local)" || ServerIP == "127.0.0.1")
{
}
else
{
int i = WmiShareRemote.CreateRemoteDirectory(scope, Path.GetDirectoryName(dir), Directory.GetParent(dir).FullName);
}
string DBFile = DBName + DateTime.Now.ToString("yyyyMMddHHmm");
string filename = dir + "\\" + DBFile + ".bak";
if (!File.Exists(filename))
{
try
{
FileStream fs = File.Create(filename);
fs.Close();
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbBackup", "error", "无法创建 [" + filename + "] 数据库备份文件！" + Environment.NewLine + e.Message);
}
}
if (ServerIP == "." || ServerIP == "(local)" || ServerIP == "127.0.0.1")
{
}
else
{
bool flag = WmiShareRemote.WmiFileCopyToRemote(ServerIP, username, password, dir, "DatabaseBackup", "数据库备份集", null, new string[] { filename }, 0);
}
oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.PercentComplete += Backup_PercentComplete;
oBackup.Database = DBName;
oBackup.Files = @"" + string.Format("[{0}]", filename) + "";
oBackup.BackupSetName = DBName;
oBackup.BackupSetDescription = "备份集" + DBName;
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);//这里可能存在问题：比如备份远程数据库的时候，选的路径path却是本地的，恰好是远程服务器上不存在的目录
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
SqlHelper.ExecuteSql("insert into Record (DB, Path, Remark) values ('" + DBName + "', '" + filename + "', '" + remark + "')", true);
return(true);
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbBackup", "error", "备份时出错：" + e.Message);
}
finally
{
oSQLServer.DisConnect();
}
return(false);
}
/// < summary>
/// SQL恢复数据库
/// < /summary>
/// <param name="id">备份集ID</param>
/// <param name="Restore_PercentComplete">进度</param>
/// <param name="oRestore">数据库还原服务对象</param>
public static bool SQLDbRestore(int id, SQLDMO.RestoreSink_PercentCompleteEventHandler Restore_PercentComplete, out SQLDMO.Restore oRestore)
{
oRestore = null;
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
object obj = SqlHelper.GetSingle("select Path from Record where ID=" + id, true);
if (obj != null && obj != DBNull.Value)
{
string filename = obj.ToString();
SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(TopFashion.Configs.ConnString);
string ServerIP = connStrBuilder.DataSource;
if (ServerIP == "." || ServerIP == "(local)" || ServerIP == "127.0.0.1")
{
}
else
{
StringBuilder TargetDir = new StringBuilder();
TargetDir.Append(@"\\");
TargetDir.Append(ServerIP);
TargetDir.Append("\\");
TargetDir.Append("DatabaseBackup");
TargetDir.Append("\\");
filename = TargetDir + Path.GetFileName(filename);
}
if (File.Exists(filename))
{
string LoginUserName = connStrBuilder.UserID;
string LoginPass = connStrBuilder.Password;
string DBName = connStrBuilder.InitialCatalog;
oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
//因为数据库正在使用，所以无法获得对数据库的独占访问权。不一定是由于其他进程的占用，还有其他的原因，所以要脱机再联机...
//KillProcess(DBName);
//KillSqlProcess(oSQLServer, DBName);
//OffAndOnLine(DBName);
OffLine(DBName);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.PercentComplete += Restore_PercentComplete;
oRestore.Database = DBName;
oRestore.Files = @"" + string.Format("[{0}]", filename) + "";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);//这里可能存在问题！
OnLine(DBName);
return(true);
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbRestore", "error", "恢复时出错：" + e.Message);
}
finally
{
oSQLServer.DisConnect();
}
}
else
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbRestore", "error", "找不到要还原的备份数据库文件 [" + filename + "]");
}
}
return(false);
}
