protected override void ExecuteTask()
{
var server = new Server(ServerName);
server.ConnectionContext.LoginSecure = true;
server.ConnectionContext.Connect();
if (!ReplaceDB)
{
if (server.Databases.Contains(DBName))
{
Log(Level.Info, DBName + " exists on " + ServerName + " and will not be replaced");
return;
}
}
Log(Level.Info, "Restoring database " + DBName + " to " + ServerName);
var restore = new Restore();
restore.Database = DBName;
restore.ReplaceDatabase = ReplaceDB;
restore.Action = RestoreActionType.Database;
restore.Devices.AddDevice(BackupPath, DeviceType.File);
var table = restore.ReadFileList(server);
restore.RelocateFiles.Add(new RelocateFile(table.Rows[0][0].ToString(), Path.Combine(server.Settings.DefaultFile, DBName + ".mdf")));
restore.RelocateFiles.Add(new RelocateFile(table.Rows[1][0].ToString(), Path.Combine(server.Settings.DefaultFile, DBName + "_Log.ldf")));
restore.PercentComplete += UpdatePercent;
restore.Complete += RestoreCompleted;
restore.SqlRestore(server);
if (server.ConnectionContext.IsOpen)
server.ConnectionContext.Disconnect();
}
public static void RestoreDatabase(this Server server, string databaseName, string filePath)
{
var res = new Restore();
res.Devices.AddDevice(filePath, DeviceType.File);
var DataFile = new RelocateFile();
string MDF = res.ReadFileList(server).Rows[0][1].ToString();
DataFile.LogicalFileName = res.ReadFileList(server).Rows[0][0].ToString();
DataFile.PhysicalFileName = server.Databases[databaseName].FileGroups[0].Files[0].FileName;
var LogFile = new RelocateFile();
string LDF = res.ReadFileList(server).Rows[1][1].ToString();
LogFile.LogicalFileName = res.ReadFileList(server).Rows[1][0].ToString();
LogFile.PhysicalFileName = server.Databases[databaseName].LogFiles[0].FileName;
res.RelocateFiles.Add(DataFile);
res.RelocateFiles.Add(LogFile);
res.Database = databaseName;
res.NoRecovery = false;
res.ReplaceDatabase = true;
res.SqlRestore(server);
}
public static bool DbRestore(System.Windows.Forms.OpenFileDialog backup, String databaseName)
{
try
{
dboperation db = new dboperation();
dboperation.GetConn("master");
ServerConnection conn = new ServerConnection();
conn.ServerInstance = ServerInstance();
Server srv = new Server(conn);
Restore res = new Restore();
res.Devices.AddDevice(backup.FileName, DeviceType.File);
RelocateFile DataFile = new RelocateFile();
string MDF = res.ReadFileList(srv).Rows[0][1].ToString();
DataFile.LogicalFileName = res.ReadFileList(srv).Rows[0][0].ToString();
DataFile.PhysicalFileName = srv.Databases[databaseName].FileGroups[0].Files[0].FileName;
RelocateFile LogFile = new RelocateFile();
string LDF = res.ReadFileList(srv).Rows[1][1].ToString();
LogFile.LogicalFileName = res.ReadFileList(srv).Rows[1][0].ToString();
LogFile.PhysicalFileName = srv.Databases[databaseName].LogFiles[0].FileName;
res.RelocateFiles.Add(DataFile);
res.RelocateFiles.Add(LogFile);
res.Database = databaseName;
res.NoRecovery = true;
res.ReplaceDatabase = true;
res.SqlRestore(srv);
conn.Disconnect();
return(true);
}
catch (Exception)
{
}
return(false);
}
/// <summary>
/// Restores each of the backups. If the default file location is available it will move the files to there.
/// </summary>
/// <param name="backupOrder">An ordered list of backups to apply.</param>
/// <param name="databaseName">Database to restore to.</param>
/// <param name="fileRelocation">Option for renaming files during the restore.</param>
public void Restore(IEnumerable <BackupMetadata> backupOrder, string databaseName,
Func <string, string> fileRelocation = null)
{
var restore = new Restore {
Database = databaseName, NoRecovery = true
};
foreach (var backup in backupOrder)
{
var device = BackupFileTools.IsUrl(backup.PhysicalDeviceName) ? DeviceType.Url : DeviceType.File;
var backupDeviceItem = new BackupDeviceItem(backup.PhysicalDeviceName, device);
if (_credentialName != null && device == DeviceType.Url)
{
backupDeviceItem.CredentialName = _credentialName;
}
restore.Devices.Add(backupDeviceItem);
var defaultFileLocations = DefaultFileLocations();
if (defaultFileLocations != null)
{
restore.RelocateFiles.Clear();
foreach (var file in restore.ReadFileList(_server).AsEnumerable())
{
var physicalName = (string)file["PhysicalName"];
var fileName = Path.GetFileName(physicalName) ??
throw new InvalidBackupException($"Physical name in backup is incomplete: {physicalName}");
if (fileRelocation != null)
{
fileName = fileRelocation(fileName);
}
var path = (string)file["Type"] == "L" ? defaultFileLocations?.Log : defaultFileLocations?.Data;
path = path ?? Path.GetFullPath(physicalName);
var newFilePath = Path.Combine(path, fileName);
restore.RelocateFiles.Add(new RelocateFile((string)file["LogicalName"], newFilePath));
}
}
_server.ConnectionContext.StatementTimeout = 86400; // 60 * 60 * 24 = 24 hours
restore.SqlRestore(_server);
restore.Devices.Remove(backupDeviceItem);
}
}
/// <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);
}
}
}
public void CreateDatabaseFromBackupFile(
string databaseName,
string backupFilePath)
{
DropDatabase(databaseName);
using (var cn = new SqlConnection(m_ConnectionString))
{
cn.Open();
var sc = new ServerConnection(cn);
var srv = new Server(sc);
var res = new Restore
{
NoRecovery = false,
Database = databaseName
};
res.Devices.AddDevice(backupFilePath, DeviceType.File);
//
// Db files path (on the SQL server!)
//
DataTable fileList = res.ReadFileList(srv);
foreach (DataRow file2Relocate in fileList.Rows)
{
res.RelocateFiles.Add(new RelocateFile(
// Logical name in the backup
(string)file2Relocate["LogicalName"],
// New physical name
Path.Combine(
srv.Databases["master"].PrimaryFilePath,
Path.ChangeExtension(
databaseName,
Path.GetExtension((string)file2Relocate["PhysicalName"])))));
}
res.SqlRestore(srv);
}
}
public void RestoreDatabase(String databaseName, String backUpFile)
{
//var connection = new ServerConnection("dev.empirisense.com", "hari", "hari123");
//var connection = new ServerConnection(Properties.Settings.Default.ServerName, Properties.Settings.Default.UserName, Properties.Settings.Default.Password);
var connection = new ServerConnection(new SqlConnection(Properties.Settings.Default.AryaDbConnectionString));
var sqlServer = new Server(connection);
var rstDatabase = new Restore {
Action = RestoreActionType.Database, Database = databaseName
};
var bkpDevice = new BackupDeviceItem(backUpFile, DeviceType.File);
rstDatabase.Devices.Add(bkpDevice);
var logicalRestoreFiles = rstDatabase.ReadFileList(sqlServer);
rstDatabase.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[0][0].ToString(), @"E:\MSSQL\Data\" + databaseName + ".mdf"));
rstDatabase.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[1][0].ToString(), @"D:\MSSQL\Data\" + databaseName + ".ldf"));
rstDatabase.ReplaceDatabase = true;
rstDatabase.SqlRestore(sqlServer);
}
public static bool CreateDB(string serverName, string dbName, string backupPath, string organisationCode)
{
Server server = new Server(serverName);
try
{
server.ConnectionContext.LoginSecure = false;
server.ConnectionContext.Login = ConfigurationSettings.AppSettings["DbUser"].ToString();
server.ConnectionContext.Password = ConfigurationSettings.AppSettings["DbPassword"].ToString();
string mdfLogicalName = ConfigurationSettings.AppSettings["MDFLogicalName"].ToString();
string ldfLogicalName = ConfigurationSettings.AppSettings["LDFLogicalName"].ToString();
string dbRelocationPath = ConfigurationSettings.AppSettings["DBRelocationPath"].ToString();
Restore restore = new Restore();
restore.Database = dbName;
restore.Action = RestoreActionType.Database;
restore.Devices.AddDevice(backupPath, DeviceType.File);
restore.NoRecovery = false;
System.Data.DataTable logicalFiles = restore.ReadFileList(server);
if (!System.IO.Directory.Exists(dbRelocationPath + "Organisations\\Organisation" + organisationCode))
{
System.IO.Directory.CreateDirectory(dbRelocationPath + "Organisations\\Organisation" + organisationCode);
}
restore.RelocateFiles.Add(new RelocateFile(mdfLogicalName, dbRelocationPath + "Organisations\\Organisation" + organisationCode + "\\" + dbName + ".mdf"));
restore.RelocateFiles.Add(new RelocateFile(ldfLogicalName, dbRelocationPath + "Organisations\\Organisation" + organisationCode + "\\" + dbName + ".ldf"));
restore.PercentCompleteNotification = 10;
server.ConnectionContext.Connect();
int count = server.Databases.Count;
restore.SqlRestore(server);
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
if (server.ConnectionContext.IsOpen)
{
server.ConnectionContext.Disconnect();
}
}
}
public static bool DbRestoreEx(string string_0, string string_1, string string_2, string string_3,
string string_4)
{
bool flag = false;
string columnString = "";
Restore restoreClass = new Restore();
SQLServer sQLServerClass = new SQLServer();
try
{
try
{
sQLServerClass.LoginSecure = false;
sQLServerClass.Connect(string_0.Trim(), string_1.Trim(), string_2.Trim());
restoreClass.Action = 0;
restoreClass.Database = string_3.Trim();
restoreClass.Files = string_4;
restoreClass.FileNumber = 1;
columnString = restoreClass.ReadFileList(sQLServerClass).GetColumnString(1, 2);
columnString = columnString.Substring(0, columnString.LastIndexOf('\\'));
if (!Directory.Exists(columnString))
{
Directory.CreateDirectory(columnString);
}
restoreClass.ReplaceDatabase = true;
restoreClass.SQLRestore(sQLServerClass);
flag = true;
}
catch (Exception exception)
{
exception.ToString();
MessageBox.Show("请删除与要恢复的数据库同名的数据文件");
}
}
finally
{
sQLServerClass.DisConnect();
}
return(flag);
}
public void RestoreDataBase(string backupFilePath, string destinationDatabaseName, string databaseFolder, string databaseFileName, string databaseLogFileName)
{
try
{
Server srv = GetServer();
Restore restore = new Restore {
Database = destinationDatabaseName, Action = RestoreActionType.Database
};
Database database = srv.Databases[destinationDatabaseName];
if (database != null)
{
srv.KillAllProcesses(destinationDatabaseName);
string primaryFilePath = database.PrimaryFilePath;
if (backupFilePath != null)
{
restore.Devices.AddDevice(backupFilePath, DeviceType.File);
}
DataTable datosbackup = restore.ReadFileList(srv);
restore.ReplaceDatabase = true;
//restore.NoRecovery = true;
string physicalFileName = String.Format(@"{0}\{1}.mdf", primaryFilePath, destinationDatabaseName);
string str3 = String.Format(@"{0}\{1}_log.ldf", primaryFilePath, destinationDatabaseName);
restore.RelocateFiles.Add(new RelocateFile(datosbackup.Rows[0][0].ToString(), physicalFileName));
restore.RelocateFiles.Add(new RelocateFile(datosbackup.Rows[1][0].ToString(), str3));
}
//restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 5;
restore.PercentComplete += Progreso_PercentComplete;
restore.Complete += Progreso_Complete;
restore.SqlRestore(srv);
srv.Databases[destinationDatabaseName].SetOnline();
srv.Databases[destinationDatabaseName].Refresh();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
public static void RestoreDatabase()
{
try
{
ServerConnection connection = new ServerConnection(@"Server\instance", "uname", "PWD");
Server sqlServer = new Server(connection);
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
rstDatabase.Database = "H5MI_Automation_Restore_Backup";
BackupDeviceItem bkpDevice = new BackupDeviceItem(@"E:\DATA\QA_SP\MSSQL11.QA_SP\MSSQL\Backup\H5MI_Automation.bak", DeviceType.File);
rstDatabase.Devices.Add(bkpDevice);
rstDatabase.ReplaceDatabase = true;
//As mentioned in the above solution this code will take care .mdf and .ldf file location issue
foreach (DataRow r in rstDatabase.ReadFileList(sqlServer).Rows)
{
var relocateFile = new RelocateFile();
relocateFile.LogicalFileName = r["LogicalName"].ToString();
Console.WriteLine(relocateFile.LogicalFileName);
var physicalName = r["PhysicalName"].ToString();
Console.WriteLine(physicalName);
var path = System.IO.Path.GetDirectoryName(physicalName);
Console.WriteLine(path);
var filename = System.IO.Path.GetFileName(physicalName);
Console.WriteLine(filename);
physicalName = path + @"\H5MI_Automation_Restore_Backup_" + filename;
Console.WriteLine(physicalName);
relocateFile.PhysicalFileName = physicalName;
Console.WriteLine(relocateFile.PhysicalFileName);
Console.WriteLine(relocateFile);
rstDatabase.RelocateFiles.Add(relocateFile);
}
rstDatabase.SqlRestore(sqlServer);
connection.Disconnect();
}
catch (Exception e)
{
Console.Write(e);
}
}
public BackupInfo GetBackupInfo(string backupFileName, Server server)
{
var restoreDb = new Restore();
var info = new BackupInfo();
restoreDb.Action = RestoreActionType.Database;
restoreDb.Devices.AddDevice(backupFileName, DeviceType.File);
DataTable fileList = restoreDb.ReadFileList(server);
foreach (DataRow row in fileList.Rows)
{
if (row.Field<string>("Type").Equals("D"))
{
info.DataLogicalName = row.Field<string>("LogicalName");
}
else if (row.Field<string>("Type").Equals("L"))
{
info.LogFiles.Add(new DatabaseFile {LogicalName = row.Field<string>("LogicalName")});
}
}
return info;
}
public void Verify()
{
if (string.IsNullOrEmpty(AttachDbFilename))
{
MessageBox.Show("Please select file");
return;
}
try
{
Mouse.OverrideCursor = Cursors.Wait;
var rest = new Restore();
rest.Devices.AddDevice(AttachDbFilename, DeviceType.File);
bool verifySuccessful = rest.SqlVerify(Srv);
if (verifySuccessful)
{
MessageBox.Show("Backup Verified!", "SMO Demos");
DataTable dt = rest.ReadFileList(Srv);
// this.dataGridView1.DataSource = dt;
}
else
{
MessageBox.Show("Backup NOT Verified!", "SMO Demos");
}
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Mouse.OverrideCursor = null;
}
}
private void btnVerify_Click(object sender, EventArgs e)
{
Restore rest = new Restore();
string fileName = this.txtFileName.Text;
this.Cursor = Cursors.WaitCursor;
this.dataGridView1.DataSource = string.Empty;
try
{
rest.Devices.AddDevice(fileName, DeviceType.File);
bool verifySuccessful = rest.SqlVerify(srv);
if (verifySuccessful)
{
MessageBox.Show("Backup Verified!", "SMO Demos");
DataTable dt = rest.ReadFileList(srv);
this.dataGridView1.DataSource = dt;
}
else
{
MessageBox.Show("Backup NOT Verified!", "SMO Demos");
}
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
this.Cursor = Cursors.Default;
}
}
public bool RestoreDatabase(string database, string filePath)
{
using (var sqlConnection = new SqlConnection(ConfigUtil.GetConnectionString()))
{
ServerConnection conn = new ServerConnection(ConfigUtil.GetServerName(), ConfigUtil.GetUserName(), ConfigUtil.GetPassword());
srv = new Server(conn);
try
{
res = new Restore()
{
Database = database,
NoRecovery = false,
ReplaceDatabase = true
};
res.PercentComplete += new PercentCompleteEventHandler(Restore_PercentComplete);
res.Devices.AddDevice(filePath, DeviceType.File);
var DataFile = new RelocateFile();
var MDF = res.ReadFileList(srv).Rows[0][1].ToString();
DataFile.LogicalFileName = res.ReadFileList(srv).Rows[0][0].ToString();
DataFile.PhysicalFileName = srv.Databases[database].FileGroups[0].Files[0].FileName;
var LogFile = new RelocateFile();
var LDF = res.ReadFileList(srv).Rows[1][1].ToString();
LogFile.LogicalFileName = res.ReadFileList(srv).Rows[1][0].ToString();
LogFile.PhysicalFileName = srv.Databases[database].LogFiles[0].FileName;
res.RelocateFiles.Add(DataFile);
res.RelocateFiles.Add(LogFile);
this.KillProcesses(database);
//this.viewModel.RestoreStarted = true;
res.SqlRestore(srv);
return(true);
}
catch (SmoException ex)
{
var innerExceptionMessage = "";
if (ex.InnerException != null)
{
innerExceptionMessage = ex.InnerException.Message;
}
MessageBox.Show(string.Format("{0}\n\n{1}", ex.Message, innerExceptionMessage));
return(false);
}
catch (IOException ex)
{
MessageBox.Show(ex.Message);
return(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
}
public Response Task()
{
Response response = new Response();
string dbBakFile = _db.File.FullName;
string dbtoRestore = _db.DatabaseName;
if (string.IsNullOrEmpty(dbBakFile) || !File.Exists(dbBakFile))
{
response.Status = Response.ResponseMessage.Failed;
response.Message = "No such .bak file found.";
return(response);
}
var smoServer = _db.Server;
var db = smoServer.Databases[dbtoRestore];
if (db != null)
{
smoServer.KillAllProcesses(dbtoRestore);
log.Debug("All processes on db killed");
db.SetOffline();
}
string dataPath = UtilityConfig.GetMachineConfigValue("DataPath");
string dbPath = Path.Combine(dataPath, dbtoRestore + ".mdf");
string logPath = Path.Combine(dataPath, dbtoRestore + ".ldf");
var restore = new Restore();
var deviceItem = new BackupDeviceItem(dbBakFile, DeviceType.File);
restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += restore_PercentComplete;
restore.Complete += restore_Complete;
try
{
restore.Devices.Add(deviceItem);
if (db != null)
{
DataTable dtFileList = restore.ReadFileList(smoServer);
string dbLogicalName = dtFileList.Rows[0][0].ToString();
string logLogicalName = dtFileList.Rows[1][0].ToString();
restore.RelocateFiles.Add(new RelocateFile(dbLogicalName, dbPath));
restore.RelocateFiles.Add(new RelocateFile(logLogicalName, logPath));
}
else
{
Database smoDatabase = new Database(smoServer, dbtoRestore);
FileGroup fg = new FileGroup(smoDatabase, "PRIMARY");
smoDatabase.FileGroups.Add(fg);
DataFile df = new DataFile(fg, dbtoRestore, dbPath);
df.IsPrimaryFile = true;
fg.Files.Add(df);
LogFile lf = new LogFile(smoDatabase, dbtoRestore + "_log", logPath);
smoDatabase.LogFiles.Add(lf);
smoDatabase.Create();
smoServer.Refresh();
smoServer.KillAllProcesses(dbtoRestore);
log.Debug("All processes on db killed");
smoServer.Databases[dbtoRestore].SetOffline();
//restore.DatabaseFiles.Add(dbPath);
//restore.DatabaseFiles.Add(logPath);
DataTable dtFileList = restore.ReadFileList(smoServer);
string dbLogicalName = dtFileList.Rows[0][0].ToString();
string logLogicalName = dtFileList.Rows[1][0].ToString();
restore.RelocateFiles.Add(new RelocateFile(dbLogicalName, dbPath));
restore.RelocateFiles.Add(new RelocateFile(logLogicalName, logPath));
}
restore.Database = dbtoRestore;
restore.FileNumber = 1;
restore.Action = RestoreActionType.Files;
restore.SqlRestore(smoServer);
db = smoServer.Databases[dbtoRestore];
db.SetOnline();
smoServer.Refresh();
db.Refresh();
}
catch (Exception ex)
{
// cleanup
smoServer.Refresh();
db = smoServer.Databases[dbtoRestore];
if (db != null)
{
db.Drop();
}
if (File.Exists(dbPath))
{
File.Delete(dbPath);
}
if (File.Exists(logPath))
{
File.Delete(dbPath);
}
response.Status = Response.ResponseMessage.Failed;
response.Message = ex.GetExceptionMessages();
return(response);
}
response.Status = Response.ResponseMessage.Succeeded;
response.Message = string.Format("{0} file restored to database {1}.", _db.File.Name, dbtoRestore);
return(response);
}
public void RestoreDatabase(SqlConnection connection, string dbName, string filePath)
{
using (connection)
{
connection.Open();
Server srv = new Server(new ServerConnection(connection));
Database db = GetDatabase(srv, dbName);
//srv.KillAllProcesses(db.Name);
// Define a Restore object variable.
Restore rs = new Restore();
if (db.UserAccess != DatabaseUserAccess.Single)
{
db.UserAccess = DatabaseUserAccess.Single;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
db.Refresh();
}
try
{
rs.Action = RestoreActionType.Database;
rs.ReplaceDatabase = true;
//rs.Restart = true;
// Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = false;
//http://social.msdn.microsoft.com/Forums/sqlserver/en-US/fe8dd4c4-3550-48cd-baaf-fae9246ff87a/smo-restore-unable-to-complete-restore?forum=sqlsmoanddmo
// Specify the database name.
rs.Database = db.Name;
// Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(filePath, DeviceType.File);
// Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi);
var dataFile = new RelocateFile
{
LogicalFileName = rs.ReadFileList(srv).Rows[0][0].ToString(),
PhysicalFileName = srv.Databases[dbName].FileGroups[0].Files[0].FileName
};
var logFile = new RelocateFile
{
LogicalFileName = rs.ReadFileList(srv).Rows[1][0].ToString(),
PhysicalFileName = srv.Databases[dbName].LogFiles[0].FileName
};
rs.RelocateFiles.Add(dataFile);
rs.RelocateFiles.Add(logFile);
connection.ChangeDatabase("master"); // You cannot restore a database that you are connected to
rs.PercentCompleteNotification = 1;
rs.PercentComplete += Restore_PercentComplete;
rs.Complete += Restore_Complete;
// Restore the full database backup with no recovery.
rs.SqlRestore(srv); //SqlRestoreAsync won't fire Complete event ?
}
finally
{
db.UserAccess = DatabaseUserAccess.Multiple;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
db.Refresh();
}
}
}
public void Created(string source, string desc, string database)
{
try
{
var fileInfo = new FileInfo(source);
if (!fileInfo.Exists)
{
RaiseCreatedErrorEventHander("Không thể tạo được cơ sở dữ liệu.\nChi Tiết:\n\tCở dữ liệu nguồn không tồn tại vui lòng cài đặt lại phần mềm");
return;
}
if (!Directory.Exists(desc))
{
RaiseCreatedErrorEventHander("Không thể tạo được cơ sở dữ liệu.\nChi Tiết:\n\tThư mục tạo cơ sở dữ liệu không tồn tại.Vui lòng tạo hay chọn một thư mục khác.");
return;
}
var backupFilePath = source;
var destinationDatabaseName = database;
var databaseFolder = desc;
var cnn = new SqlConnection(_mConnectString);
cnn.Open();
var conn = new ServerConnection(cnn);
var myServer = new Server(conn);
//var db = new Database(myServer, database);
// db.StoredProcedures.ItemById(0).Script(new ScriptingOptions());
var myRestore = new Restore {Database = destinationDatabaseName};
//Database currentDb = myServer.Databases[destinationDatabaseName];
//if (currentDb != null)
//myServer.KillAllProcesses(destinationDatabaseName);
myRestore.Devices.AddDevice(backupFilePath, DeviceType.File);
var dt = myRestore.ReadFileList(myServer);
var databaseFileName = "ERP";
var databaseLogFileName = "ERP_Log";
if (dt == null)
{
RaiseCreatedErrorEventHander("Không thể tạo được cơ sở dữ liệu.\nChi Tiết:\n\tTập tin cơ sở dữ liệu nguồn không đúng, vui lòng cài đặt lại phần mềm.");
return;
}
if (dt.Rows.Count != 0)
{
databaseFileName = dt.Rows[0][0].ToString();
databaseLogFileName = dt.Rows[1][0].ToString();
}
var dataFileLocation = databaseFolder + "\\" + destinationDatabaseName + ".mdf";
var logFileLocation = databaseFolder + "\\" + destinationDatabaseName + "_log.ldf";
myRestore.RelocateFiles.Add(new RelocateFile(databaseFileName, dataFileLocation));
myRestore.RelocateFiles.Add(new RelocateFile(databaseLogFileName, logFileLocation));
myRestore.ReplaceDatabase = true;
myRestore.PercentCompleteNotification = 10;
myRestore.PercentComplete +=
myRestore_PercentComplete;
myRestore.Complete += myRestore_Complete;
myRestore.SqlRestore(myServer);
cnn.Close();
}
catch (Exception ex)
{
if (ex.Message.IndexOf("failed for Server") != -1)
{
RaiseCreatedErrorEventHander("Không thể tạo được cơ sở dữ liệu.\nChi Tiết:\n\tThư mục tạo cơ sở dữ liệu đã được bảo vệ, vui lòng chọn thư mục khác.");
}
else
{
RaiseCreatedErrorEventHander("Không thể tạo được cơ sở dữ liệu.\nChi Tiết:\n\t" + ex.Message);
}
}
}
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;
}
public bool RestoreDB(string string_3, string string_4, string string_5, ProgressBar progressBar_1)
{
int i;
bool flag;
this.progressBar_0 = progressBar_1;
string columnString = "";
string str = "";
string str1 = "";
string str2 = "";
SQLServer sQLServerClass = new SQLServer();
try
{
try
{
sQLServerClass.Connect(this.ServerName, this.UserName, this.Password);
QueryResults queryResult = sQLServerClass.EnumProcesses(-1);
int num = -1;
int num1 = -1;
for (i = 1; i <= queryResult.Columns; i++)
{
string columnName = queryResult.ColumnName[i];
if (columnName.ToUpper().Trim() == "SPID")
{
num = i;
}
else if (columnName.ToUpper().Trim() == "DBNAME")
{
num1 = i;
}
if ((num == -1 ? false : num1 != -1))
{
break;
}
}
for (i = 1; i <= queryResult.Rows; i++)
{
int columnLong = queryResult.GetColumnLong(i, num);
if (queryResult.GetColumnString(i, num1).ToUpper() == string_3.ToUpper())
{
sQLServerClass.KillProcess(columnLong);
}
}
Restore restoreClass = new Restore()
{
Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
};
restoreClass.PercentComplete += new RestoreSink_PercentCompleteEventHandler(this.Step);
restoreClass.Files = string_4;
restoreClass.Database = string_3;
restoreClass.RelocateFiles = "[SuperOA],[D:\\aaaa.mdf]";
columnString = restoreClass.ReadFileList(sQLServerClass).GetColumnString(1, 1);
str = restoreClass.ReadFileList(sQLServerClass).GetColumnString(2, 1);
str1 = string.Concat(string_5, columnString, ".mdf");
str2 = string.Concat(string_5, str, ".ldf");
string[] strArrays = new string[] { "[", columnString, "],[", str1, "],[", str, "],[", str2, "]" };
restoreClass.RelocateFiles = string.Concat(strArrays);
restoreClass.ReplaceDatabase = true;
restoreClass.SQLRestore(sQLServerClass);
flag = true;
}
catch (Exception exception)
{
exception.Message.ToString();
MessageBox.Show("恢复数据库失败,请关闭所有和该数据库连接的程序！");
flag = false;
}
}
finally
{
sQLServerClass.DisConnect();
}
return(flag);
}
public void Restore(string backupFile, string DatabaseName)
{
Microsoft.SqlServer.Management.Common.ServerConnection connection;
Console.WriteLine(string.Format("Connecting to SQL server {0}", Server));
// Initialize our common objects
connection = new Microsoft.SqlServer.Management.Common.ServerConnection(Server);
smoServer = new Server(connection);
database = smoServer.Databases[DatabaseName];
if (false && database == null)
{
// Invalid database name.
Console.WriteLine(string.Format("Invalid database '{1}' on server '{0}'.", Server, DatabaseName));
foreach (Database db in smoServer.Databases)
{
Console.WriteLine(string.Format("Available database: '{1}' on server '{0}'.", Server, db.Name));
}
throw new Exception(string.Format("Invalid database '{1}' on server '{0}'.", Server, DatabaseName));
}
Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
restore.Database = DatabaseName;
restore.Devices.AddDevice(backupFile, DeviceType.File);
restore.ReplaceDatabase = true;
RelocateFile DataFile = new RelocateFile();
var fileList = restore.ReadFileList(smoServer);
string MDF = fileList.Rows[0][1].ToString();
DataFile.LogicalFileName = restore.ReadFileList(smoServer).Rows[0][0].ToString();
DataFile.PhysicalFileName = smoServer.Databases[DatabaseName].FileGroups[0].Files[0].FileName;
RelocateFile LogFile = new RelocateFile();
string LDF = restore.ReadFileList(smoServer).Rows[1][1].ToString();
LogFile.LogicalFileName = restore.ReadFileList(smoServer).Rows[1][0].ToString();
LogFile.PhysicalFileName = smoServer.Databases[DatabaseName].LogFiles[0].FileName;
restore.RelocateFiles.Add(DataFile);
restore.RelocateFiles.Add(LogFile);
// Kill all connections on the database.
if (database != null)
{
Console.WriteLine(string.Format("Deleting database {0}.", DatabaseName));
smoServer.KillDatabase(DatabaseName);
//context.TrackBuildMessage("Killing all processes");
//smoServer.KillAllProcesses(database.Name);
//context.TrackBuildMessage("Setting single-user mode");
//database.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
//database.Alter(TerminationClause.RollbackTransactionsImmediately);
//context.TrackBuildMessage("Detatching database");
//smoServer.DetachDatabase(DatabaseName.Get(context), false);
}
// Set some status update event handlers.
restore.PercentCompleteNotification = 10; // Send updates every 10%
restore.PercentComplete += new PercentCompleteEventHandler(RestoreProgressEventHandler);
Console.WriteLine("Restoring");
restore.SqlRestore(smoServer);
}
public void RestoreDataBase(string backupFilePath, string destinationDatabaseName, string databaseFolder, string databaseFileName, string databaseLogFileName)
{
try
{
Server srv = GetServer();
Restore restore = new Restore { Database = destinationDatabaseName, Action = RestoreActionType.Database };
Database database = srv.Databases[destinationDatabaseName];
if (database != null)
{
srv.KillAllProcesses(destinationDatabaseName);
string primaryFilePath = database.PrimaryFilePath;
if (backupFilePath != null) restore.Devices.AddDevice(backupFilePath, DeviceType.File);
DataTable datosbackup = restore.ReadFileList(srv);
restore.ReplaceDatabase = true;
//restore.NoRecovery = true;
string physicalFileName = String.Format(@"{0}\{1}.mdf", primaryFilePath, destinationDatabaseName);
string str3 = String.Format(@"{0}\{1}_log.ldf", primaryFilePath, destinationDatabaseName);
restore.RelocateFiles.Add(new RelocateFile(datosbackup.Rows[0][0].ToString(), physicalFileName));
restore.RelocateFiles.Add(new RelocateFile(datosbackup.Rows[1][0].ToString(), str3));
}
//restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 5;
restore.PercentComplete += Progreso_PercentComplete;
restore.Complete += Progreso_Complete;
restore.SqlRestore(srv);
srv.Databases[destinationDatabaseName].SetOnline();
srv.Databases[destinationDatabaseName].Refresh();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void RS()
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(@"D:\Bakup\Weedon.bak", DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = "Weedon";
ServerConnection connection = new ServerConnection(@".\SQLEXPRESS");
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases["Weedon"];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = @"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Weedon.mdf";
String logFileLocation = @"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Weedon_Log.ldf";
db = sqlServer.Databases["Weedon"];
RelocateFile rf = new RelocateFile("Weedon", dataFileLocation);
//sqlRestore.RelocateFiles.Add(new RelocateFile("Weedon", dataFileLocation));
//sqlRestore.RelocateFiles.Add(new RelocateFile("Weedon" + "_log", logFileLocation));
System.Data.DataTable logicalRestoreFiles = sqlRestore.ReadFileList(sqlServer);
sqlRestore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[0][0].ToString(), dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[1][0].ToString(), logFileLocation));
sqlRestore.ReplaceDatabase = true;
//sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 10;
//sqlRestore.PercentComplete +=
//new PercentCompleteEventHandler(sqlRestore_PercentComplete);
//sqlServer.ConnectionContext.Disconnect();
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases["Weedon"];
db.SetOnline();
sqlServer.Refresh();
}
/// <summary>
/// Handles the ButtonClick event of the btnOldDatabasePath control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="DevExpress.XtraEditors.Controls.ButtonPressedEventArgs"/> instance containing the event data.</param>
/// <summary>
/// Handles the Click event of the btnFinish control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void btnFinish_Click(object sender, EventArgs e)
{
try
{
if (treeView1.Nodes.Count > 0)
{
Cursor = Cursors.WaitCursor;
lstCheck.Clear();
foreach (TreeNode node in treeView1.Nodes)
{
GetCheckNode(node);
}
progressBar1.Visible = true;
progressBar1.Minimum = 0;
progressBar1.Maximum = lstCheck.Count;
int i = 0;
var restore = new Restore
{
Action = RestoreActionType.Database,
Database = txtDatabaseName.Text.Trim(),
ReplaceDatabase = true,
ContinueAfterError = true
};
if (!System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory + DatabaseTemplatePath))
{
XtraMessageBox.Show("Đường dẫn cơ sở dữ liệu mẫu không tồn tại", "Thông báo lỗi",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
var backupDeviceItem =
new BackupDeviceItem(AppDomain.CurrentDomain.BaseDirectory + DatabaseTemplatePath,
DeviceType.File);
restore.Devices.Add(backupDeviceItem);
var fileList = restore.ReadFileList(_server);
restore.RelocateFiles.Add(new RelocateFile(fileList.Rows[0][0].ToString().Trim(),
btnDatabasePath.Text + @"\" + txtDatabaseName.Text.Trim() + ".mdf"));
restore.RelocateFiles.Add(new RelocateFile(fileList.Rows[1][0].ToString().Trim(),
btnDatabasePath.Text + @"\" + txtDatabaseName.Text.Trim() + "_log.ldf"));
restore.SqlRestore(_server);
var database = _server.Databases[txtDatabaseName.Text.Trim()];
if (database != null)
{
database.SetOnline();
}
//update options
var message = _dbOptionsPresenter.Save();
if (message != null)
{
XtraMessageBox.Show(message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
progressBar1.Value = i;
}
//set connect string cho khởi tạo db mới hoặc db đang sử dụng
//xu ly password
var password = RegistryHelperTransform.GetValueByRegistryKey("Password");
if (!string.IsNullOrEmpty(password))
{
password = Crypto.Decrypting(password, "@bgt1me");
}
if (isNewDB)
{
//add new connection string
var dataProvider = ConfigurationManager.AppSettings.Get("ConnectionStringName");
var appConfig =
ConfigurationManager.OpenExeConfiguration(Assembly.GetExecutingAssembly().Location);
var connectionString = string.Format(
"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}",
RegistryHelper.GetValueByRegistryKey("InstanceName"),
txtDatabaseName.Text.Trim(),
RegistryHelper.GetValueByRegistryKey("UserName"),
password);
if (appConfig.ConnectionStrings.ConnectionStrings[dataProvider] == null) //AnhNT: Thêm điều kiện check nếu chưa tồn tại connection thì mới add
{
appConfig.ConnectionStrings.ConnectionStrings.Add(
new ConnectionStringSettings(dataProvider, connectionString));
}
else//Nếu tồn tại thì modify
{
appConfig.ConnectionStrings.ConnectionStrings[dataProvider].ConnectionString =
connectionString;
}
appConfig.Save(ConfigurationSaveMode.Full);
ConfigurationManager.RefreshSection("connectionStrings");
}
//chuyển đổi dữ liệu
string connectString = BuildConnectionString(cbSelectServer.Text, cboSelectSourceDB.Text);
foreach (var check in lstCheck)
{
var result = model.ConvertDB(connectString, check.Name.Substring(4));
if (result == null)
{
i = i + 1;
progressBar1.Value = i;
_result = true;
}
else
{
_result = false;
XtraMessageBox.Show(check.Text + ":" + result, "Thông báo ",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
if (_result == false)
{
var databaseForDelete =
GlobalVariable.Server.Databases[txtDatabaseName.Text.Trim()];
databaseForDelete.Drop();
}
else
{
DialogResult dialogResult = new DialogResult();
if (isNewDB)
{
dialogResult = XtraMessageBox.Show(
"Chuyển đổi cơ sở dữ liệu thành công!Bạn có muốn sử dụng cơ sở dữ liệu đã chuyển đổi",
"Thông báo ",
MessageBoxButtons.YesNo, MessageBoxIcon.Information);
Cursor = Cursors.Default;
}
else
{
dialogResult = XtraMessageBox.Show(
"Chuyển đổi cơ sở dữ liệu thành công!",
"Thông báo ",
MessageBoxButtons.OK, MessageBoxIcon.Information);
Cursor = Cursors.Default;
}
if (dialogResult == DialogResult.Yes)
{
////update registry
RegistryHelperTransform.RemoveConnectionString();
RegistryHelperTransform.SetValueForRegistry("DatabaseName", txtDatabaseName.Text);
var dbPathLenght = btnDatabasePath.Text.LastIndexOf(@"\", StringComparison.Ordinal);
var dbPath = btnDatabasePath.Text;
if (dbPathLenght == -1)
{
dbPath = dbPath + @"\";
}
RegistryHelperTransform.SetValueForRegistry("DatabasePath", dbPath);
RegistryHelperTransform.SaveConfigFile();
System.Windows.Forms.Application.Restart();
}
else
{
if (isNewDB)
{
var dataProvider = ConfigurationManager.AppSettings.Get("ConnectionStringName");
var appConfig =
ConfigurationManager.OpenExeConfiguration(Assembly.GetExecutingAssembly().Location);
//xu ly password
var password = RegistryHelperTransform.GetValueByRegistryKey("Password");
if (!string.IsNullOrEmpty(password))
{
password = Crypto.Decrypting(password, "@bgt1me");
}
var connectionString = string.Format(
"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}",
RegistryHelperTransform.GetValueByRegistryKey("InstanceName"),
RegistryHelperTransform.GetValueByRegistryKey("DatabaseName"),
RegistryHelperTransform.GetValueByRegistryKey("UserName"),
password);
appConfig.ConnectionStrings.ConnectionStrings[dataProvider].ConnectionString =
connectionString;
appConfig.Save(ConfigurationSaveMode.Full);
ConfigurationManager.RefreshSection("connectionStrings");
}
Close();
}
}
}
catch (Exception ex)
{
Cursor = Cursors.Default;
XtraMessageBox.Show(ex.ToString(),
"", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Handles the Click event of the btnFinish control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void btnFinish_Click(object sender, EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor;
var restore = new Restore
{
Action = RestoreActionType.Database,
Database = txtDatabaseName.Text.Trim(),
ReplaceDatabase = true,
ContinueAfterError = true
};
if (!System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory + DatabaseTemplatePath))
{
XtraMessageBox.Show(ResourceHelper.GetResourceValueByName("ResDatabaseTempNotExist"), ResourceHelper.GetResourceValueByName("ResExceptionCaption"),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
var backupDeviceItem = new BackupDeviceItem(AppDomain.CurrentDomain.BaseDirectory + DatabaseTemplatePath, DeviceType.File);
restore.Devices.Add(backupDeviceItem);
var fileList = restore.ReadFileList(_server);
string PathBackUp = btnDatabasePath.Text;
string ExportXmlPath = "";
if (!Directory.Exists(PathBackUp))
{
string BPath = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
PathBackUp = Path.Combine(BPath, "BACKUP");
ExportXmlPath = Path.Combine(BPath, "EXPORTXML");
System.IO.Directory.CreateDirectory(PathBackUp);
System.IO.Directory.CreateDirectory(ExportXmlPath);
DBOptions.Add(new DBOptionModel {
OptionId = "PathBackup", OptionValue = PathBackUp, ValueType = 0, Description = "Thư mục sao lưu", IsSystem = false
});
DBOptions.Add(new DBOptionModel {
OptionId = "PathXML", OptionValue = ExportXmlPath, ValueType = 0, Description = "", IsSystem = false
});
}
restore.RelocateFiles.Add(new RelocateFile(fileList.Rows[0][0].ToString().Trim(), PathBackUp + @"\" + txtDatabaseName.Text.Trim() + ".mdf"));
restore.RelocateFiles.Add(new RelocateFile(fileList.Rows[1][0].ToString().Trim(), PathBackUp + @"\" + txtDatabaseName.Text.Trim() + "_log.ldf"));
restore.SqlRestore(_server);
var database = _server.Databases[txtDatabaseName.Text.Trim()];
if (database != null)
{
database.SetOnline();
}
////LinhMC change logicalName, ThangNK comment lại khi chuyển sang tư vấn nó ko lỗi
//if (_server != null)
//{
// var databaseName = txtDatabaseName.Text.Trim();
// string strCommand = "ALTER DATABASE " + databaseName + " MODIFY FILE (NAME = aBigTime, NEWNAME = " +
// databaseName + "); ALTER DATABASE " + databaseName +
// " MODIFY FILE (NAME = aBigTime_log, NEWNAME = " + databaseName + "_log);";
// _server.ConnectionContext.ExecuteNonQuery(strCommand);
//}
//update registry
RegistryHelper.RemoveConnectionString();
RegistryHelper.SetValueForRegistry("DatabaseName", txtDatabaseName.Text);
var dbPathLenght = PathBackUp.LastIndexOf(@"\", StringComparison.Ordinal);
var dbPath = PathBackUp;
if (dbPathLenght == -1)
{
dbPath = dbPath + @"\";
}
RegistryHelper.SetValueForRegistry("DatabasePath", dbPath);
RegistryHelper.SaveConfigFile();
//update options
var message = _dbOptionsPresenter.Save();
if (message != null)
{
XtraMessageBox.Show(message, ResourceHelper.GetResourceValueByName("ResDetailContent"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//update currency
//_currencyPresenter.DisplayByCurrencyCode(cboCurrencyAccounting.EditValue == null ? null : cboCurrencyAccounting.EditValue.ToString());
//if (CurrencyId != 0)
//{
// IsActive = true;
// _currencyPresenter.Save();
//}
//_currencyPresenter.DisplayByCurrencyCode(cboCurrencyAccounting.EditValue == null ? null : cboCurrencyLocal.EditValue.ToString());
//if (CurrencyId != 0)
//{
// IsActive = true;
// _currencyPresenter.Save();
//}
//_currencyPresenter.DisplayByCurrencyCode(cboCurrencyAccounting.EditValue == null ? null : cboCurrencyCodeOfSalary.EditValue.ToString());
//if (CurrencyId != 0)
//{
// IsActive = true;
// _currencyPresenter.Save();
//}
XtraMessageBox.Show(ResourceHelper.GetResourceValueByName("ResCreateDBSucces"),
ResourceHelper.GetResourceValueByName("ResDetailContent"), MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
////update account
//_accountPresenter.DisplayByAccountCode("11122");
//CurrencyCode = cboCurrencyLocal.EditValue.ToString().Trim();
//_accountPresenter.Save();
//_accountPresenter.DisplayByAccountCode("11222");
//CurrencyCode = cboCurrencyLocal.EditValue.ToString().Trim();
//_accountPresenter.Save();
Cursor = Cursors.Default;
System.Windows.Forms.Application.Restart();
}
catch (Exception ex)
{
Cursor = Cursors.Default;
XtraMessageBox.Show(ex.ToString(),
ResourceHelper.GetResourceValueByName("ResExceptionCaption"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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);
}
}
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);
}
}
internal bool RestoreDatabase(string databaseRestoreName, string BackupFilename, EventHandler handler)
{
UpdateCallback = handler;
Db = InitServer(databaseRestoreName);
Error = "";
try
{
var rs = new Restore
{
NoRecovery = false,
};
rs.PercentComplete += CompletionStatusInPercent;
var bdi = new BackupDeviceItem(BackupFilename, DeviceType.File);
rs.Devices.Add(bdi);
rs.ReplaceDatabase = true;
rs.Database = databaseRestoreName;
rs.Action = RestoreActionType.Database;
var fileList = new DataTable();
try
{
fileList = rs.ReadFileList(server);
}
catch (Exception tex)
{
Error = $"{ tex.Message}";
}
string dataLogicalName = fileList.Rows[0][0].ToString();
// string dataPhysicalName = fileList.Rows[0][1].ToString();
string logLogicalName = fileList.Rows[1][0].ToString();
// string logPhysicalName = fileList.Rows[1][1].ToString();
var path = server.MasterDBPath;
var DataFile = new RelocateFile
{
LogicalFileName = dataLogicalName,
PhysicalFileName = Path.Combine(path, databaseRestoreName) + ".mdf"
};
var LogFile = new RelocateFile()
{
LogicalFileName = logLogicalName,
PhysicalFileName = Path.Combine(path, databaseRestoreName) + "_log.ldf"
};
rs.RelocateFiles.Clear();
rs.RelocateFiles.Add(DataFile);
rs.RelocateFiles.Add(LogFile);
rs.SqlRestore(server);
}
catch (Exception ex)
{
Error = $"An error ocurred backup up database {databaseRestoreName} - {ex.Message}";
return(false);
}
return(true);
}
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);
}
}
public static int RestoreDB(string ConnectionString, string SourceBAKfile, string NewDBname, Registrador.IRegistroEjecucion Logger)
{
try {
ServerConnection oConnection = Connection(ConnectionString);
Server oServer = new Server(oConnection);
Database oDataBase = oServer.Databases[NewDBname];
if (oDataBase != null)
{
Logger.RegistrarError("ya existe una BD con el nombre '" + NewDBname + "', por lo cual no es posible restaurar la BD y así evitar perdida de información.");
return(2);
}
else
{
BackupDeviceItem oDevice = new BackupDeviceItem(SourceBAKfile, DeviceType.File);
Restore oRestore = new Restore();
RelocateFile DataFile = new RelocateFile();
RelocateFile LogFile = new RelocateFile();
string Path = oServer.MasterDBPath;
oServer.ConnectionContext.StatementTimeout = 6000;
oRestore.Devices.Add(oDevice);
string LogicFileName_MDF = oRestore.ReadFileList(oServer).Rows[0][0].ToString();
DataFile.LogicalFileName = LogicFileName_MDF;
DataFile.PhysicalFileName = Path + "\\" + NewDBname + ".mdf";
string LogicFileName_LDF = oRestore.ReadFileList(oServer).Rows[1][0].ToString();
LogFile.LogicalFileName = LogicFileName_LDF;
LogFile.PhysicalFileName = Path + "\\" + NewDBname + ".ldf";
oRestore.NoRecovery = false;
oRestore.RelocateFiles.Add(DataFile);
oRestore.RelocateFiles.Add(LogFile);
oRestore.Database = NewDBname;
Logger.Registrar(DateTime.Now.ToString("HH:mm:ss") + " Iniciando restauración.");
oRestore.SqlRestore(oServer);
Logger.Registrar(DateTime.Now.ToString("HH:mm:ss") + " Finalizando restauración.");
oRestore.Devices.Remove(oDevice);
oDataBase = oServer.Databases[NewDBname];
oDataBase.RecoveryModel = RecoveryModel.Simple;
oDataBase.Alter();
oConnection.Disconnect();
}
} catch (Exception ex) {
Exception InnerEx = ex.InnerException;
string MoreDetails = InnerEx != null ? Environment.NewLine + "Mas detalles: " + InnerEx.Message:"";
if (InnerEx != null)
{
MoreDetails += InnerEx.InnerException != null ? Environment.NewLine + InnerEx.InnerException.Message : "";
}
Logger.RegistrarError("No fue posible la restaurar la BD con el nombre '" + NewDBname + "' . Detalles: " + Environment.NewLine + ex.Message + MoreDetails);
DropDB(ConnectionString, NewDBname, Logger);
return(1);
}
return(0);
}
/// <summary>
/// Handles the Click event of the btnFinish control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void btnFinish_Click(object sender, EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor;
var restore = new Restore
{
Action = RestoreActionType.Database,
Database = txtDatabaseName.Text.Trim(),
ReplaceDatabase = true,
ContinueAfterError = true
};
if (!System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory + DatabaseTemplatePath))
{
XtraMessageBox.Show(ResourceHelper.GetResourceValueByName("ResDatabaseTempNotExist"), ResourceHelper.GetResourceValueByName("ResExceptionCaption"),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
var backupDeviceItem = new BackupDeviceItem(AppDomain.CurrentDomain.BaseDirectory + DatabaseTemplatePath, DeviceType.File);
restore.Devices.Add(backupDeviceItem);
var fileList = restore.ReadFileList(_server);
restore.RelocateFiles.Add(new RelocateFile(fileList.Rows[0][0].ToString().Trim(), btnDatabasePath.Text + @"\" + txtDatabaseName.Text.Trim() + ".mdf"));
restore.RelocateFiles.Add(new RelocateFile(fileList.Rows[1][0].ToString().Trim(), btnDatabasePath.Text + @"\" + txtDatabaseName.Text.Trim() + "_log.ldf"));
restore.SqlRestore(_server);
var database = _server.Databases[txtDatabaseName.Text.Trim()];
if (database != null)
{
database.SetOnline();
}
////LinhMC change logicalName, ThangNK comment lại khi chuyển sang tư vấn nó ko lỗi
//if (_server != null)
//{
// var databaseName = txtDatabaseName.Text.Trim();
// string strCommand = "ALTER DATABASE " + databaseName + " MODIFY FILE (NAME = aBigTime, NEWNAME = " +
// databaseName + "); ALTER DATABASE " + databaseName +
// " MODIFY FILE (NAME = aBigTime_log, NEWNAME = " + databaseName + "_log);";
// _server.ConnectionContext.ExecuteNonQuery(strCommand);
//}
//update registry
RegistryHelper.RemoveConnectionString();
RegistryHelper.SetValueForRegistry("DatabaseName", txtDatabaseName.Text);
RegistryHelper.SetValueForRegistry("DatabasePath", btnDatabasePath.Text + @"\");
RegistryHelper.SaveConfigFile();
//update options
var message = _dbOptionsPresenter.Save();
if (message != null)
{
XtraMessageBox.Show(message, ResourceHelper.GetResourceValueByName("ResDetailContent"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//update currency
_currencyPresenter.DisplayByCurrencyCode(cboCurrencyAccounting.EditValue == null ? null : cboCurrencyAccounting.EditValue.ToString());
if (CurrencyId != 0)
{
IsActive = true;
_currencyPresenter.Save();
}
_currencyPresenter.DisplayByCurrencyCode(cboCurrencyAccounting.EditValue == null ? null : cboCurrencyLocal.EditValue.ToString());
if (CurrencyId != 0)
{
IsActive = true;
_currencyPresenter.Save();
}
_currencyPresenter.DisplayByCurrencyCode(cboCurrencyAccounting.EditValue == null ? null : cboCurrencyCodeOfSalary.EditValue.ToString());
if (CurrencyId != 0)
{
IsActive = true;
_currencyPresenter.Save();
}
XtraMessageBox.Show(ResourceHelper.GetResourceValueByName("ResCreateDBSucces"), ResourceHelper.GetResourceValueByName("ResDetailContent"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//update account
_accountPresenter.DisplayByAccountCode("11122");
CurrencyCode = cboCurrencyLocal.EditValue.ToString().Trim();
_accountPresenter.Save();
_accountPresenter.DisplayByAccountCode("11222");
CurrencyCode = cboCurrencyLocal.EditValue.ToString().Trim();
_accountPresenter.Save();
//update cac khoan luong
LoadPayItem();
if (cboCurrencyCodeOfSalary.EditValue.Equals("USD"))
{
foreach (var payItem in _payItems)
{
_payItemPresenter.Display(payItem.PayItemId.ToString(CultureInfo.InvariantCulture));
CreditAccountCode = "11121";
_payItemPresenter.Save();
}
}
else
{
foreach (var payItem in _payItems)
{
_payItemPresenter.Display(payItem.PayItemId.ToString(CultureInfo.InvariantCulture));
CreditAccountCode = "11122";
_payItemPresenter.Save();
}
}
LoadAutoBusiness();
if (cboCurrencyLocal.EditValue != null && cboCurrencyAccounting.EditValue != null)
{
var lstAutoBusinessByNotUSD = _autoAutoBusiness.Where(w => w.CurrencyCode != null && w.CurrencyCode != string.Empty && w.CurrencyCode != cboCurrencyAccounting.EditValue.ToString()).ToList();
foreach (var autoBusiness in lstAutoBusinessByNotUSD)
{
_autoBusinessPresenter.Display(autoBusiness.AutoBusinessId.ToString(CultureInfo.InvariantCulture));
CurrencyCode = cboCurrencyLocal.EditValue.ToString();
_autoBusinessPresenter.Save();
}
}
Cursor = Cursors.Default;
System.Windows.Forms.Application.Restart();
}
catch (Exception ex)
{
Cursor = Cursors.Default;
XtraMessageBox.Show(ex.ToString(),
ResourceHelper.GetResourceValueByName("ResExceptionCaption"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
