public void RestoreDb(string dbName, string resotrFilePath /*, string dtabase_MDF_Location*/)
{
killOpenProcesses(dbName);
Restore dbRestore = new Restore();
this.Cursor = Cursors.WaitCursor;
dbRestore.Database = dbName;
dbRestore.Action = RestoreActionType.Database;
dbRestore.ReplaceDatabase = true;
try
{
BackupDeviceItem device = new BackupDeviceItem(resotrFilePath, DeviceType.File);
dbRestore.Devices.Add(device);
DataTable dtFiles = dbRestore.ReadFileList(sqlServer);
string backupDbLogicalName = dtFiles.Rows[0]["LogicalName"].ToString();
//RelocateFile dbRf = new RelocateFile(backupDbLogicalName, string.Format("{0}\\{1}.mdf", dtabase_MDF_Location, dbName));
//RelocateFile logRf = new RelocateFile(string.Format("{0}_log", backupDbLogicalName), string.Format("{0}\\{1}_Log.ldf", dtabase_MDF_Location, dbName));
//dbRestore.RelocateFiles.Add(dbRf);
//dbRestore.RelocateFiles.Add(logRf);
string sql = string.Empty;
StringCollection scriptColl = dbRestore.Script(sqlServer);
foreach (string str in scriptColl)
{
sql += str;
}
progBar.Visible = true;
progBar.Value = 0;
dbRestore.Complete += new ServerMessageEventHandler(dbRestore_Complete);
dbRestore.PercentComplete += new PercentCompleteEventHandler(PercentComplete);
dbRestore.SqlRestore(sqlServer);
}
catch /*(Exception ex)*/
{
dbRestore.Abort();
//Log.AddLog(ex.ToString());
}
finally
{
sqlConn.Close();
this.Cursor = Cursors.Default;
}
progBar.Visible = false;
}
/// <summary>
/// Restore Database and Log on target server from backup files.
/// </summary>
/// <param name="fileShare">Location of backup files</param>
/// <param name="targetServer">Destination server</param>
/// <param name="dbName">Name of the database to be restored</param>
/// <param name="noRecovery">Gets or sets a Restore.NoRecovery property value that determines whether the tail of the log is backed up and whether the database is restored into the 'Restoring' state</param>
/// <param name="deleteBackupFiles">If true, backup files will be deleted after restore operation is complete</param>
public static void RestoreDatabase(string fileShare, SMO.Server targetServer, string dbName, bool noRecovery, bool deleteBackupFiles)
{
string backupFilePath;
string dataDirectory = targetServer.InstallDataDirectory;
foreach (BackupActionType backupType in new List <BackupActionType> {
BackupActionType.Database, BackupActionType.Log
})
{
backupFilePath = Path.Combine(fileShare, string.Format(backupFileNameTemplate, dbName, backupType.ToString()));
BackupDeviceItem backupDeviceItem = new BackupDeviceItem(backupFilePath, DeviceType.File);
//restore on the destination
Restore restore = new Restore();
restore.Action = (backupType == BackupActionType.Database) ? RestoreActionType.Database : RestoreActionType.Log;
restore.NoRecovery = (backupType == BackupActionType.Log && noRecovery == false) ? false : true;
restore.Devices.Add(backupDeviceItem);
restore.Database = dbName;
restore.ReplaceDatabase = false;
DataTable logicalFilesDt = restore.ReadFileList(targetServer);
DataRow[] foundLogicalFilesRows = logicalFilesDt.Select();
if (!string.IsNullOrEmpty(dataDirectory))
{
foreach (DataRow row in foundLogicalFilesRows)
{
string logicalFileName = row["LogicalName"].ToString();
string physicalFileName = (logicalFileName.EndsWith("_log", StringComparison.OrdinalIgnoreCase)) ?
Path.Combine(dataDirectory, string.Format(CultureInfo.InvariantCulture, "{0}.ldf", logicalFileName)) :
Path.Combine(dataDirectory, string.Format(CultureInfo.InvariantCulture, "{0}.mdf", logicalFileName));
restore.RelocateFiles.Add(new RelocateFile(logicalFileName, physicalFileName));
}
}
foreach (string script in restore.Script(targetServer))
{
}
restore.SqlRestore(targetServer);
if (deleteBackupFiles)
{
File.Delete(backupFilePath);
}
}
}
private void RestoreButton_Click(object sender, EventArgs e)
{
// Restore the complete database to disk
Cursor csr = null;
Restore restore;
Database db;
BackupDeviceItem backupDeviceItem;
// Are you sure? Default to No.
if (System.Windows.Forms.MessageBox.Show(this,
string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ReallyRestore,
DatabasesComboBox.Text), this.Text, MessageBoxButtons.YesNo,
MessageBoxIcon.Question, MessageBoxDefaultButton.Button2,
0) == DialogResult.No)
{
return;
}
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Get database object from combobox
db = (Database)DatabasesComboBox.SelectedItem;
// Create a new Restore object instance
restore = new Restore();
// Restore database action
restore.Action = RestoreActionType.Database;
// Set database name
restore.Database = db.Name;
// Create a file backup device
backupDeviceItem = new BackupDeviceItem(
BackupFileTextBox.Text, DeviceType.File);
// Add database backup device
restore.Devices.Add(backupDeviceItem);
// Notify this program every 5%
restore.PercentCompleteNotification = 5;
// Replace the existing database
restore.ReplaceDatabase = true;
// Unload the backup device (tape)
restore.UnloadTapeAfter = true;
// add event handler to show progress
restore.PercentComplete += new PercentCompleteEventHandler(
this.ProgressEventHandler);
// generate and print script
ResultsTextBox.AppendText(Properties.Resources.GeneratedScript);
System.Collections.Specialized.StringCollection strColl =
restore.Script(SqlServerSelection);
foreach (string script in strColl)
{
ResultsTextBox.AppendText(script + Environment.NewLine);
}
ResultsTextBox.AppendText(Properties.Resources.Restoring);
UpdateStatus(0);
// Actual restore begins here
restore.SqlRestore(SqlServerSelection);
ResultsTextBox.AppendText(Properties.Resources.RestoreComplete);
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
// Restore the original cursor
this.Cursor = csr;
}
}
private void RestoreDb()
{
string[] dbNameFragments = _backupFileName.Split('_');
string _dbName = dbNameFragments[0].Replace(_scratchPad, "");
Restore dbRestore = new Restore();
dbRestore.Database = _dbName;
dbRestore.Action = RestoreActionType.Database;
dbRestore.ReplaceDatabase = true;
try
{
BackupDeviceItem device = new BackupDeviceItem(_backupFileName, DeviceType.File);
dbRestore.Devices.Add(device);
DataTable dtFiles = dbRestore.ReadFileList(_sqlServer);
string backupDbLogicalName = dtFiles.Rows[0]["LogicalName"].ToString();
RelocateFile dbRf = new RelocateFile(backupDbLogicalName, _databaseFileName);
RelocateFile logRf = new RelocateFile($"{backupDbLogicalName}_log", _databaseLogFileName);
dbRestore.RelocateFiles.Add(dbRf);
dbRestore.RelocateFiles.Add(logRf);
if (!logRf.PhysicalFileName.Contains(@"C:\"))
{
logRf.PhysicalFileName = _DbFileLocation + _databaseLogFileName;
}
Logger.LogMessage("Physical Log File: " + logRf.PhysicalFileName);
Logger.LogMessage("Physical DB File: " + dbRf.PhysicalFileName);
string sql = string.Empty;
StringCollection scriptColl = dbRestore.Script(_sqlServer);
foreach (string str in scriptColl)
{
sql += str;
}
sql = "USE master ALTER DATABASE " + _dbName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE " + sql;
sql += " ALTER DATABASE " + _dbName + " SET MULTI_USER ";
txtRestoreScript.Text = sql;
Logger.LogMessage("SQL Restore Script: " + sql);
progBar.Visible = true;
progBar.Value = 0;
dbRestore.Complete += new ServerMessageEventHandler(dbRestore_Complete);
dbRestore.PercentComplete += new PercentCompleteEventHandler(PercentComplete);
dbRestore.SqlRestore(_sqlServer);
}
catch (Exception exc)
{
dbRestore.Abort();
Logger.LogMessage($"RestoreDb(): Exception occured.\nMessage: {exc.Message}");
MessageBox.Show("RestoreDb(): Exception occured.\nMessage:" + exc.Message, _messageBoxCaption);
;
}
finally
{
if (File.Exists(_backupFileName))
{
File.Delete(_backupFileName);
}
}
progBar.Visible = false;
}
private void RestoreButton_Click(object sender, EventArgs e)
{
// Restore the complete database to disk
Cursor csr = null;
Restore restore;
Database db;
BackupDeviceItem backupDeviceItem;
// Are you sure? Default to No.
if (System.Windows.Forms.MessageBox.Show(this,
string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ReallyRestore,
DatabasesComboBox.Text), this.Text, MessageBoxButtons.YesNo,
MessageBoxIcon.Question, MessageBoxDefaultButton.Button2,
0) == DialogResult.No)
{
return;
}
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Get database object from combobox
db = (Database)DatabasesComboBox.SelectedItem;
// Create a new Restore object instance
restore = new Restore();
// Restore database action
restore.Action = RestoreActionType.Database;
// Set database name
restore.Database = db.Name;
// Create a file backup device
backupDeviceItem = new BackupDeviceItem(
BackupFileTextBox.Text, DeviceType.File);
// Add database backup device
restore.Devices.Add(backupDeviceItem);
// Notify this program every 5%
restore.PercentCompleteNotification = 5;
// Replace the existing database
restore.ReplaceDatabase = true;
// Unload the backup device (tape)
restore.UnloadTapeAfter = true;
// add event handler to show progress
restore.PercentComplete += new PercentCompleteEventHandler(
this.ProgressEventHandler);
// generate and print script
ResultsTextBox.AppendText(Properties.Resources.GeneratedScript);
System.Collections.Specialized.StringCollection strColl =
restore.Script(SqlServerSelection);
foreach (string script in strColl)
{
ResultsTextBox.AppendText(script + Environment.NewLine);
}
ResultsTextBox.AppendText(Properties.Resources.Restoring);
UpdateStatus(0);
// Actual restore begins here
restore.SqlRestore(SqlServerSelection);
ResultsTextBox.AppendText(Properties.Resources.RestoreComplete);
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
// Restore the original cursor
this.Cursor = csr;
}
}
