public SqlServerBackup(string backupPath,Server server)
{
Restore restore=new Restore();
restore.Devices.AddDevice(backupPath,DeviceType.File);
DataTable dt= restore.ReadBackupHeader(server);
if(dt.Rows.Count==1)
{
/*
DataRow row = dt.Rows[0];
BackupName = row[""].ToString();
BackupDescription = row[""].ToString();
Compressed = row[""].ToString();
BackupTypeDescription = row[""].ToString();
DifferentialBaseLSN = row[""].ToString();
DifferentialBaseGUID = row[""].ToString();
BackupSetGUID = row[""].ToString();
ServerName = row[""].ToString();
DatabaseName = row[""].ToString();
DatabaseVersion = row[""].ToString();
BackupStartDate = row[""].ToString();
BackupFinishDate = row[""].ToString();
MachineName = row[""].ToString();
CompatibilityLevel = row[""].ToString();
DatabaseVersion = row[""].ToString();
DatabaseVersion = row[""].ToString();
DatabaseVersion = row[""].ToString();
*/
}
}
public static void MakeRestore(ServerMessageEventHandler onComplete, Guid id,
string serverName, string dbName, string uncDir, string userName, string password)
{
var sc = new ServerConnection(serverName);
sc.LoginSecure = false;
sc.Login = userName;
sc.Password = password;
var srv = new Server(sc);
var rest = new Restore();
rest.Action = RestoreActionType.Database;
rest.Database = dbName;
rest.Devices.AddDevice(Path.Combine(uncDir, rest.Database + ".bak"), DeviceType.File);
rest.ReplaceDatabase = true;
var headers = rest.ReadBackupHeader(srv);
var query = from DataRow row in headers.Rows
where (string)row["BackupName"] == id.ToString()
select(Int16) row["Position"];
rest.FileNumber = query.First();
if (onComplete != null)
{
rest.Complete += onComplete;
}
rest.SqlRestore(srv);
}
private void RestoreDatabaseLog(Server myServer)
{
Restore restoreDBLog = new Restore();
restoreDBLog.Action = RestoreActionType.Log;
restoreDBLog.Devices.AddDevice($"{bteFile.Text}", DeviceType.File);
DataTable dataTable = restoreDBLog.ReadBackupHeader(myServer);
if (dataTable.Rows.Count > 0)
{
restoreDBLog.Database = dataTable.Rows[0]["DatabaseName"].ToString();
}
/* You can specify NoRecovery = false (default) so that transactions are
* rolled forward and recovered. */
restoreDBLog.NoRecovery = true;
/* Wiring up events for progress monitoring */
restoreDBLog.PercentComplete += PercentComplete;
restoreDBLog.Complete += (sender, e) => Completed(sender, e, "Restore");
/* SqlRestore method starts to restore database
* You cab also use SqlRestoreAsync method to perform restore
* operation asynchronously */
restoreDBLog.SqlRestoreAsync(myServer);
}
public static void MakeRestore(ServerMessageEventHandler onComplete, Guid id,
string serverName, string dbName, string uncDir, string userName, string password)
{
var sc = new ServerConnection(serverName);
sc.LoginSecure = false;
sc.Login = userName;
sc.Password = password;
var srv = new Server(sc);
var rest = new Restore();
rest.Action = RestoreActionType.Database;
rest.Database = dbName;
rest.Devices.AddDevice(Path.Combine(uncDir, rest.Database + ".bak"), DeviceType.File);
rest.ReplaceDatabase = true;
var headers = rest.ReadBackupHeader(srv);
var query = from DataRow row in headers.Rows
where (string)row["BackupName"] == id.ToString()
select (Int16)row["Position"];
rest.FileNumber = query.First();
if (onComplete != null) rest.Complete += onComplete;
rest.SqlRestore(srv);
}
public List <BackupItem> GetBackupItems(FileInfo file)
{
Check.DoRequireArgumentNotNull(file, "file");
var restore = new Restore();
restore.Devices.AddDevice(file.FullName, DeviceType.File);
var headerTable = restore.ReadBackupHeader(Server);
var result = new List <BackupItem>(headerTable.Rows.Count);
foreach (DataRow row in headerTable.Rows)
{
result.Add(GetBackupItem(row, file));
}
return(result);
}
public void RestoreDatabase(string fileName)
{
CreateDatabase();
var restore = new Restore
{
Database = _DatabaseName,
Action = RestoreActionType.Database,
NoRecovery = false,
ReplaceDatabase = true
};
var path = Path.Combine(Directory.GetCurrentDirectory(), fileName);
var device = new BackupDeviceItem(path, DeviceType.File);
restore.Devices.Add(device);
var backupHeader = restore.ReadBackupHeader(_Server);
var originalDatabaseName = backupHeader.Rows[0]["DatabaseName"].ToString();
if (originalDatabaseName != _DatabaseName)
{
var dataFile = new RelocateFile
{
PhysicalFileName = Path.Combine(_Server.DefaultFile, _DatabaseName + ".mdb"),
LogicalFileName = originalDatabaseName
};
Console.WriteLine("Relocating {0} to {1}", dataFile.LogicalFileName, dataFile.PhysicalFileName);
restore.RelocateFiles.Add(dataFile);
var logFile = new RelocateFile
{
PhysicalFileName = Path.Combine(_Server.DefaultLog, _DatabaseName + "_log.ldb"),
LogicalFileName = originalDatabaseName + "_log"
};
Console.WriteLine("Relocating {0} to {1}", logFile.LogicalFileName, logFile.PhysicalFileName);
restore.RelocateFiles.Add(logFile);
}
_Server.KillAllProcesses(_DatabaseName);
restore.PercentCompleteNotification = 10;
restore.PercentComplete += (sender, e) => Console.WriteLine(e.Percent + "%");
restore.SqlRestore(_Server);
}
private void RestoreDatabaseWithDifferentNameAndLocation(Server myServer)
{
Restore restoreDB = new Restore();
/* Specify whether you want to restore database or files or log etc */
restoreDB.Action = RestoreActionType.Database;
restoreDB.Devices.AddDevice($"{bteFile.Text}", DeviceType.File);
DataTable dataTable = restoreDB.ReadBackupHeader(myServer);
if (dataTable.Rows.Count > 0)
{
restoreDB.Database = dataTable.Rows[0]["DatabaseName"].ToString() + "New";
}
/* You can specify ReplaceDatabase = false (default) to not create a new image
* of the database, the specified database must exist on SQL Server instance.
* You can specify ReplaceDatabase = true to create new database image
* regardless of the existence of specified database with same name */
restoreDB.ReplaceDatabase = true;
/* If you have differential or log restore to be followed, you would need
* to specify NoRecovery = true, this will ensure no recovery is done after the
* restore and subsequent restores are allowed. It means it will database
* in the Restoring state. */
restoreDB.NoRecovery = false;
/* RelocateFiles collection allows you to specify the logical file names and
* physical file names (new locations) if you want to restore to a different location.*/
restoreDB.RelocateFiles.Add(new RelocateFile($"{ lokDB.Text}_Data", $@"{btePath.Text}\{ lokDB.Text}New_Data.mdf"));
restoreDB.RelocateFiles.Add(new RelocateFile($"{ lokDB.Text}_Log", $@"{btePath.Text}\{ lokDB.Text}New_Log.ldf"));
/* Wiring up events for progress monitoring */
restoreDB.PercentComplete += PercentComplete;
restoreDB.Complete += (sender, e) => Completed(sender, e, "Restore");
/* SqlRestore method starts to restore database
* You cab also use SqlRestoreAsync method to perform restore
* operation asynchronously */
restoreDB.SqlRestoreAsync(myServer);
}
private void RestoreDatabaseWithDifferentNameAndLocation(Server myServer)
{
Restore restoreDB = new Restore();
restoreDB.Action = RestoreActionType.Database;
restoreDB.Devices.AddDevice($"{bteFile.Text}", DeviceType.File);
DataTable dataTable = restoreDB.ReadBackupHeader(myServer);
if (dataTable.Rows.Count > 0)
{
restoreDB.Database = dataTable.Rows[0]["DatabaseName"].ToString() + "New";
}
restoreDB.ReplaceDatabase = true;
restoreDB.NoRecovery = false;
restoreDB.RelocateFiles.Add(new RelocateFile($"{ lokDatabase.Text}_Data", $@"{btePath.Text}\{ lokDatabase.Text}New_Data.mdf"));
restoreDB.RelocateFiles.Add(new RelocateFile($"{ lokDatabase.Text}_Log", $@"{btePath.Text}\{ lokDatabase.Text}New_Log.ldf"));
restoreDB.PercentComplete += PercentComplete;
restoreDB.Complete += (sender, e) => Completed(sender, e, "Restore");
restoreDB.SqlRestoreAsync(myServer);
restoreDB.Wait();
}
/// <summary>
/// Reads backup file header to get source database names
/// If valid credential name is not provided for URL throws exception while executing T-sql statement
/// </summary>
/// <param name="bkdevList">List of backup device items</param>
/// <param name="credential">Optional Sqlserver credential name to read backup header from URL</param>
public List <String> GetSourceDbNames(ICollection <BackupDeviceItem> bkdevList, string credential = null)
{
List <string> databaseNames = new List <string>();
foreach (BackupDeviceItem bkdev in bkdevList)
{
// use the Restore public API to do the Restore Headeronly query
Restore res = new Restore();
res.CredentialName = credential;
res.Devices.Add(bkdev);
DataTable dt = res.ReadBackupHeader(this.server);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
if (dr != null && !(dr["DatabaseName"] is DBNull))
{
string dbName = (string)dr["DatabaseName"];
bool found = false;
foreach (string str in databaseNames)
{
if (StringComparer.OrdinalIgnoreCase.Compare(str, dbName) == 0)
{
found = true;
break;
}
}
if (found == false)
{
databaseNames.Add(dbName);
}
}
}
}
}
return(databaseNames);
}
private void RestoreDatabaseDifferential(Server myServer)
{
Restore restoreDB = new Restore();
restoreDB.Action = RestoreActionType.Database;
restoreDB.Devices.AddDevice($"{bteFile.Text}", DeviceType.File);
DataTable dataTable = restoreDB.ReadBackupHeader(myServer);
if (dataTable.Rows.Count > 0)
{
restoreDB.Database = dataTable.Rows[0]["DatabaseName"].ToString();
}
Database db = myServer.Databases[restoreDB.Database];
if (db != null)
{
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
db.Refresh();
}
restoreDB.ReplaceDatabase = true;
restoreDB.NoRecovery = true;
restoreDB.PercentComplete += PercentComplete;
restoreDB.Complete += (sender, e) => Completed(sender, e, "Restore");
restoreDB.SqlRestoreAsync(myServer);
restoreDB.Wait();
if (db != null)
{
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
db.Refresh();
}
}
/// <summary>
/// Used to restore databases older than 6.0
/// </summary>
/// <param name="fileContent"></param>
/// <param name="serverAppFolder"></param>
/// <returns></returns>
//public static string RestoreDatabaseOld(string fileContent, string serverAppFolder)
//{
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Start restore"));
// string fileName = serverAppFolder + "\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".bak.gz";
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restore temp file: " + fileName));
// File.WriteAllBytes(fileName, Convert.FromBase64String(fileContent));
// // Get the stream of the source file.
// FileInfo fi = new FileInfo(fileName);
// using (FileStream inFile = fi.OpenRead())
// {
// // Get original file extension, for example "doc" from report.doc.gz.
// string curFile = fi.FullName;
// string origName = curFile.Remove(curFile.Length - fi.Extension.Length);
// //Create the decompressed file.
// using (FileStream outFile = File.Create(origName))
// {
// using (GZipStream decompress = new GZipStream(inFile,
// CompressionMode.Decompress))
// {
// //Copy the decompression stream into the output file.
// byte[] buffer = new byte[4096];
// int numRead;
// while ((numRead = decompress.Read(buffer, 0, buffer.Length)) != 0)
// {
// outFile.Write(buffer, 0, numRead);
// }
// }
// }
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Decompress finished."));
// Restore sqlRestore = new Restore();
// BackupDeviceItem deviceItem = new BackupDeviceItem(origName, DeviceType.File);
// SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
// conn.Open();
// string databaseName = conn.Database;
// ServerConnection connection = new ServerConnection(conn);
// Server sqlServer = new Server(connection);
// sqlRestore.Devices.Add(deviceItem);
// sqlRestore.Database = conn.Database;
// DataTable dt = sqlRestore.ReadBackupHeader(sqlServer);
// //Check db is for the same app version
// //Check db is for the same app version
// if (dt.Rows[0]["BackupDescription"] == DBNull.Value ||
// ((string)dt.Rows[0]["BackupDescription"] != AppVersion.Version.ToString(2) &&
// //this awful hardcoded line is to allow backups from Version 5.5.6 in version 6
// !((string)dt.Rows[0]["BackupDescription"] == "5.5" && AppVersion.Version.Major == 6)))
// {
// //invalid db
// sqlRestore.Abort();
// string alter2 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
// SqlCommand alter2Cmd = new SqlCommand(alter2, conn);
// alter2Cmd.ExecuteNonQuery();
// return string.Format("Backups from {0} version are not supported.", dt.Rows[0]["BackupDescription"] == System.DBNull.Value ? "an older" : (string)dt.Rows[0]["BackupDescription"]);
// }
// const string UseMaster = "USE master";
// SqlCommand useMasterCommand = new SqlCommand(UseMaster, conn);
// useMasterCommand.ExecuteNonQuery();
// string alter1 = @"ALTER DATABASE [" + databaseName + "] SET Single_User WITH Rollback Immediate";
// SqlCommand alter1Cmd = new SqlCommand(alter1, conn);
// alter1Cmd.ExecuteNonQuery();
// Database db = sqlServer.Databases[databaseName];
// sqlRestore.Action = RestoreActionType.Database;
// String dataFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + ".mdf";
// String logFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + "_Log.ldf";
// sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA", dataFileLocation));
// sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA" + "_log", logFileLocation));
// sqlRestore.ReplaceDatabase = true;
// sqlRestore.PercentCompleteNotification = 10;
// try
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restoring"));
// sqlRestore.SqlRestore(sqlServer);
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Finished restoring"));
// }
// catch (Exception ex)
// {
// return ex.Message;
// }
// finally
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing SET Multi_User"));
// string alter2 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
// SqlCommand alter2Cmd = new SqlCommand(alter2, conn);
// alter2Cmd.ExecuteNonQuery();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "SET Multi_User executed"));
// }
// db = sqlServer.Databases[databaseName];
// db.SetOnline();
// sqlServer.Refresh();
// try
// {
// if (conn.State != ConnectionState.Closed)
// {
// conn.Close();
// }
// }
// catch (Exception)
// {
// }
// try
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing test SQL"));
// conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
// const string SQLQueryTest = @"SELECT * FROM FACTOR";
// SqlCommand sqlQueryTestCmd = new SqlCommand(SQLQueryTest, conn);
// conn.Open();
// sqlQueryTestCmd.ExecuteNonQuery();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Test SQL executed."));
// const string SQLAutoCloseOff = @"ALTER DATABASE idea SET AUTO_CLOSE OFF";
// sqlQueryTestCmd = new SqlCommand(SQLAutoCloseOff, conn);
// sqlQueryTestCmd.ExecuteNonQuery();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Auto_Close OFF executed."));
// }
// catch (Exception ex)
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Failed executing test SQL"));
// }
// finally
// {
// if (conn.State != ConnectionState.Closed)
// {
// conn.Close();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Connection closed"));
// }
// }
// return string.Empty;
// }
//}
/// <summary>
/// Restore the Data Base of Idea
/// </summary>
/// <param name="databaseBackupFileName"></param>
/// <returns></returns>
private static string RestoreDatabase(string databaseBackupFileName)
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Start restore"));
Restore sqlRestore = new Restore();
// Restore - SqlDataBase;
BackupDeviceItem deviceItem = new BackupDeviceItem(databaseBackupFileName, DeviceType.File);
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
conn.Open();
string databaseName = conn.Database;
ServerConnection connection = new ServerConnection(conn);
Server sqlServer = new Server(connection);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = conn.Database;
DataTable dt = sqlRestore.ReadBackupHeader(sqlServer);
//Check db is for the same app version
if (dt.Rows[0]["BackupDescription"] == System.DBNull.Value || (dt.Rows[0]["BackupDescription"] != System.DBNull.Value && (string)dt.Rows[0]["BackupDescription"] != AppVersion.Version.ToString()))
{
//invalid db
sqlRestore.Abort();
string alter2 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
SqlCommand alter2Cmd = new SqlCommand(alter2, conn);
alter2Cmd.ExecuteNonQuery();
return(string.Format("invaliddbversion"));
}
const string useMaster = "USE master";
SqlCommand useMasterCommand = new SqlCommand(useMaster, conn);
useMasterCommand.ExecuteNonQuery();
string alter1 = @"ALTER DATABASE [" + databaseName + "] SET Single_User WITH Rollback Immediate";
SqlCommand alter1Cmd = new SqlCommand(alter1, conn);
alter1Cmd.ExecuteNonQuery();
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + ".mdf";
String logFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + "_Log.ldf";
Trace.WriteLine(string.Format("IDEA.ERMT: dataFileLocation: {0}", dataFileLocation));
Trace.WriteLine(string.Format("IDEA.ERMT: logFileLocation: {0}", logFileLocation));
//sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA", dataFileLocation));
//sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.PercentCompleteNotification = 10;
try
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restoring"));
sqlRestore.SqlRestore(sqlServer);
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restoring"));
}
catch (Exception ex)
{
Trace.WriteLine(string.Format("IDEA.ERMT: Exception restoring: {0}", ex.Message));
return("errorrestoringdb");
}
finally
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing SET Multi_User"));
string alter3 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
SqlCommand alter3Cmd = new SqlCommand(alter3, conn);
alter3Cmd.ExecuteNonQuery();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "SET Multi_User executed"));
}
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
try
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (Exception)
{
}
try
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing test SQL"));
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
const string SQLQueryTest = @"SELECT * FROM FACTOR";
SqlCommand sqlQueryTestCmd = new SqlCommand(SQLQueryTest, conn);
conn.Open();
sqlQueryTestCmd.ExecuteNonQuery();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Test SQL executed."));
const string SQLAutoCloseOff = @"ALTER DATABASE idea SET AUTO_CLOSE OFF";
sqlQueryTestCmd = new SqlCommand(SQLAutoCloseOff, conn);
sqlQueryTestCmd.ExecuteNonQuery();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Auto_Close OFF executed."));
}
catch (Exception ex)
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Failed executing test SQL"));
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Connection closed"));
}
}
return(string.Empty);
}
private void btnRestore_Click(object sender, EventArgs e)
{
try
{
if (String.IsNullOrEmpty(txtPath.Text))
{
MessageBox.Show("No File Selected");
return;
}
else if (!File.Exists(txtPath.Text))
{
MessageBox.Show("File Not Found");
return;
}
if (String.IsNullOrEmpty(txtDatabaseName.Text))
{
MessageBox.Show("No Database Name Entered");
return;
}
string path = txtPath.Text;
string name = txtDatabaseName.Text;
Restore sqlRestore = new Restore {
Database = name
};
Server sqlServer = GetServer();
if (sqlServer.Databases[name] != null)
{
if (
MessageBox.Show("Delete existing database?", "Confirm Delete", MessageBoxButtons.YesNo,
MessageBoxIcon.Warning) == DialogResult.Yes)
{
sqlServer.KillDatabase(name);
}
else
{
return;
}
}
Database db = new Database(sqlServer, name);
db.Create();
BackupDeviceItem backupDevice = new BackupDeviceItem(path, DeviceType.File);
sqlRestore.Action = RestoreActionType.Database;
sqlRestore.Devices.Add(backupDevice);
var table = sqlRestore.ReadBackupHeader(sqlServer);
var fileNumber = sqlRestore.FileNumber;
foreach (DataRow r in table.Rows)
{
var tempNum = -1;
if (!int.TryParse(r["Position"].ToString(), out tempNum))
{
continue;
}
if (tempNum > fileNumber)
{
fileNumber = tempNum;
}
}
sqlRestore.FileNumber = fileNumber;
String dataFileLocation = db.FileGroups[0].Files[0].FileName;
String logFileLocation = db.LogFiles[0].FileName;
db = sqlServer.Databases[name];
sqlServer.ConnectionContext.Disconnect();
RelocateFile rf = new RelocateFile(sqlRestore.ReadFileList(sqlServer).Rows[0]["LogicalName"].ToString(), dataFileLocation);
RelocateFile lf = new RelocateFile(sqlRestore.ReadFileList(sqlServer).Rows[1]["LogicalName"].ToString(), logFileLocation);
sqlRestore.RelocateFiles.Add(rf);
sqlRestore.RelocateFiles.Add(lf);
sqlRestore.ReplaceDatabase = true;
sqlRestore.PercentCompleteNotification = 1;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(PercentCompleteHandler);
//sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[name];
db.SetOnline();
sqlServer.Refresh();
MessageBox.Show("Restore Completed");
progressBar.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Get the contents of the backup device.
/// </summary>
/// <param name="BackupDeviceName"></param>
private void GetBackupDeviceContents(string BackupDeviceName)
{
Cursor csr = null;
DataTable dataTable;
ListViewItem DeviceListViewItem;
BackupDeviceItem backupDeviceItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Create the restore object
sqlRestore = new Restore();
// Create a file backup device
backupDeviceItem = new BackupDeviceItem(
BackupDeviceName, DeviceType.LogicalDevice);
// Add the backup device to the restore object
sqlRestore.Devices.Add(backupDeviceItem);
// Get the backup header information
dataTable = sqlRestore.ReadBackupHeader(SqlServerSelection);
// Create the columns in the listview
BackupContentsListView.Columns.Clear();
foreach (DataColumn dtCol in dataTable.Columns)
{
BackupContentsListView.Columns.Add(dtCol.ColumnName, 100,
HorizontalAlignment.Left);
}
// Populate the listview
BackupContentsListView.Items.Clear();
for (int rowCount = 0; rowCount < dataTable.Rows.Count; rowCount++)
{
DeviceListViewItem = BackupContentsListView.Items.Add(
dataTable.Rows[rowCount][0].ToString());
for (int columnCount = 1; columnCount < dataTable.Columns.Count; columnCount++)
{
DeviceListViewItem.SubItems.Add(
dataTable.Rows[rowCount][columnCount].ToString());
}
}
// Select the first item in the list
if (BackupContentsListView.Items.Count > 0)
{
BackupContentsListView.Items[0].Selected = true;
BackupContentsListView.Items[0].EnsureVisible();
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
catch (ExecutionFailureException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
private void btnRestore_Click(object sender, EventArgs e)
{
try
{
if (String.IsNullOrEmpty(txtPath.Text))
{
MessageBox.Show("No File Selected");
return;
}
else if (!File.Exists(txtPath.Text))
{
MessageBox.Show("File Not Found");
return;
}
if (String.IsNullOrEmpty(txtDatabaseName.Text))
{
MessageBox.Show("No Database Name Entered");
return;
}
string path = txtPath.Text;
string name = txtDatabaseName.Text;
Restore sqlRestore = new Restore { Database = name };
Server sqlServer = GetServer();
if (sqlServer.Databases[name] != null)
{
if (
MessageBox.Show("Delete existing database?", "Confirm Delete", MessageBoxButtons.YesNo,
MessageBoxIcon.Warning) == DialogResult.Yes)
{
sqlServer.KillDatabase(name);
}
else
{
return;
}
}
Database db = new Database(sqlServer, name);
db.Create();
BackupDeviceItem backupDevice = new BackupDeviceItem(path, DeviceType.File);
sqlRestore.Action = RestoreActionType.Database;
sqlRestore.Devices.Add(backupDevice);
var table = sqlRestore.ReadBackupHeader(sqlServer);
var fileNumber = sqlRestore.FileNumber;
foreach (DataRow r in table.Rows)
{
var tempNum = -1;
if (!int.TryParse(r["Position"].ToString(), out tempNum)) continue;
if (tempNum > fileNumber)
{
fileNumber = tempNum;
}
}
sqlRestore.FileNumber = fileNumber;
String dataFileLocation = db.FileGroups[0].Files[0].FileName;
String logFileLocation = db.LogFiles[0].FileName;
db = sqlServer.Databases[name];
sqlServer.ConnectionContext.Disconnect();
RelocateFile rf = new RelocateFile(sqlRestore.ReadFileList(sqlServer).Rows[0]["LogicalName"].ToString(), dataFileLocation);
RelocateFile lf = new RelocateFile(sqlRestore.ReadFileList(sqlServer).Rows[1]["LogicalName"].ToString(), logFileLocation);
sqlRestore.RelocateFiles.Add(rf);
sqlRestore.RelocateFiles.Add(lf);
sqlRestore.ReplaceDatabase = true;
sqlRestore.PercentCompleteNotification = 1;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(PercentCompleteHandler);
//sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[name];
db.SetOnline();
sqlServer.Refresh();
MessageBox.Show("Restore Completed");
progressBar.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Get the contents of the backup device.
/// </summary>
/// <param name="BackupDeviceName"></param>
private void GetBackupDeviceContents(string BackupDeviceName)
{
Cursor csr = null;
DataTable dataTable;
ListViewItem DeviceListViewItem;
BackupDeviceItem backupDeviceItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Create the restore object
sqlRestore = new Restore();
// Create a file backup device
backupDeviceItem = new BackupDeviceItem(
BackupDeviceName, DeviceType.LogicalDevice);
// Add the backup device to the restore object
sqlRestore.Devices.Add(backupDeviceItem);
// Get the backup header information
dataTable = sqlRestore.ReadBackupHeader(SqlServerSelection);
// Create the columns in the listview
BackupContentsListView.Columns.Clear();
foreach (DataColumn dtCol in dataTable.Columns)
{
BackupContentsListView.Columns.Add(dtCol.ColumnName, 100,
HorizontalAlignment.Left);
}
// Populate the listview
BackupContentsListView.Items.Clear();
for (int rowCount = 0; rowCount < dataTable.Rows.Count; rowCount++)
{
DeviceListViewItem = BackupContentsListView.Items.Add(
dataTable.Rows[rowCount][0].ToString());
for (int columnCount = 1; columnCount < dataTable.Columns.Count; columnCount++)
{
DeviceListViewItem.SubItems.Add(
dataTable.Rows[rowCount][columnCount].ToString());
}
}
// Select the first item in the list
if (BackupContentsListView.Items.Count > 0)
{
BackupContentsListView.Items[0].Selected = true;
BackupContentsListView.Items[0].EnsureVisible();
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
catch (ExecutionFailureException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
static void Main()
{
// For remote connection, remote server name / ServerInstance needs to be specified
ServerConnection srvConn2 = new ServerConnection("machinename" /* <--default sql instance on machinename*/); // or (@"machinename\sqlinstance") for named instances
srvConn2.LoginSecure = false;
srvConn2.Login = "******";
srvConn2.Password = "******";
srvConn2.DatabaseName = "msdb";
Server srv3 = new Server(srvConn2);
//server info
Console.WriteLine("servername:{0} ---- version:{1}", srv3.Name, srv3.Information.Version);
//server root directory
string serverRootDir = srv3.Information.RootDirectory;
//server backup directory
string serverBackupDir = srv3.Settings.BackupDirectory;
//database primary directory
string databasePrimaryFilepath = srv3.Databases[srvConn2.DatabaseName].PrimaryFilePath;
Console.WriteLine("server_root_dir:{0}\nserver_backup_dir:{1}\ndatabase_primary_dir{2}", serverRootDir, serverBackupDir, databasePrimaryFilepath);
Backup bkpDbFull = new Backup();
bkpDbFull.Action = BackupActionType.Database;
//comment out copyonly ....
bkpDbFull.CopyOnly = true; //copy only, just for testing....avoid messing up with existing backup processes
bkpDbFull.Database = srvConn2.DatabaseName;
//backup file name
string backupfile = $"\\backuptest_{DateTime.Now.ToString("dd/MM/yyyy/hh/mm/ss")}.bak";
//add multiple files, in each location
bkpDbFull.Devices.AddDevice(serverRootDir + backupfile, DeviceType.File);
bkpDbFull.Devices.AddDevice(serverBackupDir + backupfile, DeviceType.File);
bkpDbFull.Devices.AddDevice(databasePrimaryFilepath + backupfile, DeviceType.File);
bkpDbFull.Initialize = true;
foreach (BackupDeviceItem backupdevice in bkpDbFull.Devices)
{
Console.WriteLine("deviceitem:{0}", backupdevice.Name);
}
//backup is split/divided amongst the 3 devices
bkpDbFull.SqlBackup(srv3);
Restore restore = new Restore();
restore.Devices.AddRange(bkpDbFull.Devices);
DataTable backupHeader = restore.ReadBackupHeader(srv3);
//IsCopyOnly=True
for (int r = 0; r < backupHeader.Rows.Count; r++)
{
for (int c = 0; c < backupHeader.Columns.Count; c++)
{
Console.Write("{0}={1}\n", backupHeader.Columns[c].ColumnName, (string.IsNullOrEmpty(backupHeader.Rows[r].ItemArray[c].ToString())? "**": backupHeader.Rows[r].ItemArray[c].ToString()));
}
}
srvConn2.Disconnect(); //redundant
}
