private static string FixDbUserLogin(string DBName, string Username = "******", string serverName = "")
{
string strRetVal = string.Empty;
try
{
string strConn = DBRestoreHelper.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 void AddDBAndSetState(bool onOrOff, bool addOptionalDBs = true)
{
this.RestoreDBListBox.Items.Clear();
this.RestoreDBListBox.Items.AddRange(DBRestoreHelper.DatabaseToRestore().ToArray());
for (int intIdx = 0; intIdx < this.RestoreDBListBox.Items.Count; intIdx++)
{
this.RestoreDBListBox.SetItemChecked(intIdx, onOrOff);
}
if (addOptionalDBs)
{
this.RestoreDBListBox.Items.AddRange(DBRestoreHelper.OptionalDBToRestore().ToArray());
}
}
private void SubmitBtn_Click(object sender, EventArgs e)
{
try
{
Cursor.Current = Cursors.WaitCursor;
foreach (object chekedItem in RestoreDBListBox.CheckedItems)
{
List <string> strList = DBRestoreHelper.RestoreCleanupShrinkDB(this.NetworkPathTextBox.Text
, this.DataPathTextBox.Text
, this.LogPathTextBox.Text
, this.BackupPathTxtBox.Text
, chekedItem.ToString()
, this.ServerNameTxtBox.Text);
AddToResults(strList);
if (ConfirmChkBox.Checked)
{
DialogResult dr = MessageBox.Show("Continue with Restore of Remaining Databases?", "Continue?"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Question
, MessageBoxDefaultButton.Button1);
if (dr == DialogResult.Yes)
{
continue;
}
else
{
break;
}
}
}
}
catch (Exception ex)
{
AddToResults(ex.Message);
if (ex.InnerException != null)
{
AddToResults(ex.InnerException.Message);
}
}
finally
{
Cursor.Current = Cursors.Default;
}
}
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 = DBRestoreHelper.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 = DBRestoreHelper.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);
}
