//Restores the database using the file resides in the path specified by "backupFilePath"
public void Restore(string backupFilePath)
{
try
{
CreateConnection();
Server svr = new Server(conn);
Database db = svr.Databases[DATABASE];
Restore restore = new Restore
{
Database = DATABASE,
RestrictedUser = true,
Action = RestoreActionType.Database,
ReplaceDatabase = true
};
restore.Devices.AddDevice(backupFilePath, DeviceType.File);
svr.KillAllProcesses(DATABASE);
restore.Wait();
restore.SqlRestore(svr);
}
finally
{
CloseConnection();
}
}
public void RestoreDatabase(String databaseName, String filePath, String serverName, String userName, String password, String dataFilePath, String logFilePath)
{
try
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(serverName, userName, password);
//Add by JP
sqlRestore.ReplaceDatabase = true;
Server sqlServer = new Server(connection);
//Add by JP
sqlServer.KillAllProcesses(databaseName);
sqlRestore.Wait();
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = dataFilePath + databaseName + ".mdf";
String logFileLocation = logFilePath + databaseName + "_Log.ldf";
db = sqlServer.Databases[databaseName];
RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 10;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
}
catch (Exception EX)
{
throw new Exception(EX.Message, EX.InnerException);
}
}
public static void RestoreDb(string file)
{
sqlConnectionInfo();
var restore = new Restore()
{
Database = BackupDatabase.database(), Action = RestoreActionType.Database, ReplaceDatabase = true
};
restore.Devices.AddDevice(file, DeviceType.File);
svr().KillAllProcesses(BackupDatabase.database());
restore.Wait();
restore.SqlRestore(svr());
}
public bool RestoreDataBase(string rootPath, string databaseName, string databaseBackupName)
{
try
{
// Define a Restore object variable.
Restore rs = new Restore();
BackupDeviceItem bdi = new BackupDeviceItem(string.Format("{0}{1}", rootPath, databaseBackupName), DeviceType.File);
// Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = false;
rs.Action = RestoreActionType.Database;
rs.ReplaceDatabase = true;
// Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi);
// Specify the database name.
rs.Database = databaseName;
// Restore the full database backup with no recovery.
Server srv = new Server();
srv.LoginMode = ServerLoginMode.Integrated;
srv.ConnectionContext.StatementTimeout = 60 * 60;
srv.KillDatabase(databaseName);
rs.Wait();
rs.SqlRestore(srv);
// Inform the user that the Full Database Restore is complete.
Console.WriteLine("Full Database Restore complete.");
// Remove the backup files from the hard disk.
// This location is dependent on the installation of SQL Server
//System.IO.File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\Backup\\Test_Full_Backup1");
//System.IO.File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\Backup\\Test_Differential_Backup1");
return(true);
}
catch (Exception ex)
{
return(false);
}
}
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();
}
private void ExecuteTransactions()
{
try
{
var restore_name = _targetDatabase.Name;
Restore restore = new Restore();
var trFile = _transactions[0];
var backupDeviceItem = new BackupDeviceItem(trFile, DeviceType.File);
restore.Devices.Add(backupDeviceItem);
restore.Database = restore_name;
restore.ToPointInTime = _time.ToString("yyyy-MM-ddTHH:mm:ss");
restore.ReplaceDatabase = false;
restore.Action = RestoreActionType.Log;
Logger.Info("Log file left: " + _transactions.Count);
restore.PercentComplete += Restore_PercentComplete;
restore.SqlRestoreAsync(_smoServer);
restore.Information += Restore_Information;
restore.Wait();
Transaction_Complete();
}
catch (SmoException ex)
{
Logger.Error("SMO Message : " + ex.Message);
Logger.Error("SMO Exception : " + ex.InnerException);
}
catch (IOException ex)
{
Logger.Error("IO Message : " + ex.Message);
Logger.Error("IO Exception : " + ex.InnerException);
}
catch (Exception ex)
{
Logger.Error("Message : " + ex.Message);
Logger.Error("Exception : " + ex.InnerException);
}
}
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();
}
}
private void RestoreBackup(string path, string restore_name, RestoreOption restoreOption)
{
try
{
_restoreOption = restoreOption;
InitConnexion();
if (!_smoServer.Databases.Contains(restore_name))
{
var database = new Database(_smoServer, restore_name);
database.Create();
}
Logger.Success("SQL connected");
_targetDatabase = _smoServer.Databases[restore_name];
_targetDatabase.RecoveryModel = RecoveryModel.Simple;
_targetDatabase.Alter(TerminationClause.RollbackTransactionsImmediately);
Restore restore = new Restore();
var backupDeviceItem = new BackupDeviceItem(path, DeviceType.File);
restore.Devices.Add(backupDeviceItem);
restore.Database = restore_name;
restore.ReplaceDatabase = true;
restore.NoRecovery = false;
if (_mode == RestoreMode.AtTime)
{
restore.NoRecovery = true;
}
var fileList = restore.ReadFileList(_smoServer);
var dataFile = new RelocateFile();
dataFile.LogicalFileName = fileList.Rows[0][0].ToString();
dataFile.PhysicalFileName = _smoServer.Databases[restore_name].FileGroups[0].Files[0].FileName;
var logFile = new RelocateFile();
logFile.LogicalFileName = fileList.Rows[1][0].ToString();
logFile.PhysicalFileName = _smoServer.Databases[restore_name].LogFiles[0].FileName;
restore.RelocateFiles.Add(dataFile);
restore.RelocateFiles.Add(logFile);
_smoServer.KillAllProcesses(restore_name);
restore.PercentComplete += Restore_PercentComplete;
restore.SqlRestoreAsync(_smoServer);
restore.Information += Restore_Information;
restore.Wait();
Restore_Complete();
}
catch (SmoException ex)
{
Logger.Error("SMO Message : " + ex.Message);
Logger.Error("SMO Exception : " + ex.InnerException);
}
catch (IOException ex)
{
Logger.Error("IO Message : " + ex.Message);
Logger.Error("IO Exception : " + ex.InnerException);
}
catch (Exception ex)
{
Logger.Error("Message : " + ex.Message);
Logger.Error("Exception : " + ex.InnerException);
}
}
