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 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);
}
}
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);
}