private static string FixDbUserLogin(string DBName, string Username = "******", string serverName = "")
{
string strRetVal = string.Empty;
try
{
string strConn = TODELETEClass.DBAToolConnectionString(serverName);
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("dbo.usp_ReSyncDBUser_Login", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@DBName", DBName));
cmd.Parameters.Add(new SqlParameter("@UserName", Username));
// execute the command
cmd.ExecuteNonQuery();
}
strRetVal = "Successfully fixed user:"******" in DB:" + DBName;
}
catch (Exception ex)
{
strRetVal = "Error fixing user:"******" in DB:" + DBName;
strRetVal = strRetVal + System.Environment.NewLine + ex.Message;
}
return(strRetVal);
}
private static string RestoreBackup(string sqlBackupfile, string dataPath, string logPath, string DBName, string serverName = "")
{
string strRetVal = string.Empty;
FileInfo fi = new FileInfo(sqlBackupfile);
string sqlBackuplocation = fi.DirectoryName;
try
{
string strConn = TODELETEClass.DBAToolConnectionString(serverName);
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("dbo.sp_RestoreFromAllFilesInDirectory", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@SourceDirBackupFiles", sqlBackuplocation));
cmd.Parameters.Add(new SqlParameter("@DestDirDbFiles", dataPath));
cmd.Parameters.Add(new SqlParameter("@DestDirLogFiles", logPath));
cmd.Parameters.Add(new SqlParameter("@OnlyPrintCommands", false));
// execute the command
cmd.ExecuteNonQuery();
}
strRetVal = "Successfully Restored backup :" + sqlBackupfile;
}
catch (Exception ex)
{
strRetVal = "Error in restoring Backup for DB:" + sqlBackupfile;
strRetVal = strRetVal + System.Environment.NewLine + ex.Message;
throw ex;
}
return(strRetVal);
}
private static string CleanupTables(string DBName, string serverName = "")
{
string strRetVal = string.Empty;
try
{
string strConn = TODELETEClass.DBAToolConnectionString(serverName);
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("dbo.usp_TruncateTableDBTest2_PostRestore", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@DBToTruncate", DBName));
cmd.Parameters.Add(new SqlParameter("@Debug", false));
// execute the command
cmd.ExecuteNonQuery();
}
strRetVal = "Successfully cleanedup tables for DB:" + DBName;
}
catch (Exception ex)
{
strRetVal = "Error in clearing tables from network for DB:" + DBName;
strRetVal = strRetVal + System.Environment.NewLine + ex.Message;
}
return(strRetVal);
}