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);
Server sqlServer = new Server(connection);
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 (SqlServerManagementException ex)
{
ex.Message.ToString();
}
}
public void RestoreDatabase(string databaseName, string backUpFile, string serverName, string restorePath)
{
try
{
ServerConnection connection = new ServerConnection(serverName);
Server sqlServer = new Server(connection);
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
rstDatabase.Database = databaseName;
BackupDeviceItem bkpDevice = new BackupDeviceItem(backUpFile, DeviceType.File);
rstDatabase.Devices.Add(bkpDevice);
var dataFilePath = restorePath;
var logFilePath = restorePath;
//Create The Restore Database Ldf & Mdf file name
string dataFileLocation = dataFilePath + databaseName + ".mdf";
string logFileLocation = logFilePath + databaseName + "_Log.ldf";
RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
// Replace ldf, mdf file name of selected Backup file
System.Data.DataTable logicalRestoreFiles = rstDatabase.ReadFileList(sqlServer);
rstDatabase.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[0][0].ToString(), dataFileLocation));
rstDatabase.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[1][0].ToString(), logFileLocation));
rstDatabase.ReplaceDatabase = true;
rstDatabase.SqlRestore(sqlServer);
}
catch (Exception e)
{
Console.WriteLine("Failed to restore database" + e.Message);
throw e;
}
}
/// <summary>
/// Sets restore plan properties
/// </summary>
private void SetRestorePlanProperties(RestorePlan rp)
{
if (rp == null || rp.RestoreOperations.Count < 1)
{
return;
}
rp.SetRestoreOptions(this.RestoreOptions);
rp.CloseExistingConnections = this.CloseExistingConnections;
if (this.targetDbName != null && !this.targetDbName.Equals(string.Empty))
{
rp.DatabaseName = targetDbName;
}
rp.RestoreOperations[0].RelocateFiles.Clear();
foreach (DbFile dbFile in this.DbFiles)
{
// For XStore path, we don't want to try the getFullPath.
string newPhysicalPath;
Uri pathUri;
bool uriCreated = Uri.TryCreate(dbFile.PhysicalNameRelocate, UriKind.Absolute, out pathUri);
if (uriCreated && pathUri.Scheme == "https")
{
newPhysicalPath = dbFile.PhysicalNameRelocate;
}
else
{
newPhysicalPath = Path.GetFullPath(dbFile.PhysicalNameRelocate);
}
if (!dbFile.PhysicalName.Equals(newPhysicalPath))
{
RelocateFile relocFile = new RelocateFile(dbFile.LogicalName, dbFile.PhysicalNameRelocate);
rp.RestoreOperations[0].RelocateFiles.Add(relocFile);
}
}
}
public void RestoreDB(string databaseName)
{
Console.WriteLine("*** Restoring***");
// Don't drop it! /// If you do, anyone with the default database of that DB will croak!
//server.Databases[databaseName].Drop();
Restore restore = new Restore();
restore.Devices.Clear();
string fileName = string.Format("{0}\\{1}.bak", testFolder, databaseName);
restore.Devices.Add(new BackupDeviceItem(fileName, DeviceType.File));
// Just give it a new name
string destinationDatabaseName = string.Format("{0}_newly_restored", databaseName);
// Go grab the current database's logical names for the data and log files
// For this example, we assume there are 1 for each.
Database currentDatabase = server.Databases[databaseName];
string currentLogicalData = currentDatabase.FileGroups[0].Files[0].Name;
string currentLogicalLog = currentDatabase.LogFiles[0].Name;
// Now relocate the data and log files
RelocateFile reloData = new RelocateFile(currentLogicalData, string.Format(@"{0}\{1}.mdf", testFolder, destinationDatabaseName));
RelocateFile reloLog = new RelocateFile(currentLogicalLog, string.Format(@"{0}\{1}_Log.ldf", testFolder, destinationDatabaseName));
restore.RelocateFiles.Add(reloData);
restore.RelocateFiles.Add(reloLog);
restore.Database = destinationDatabaseName;
restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += new PercentCompleteEventHandler(restore_PercentComplete);
restore.Complete += new ServerMessageEventHandler(restore_Complete);
restore.SqlRestore(server);
}
public void RestoreDatabase(String databaseName, String filePath, String serverName,
String userName, String password, String dataFilePath, String logFilePath)
{
WriteLogFile("Start DB Restore: " + DateTime.Now.ToString());
WriteLogFile("dataFilePath: " + dataFilePath + " logFilePath " + logFilePath);
// Create Restore instance
Restore sqlRestore = new Restore();
// Point to database
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
// Connect to DB Server
ServerConnection connection;
if (userName == "") // for Windows Authentication
{
SqlConnection sqlCon = new SqlConnection(@"Data Source=" + serverName + @";
Integrated Security=True;");
connection = new ServerConnection(sqlCon);
}
else // for Server Authentication
{
connection = new ServerConnection(serverName, userName, password);
}
// Restoring
Server sqlServer = new Server(connection);
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.PercentCompleteNotification = 10;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(myRestore_PercentComplete);
sqlRestore.Complete += new ServerMessageEventHandler(myRestore_Complete);
try
{
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
}
catch (Exception ex)
{
WriteLogFile("error on restore " + ex.Message);
MessageBox.Show("Error occurred on db restore. LogFile: " + DYLogFile);
}
sqlServer.Refresh();
}
public static void RestoreDbFromFile(string serverName, string dbName, string fileName)
{
Server server = CreateServer(serverName);
Restore restore = new Restore
{
Database = dbName
};
restore.Devices.AddDevice(fileName, DeviceType.File);
foreach (DataRow row in restore.ReadFileList(server).Rows)
{
RelocateFile relocateFile = new RelocateFile
{
LogicalFileName = (string)row[DB_FIELD_LOGICALNAME],
PhysicalFileName = NewFileName(server, (string)row[DB_FIELD_TYPE])
};
restore.RelocateFiles.Add(relocateFile);
}
if (server.Databases.Contains(dbName))
{
// Wait until just before restoring to drop any existing database.
server.KillDatabase(dbName);
}
restore.SqlRestore(server);
}
public override async Task Restore(DbRestorOptions opt, IProgressBarProvider progressBarProvider,
Action additionalCallbackOnCompleted)
{
await Task.Run(() =>
{
var srv = new Server(opt.SqlServerInstName);
if (srv.Databases.Contains(opt.TargetDbName))
{
srv.KillDatabase(opt.TargetDbName);
}
var res = new Restore();
srv.ConnectionContext.StatementTimeout = 0;
res.Devices.AddDevice(opt.SrcPath, DeviceType.File);
res.Database = opt.TargetDbName;
res.Action = RestoreActionType.Database;
res.PercentCompleteNotification = 1;
res.ReplaceDatabase = true;
res.Complete += (sender, args) =>
{
if (res.AsyncStatus.ExecutionStatus == ExecutionStatus.Failed)
{
progressBarProvider.OnError(args.Error.ToString());
return;
}
progressBarProvider.OnCompleted(FinishedRestore);
additionalCallbackOnCompleted?.Invoke();
};
res.PercentComplete += (sender, args) =>
{
if (res.AsyncStatus.ExecutionStatus == ExecutionStatus.Failed)
{
progressBarProvider.OnError(res.AsyncStatus.LastException.ToString());
}
// give 10% for the recovering which happens after restoring.
const double weightOfRestoring = 90.0 / 100.0;
progressBarProvider.ReportProgress((int)(args.Percent *weightOfRestoring));
};
var fileList = res.ReadFileList(srv);
var dataFile = new RelocateFile(
logicalFileName: fileList.Rows[0][0].ToString(),
physicalFileName: opt.RelocateMdfTo
);
var logFile = new RelocateFile(
logicalFileName: fileList.Rows[1][0].ToString(),
physicalFileName: opt.RelocateLdfTo
);
res.RelocateFiles.Add(dataFile);
res.RelocateFiles.Add(logFile);
res.ContinueAfterError = false;
progressBarProvider.Start(true, "Restoring...");
res.SqlRestoreAsync(srv);
});
}
/// <summary>
/// phuc thuc phuc hoi du lieu , voi duong dan mac dinh se duoc tai cho thu muc cua chuong trinh
/// </summary>
/// <param name="p_FileName"></param>
/// <param name="p_confItem"></param>
public static void CreateRestore(string p_FileName, COMMAND.Config.ConfigItem p_confItem, object p_objCall)
{
try
{
objFormCall = (Form)p_objCall;
objFormCall.Enabled = false;
objFormCall.Controls["groupControl1"].Controls["lblStatus"].Text = COMMAND.MessageUtils.Message.GetMessageById("BKRS_E_015");
string l_PathApp = Application.StartupPath + "\\App_Data";
if (!System.IO.Directory.Exists(l_PathApp))
{
System.IO.Directory.CreateDirectory(l_PathApp);
}
//khai bao thiet bi du lieu phuc hoi
BackupDeviceItem deviceItem = new BackupDeviceItem(p_FileName, DeviceType.File);
//khai bao doi tuong server
ServerConnection connection = new ServerConnection(p_confItem.StrServerName, p_confItem.StrUserName, p_confItem.StrPassword);
Server sqlServer = new Server(connection);
//khai bao doi tuong database
Database db = sqlServer.Databases[p_confItem.StrDBName];
if (db != null)
{
sqlServer.KillAllProcesses(p_confItem.StrDBName);
}
//khai bao doi tuong phuc hoi du lieu
Restore sqlRestore = new Restore();
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = p_confItem.StrDBName;
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = l_PathApp + "\\" + p_confItem.StrDBName + ".mdf";
String logFileLocation = l_PathApp + "\\" + p_confItem.StrDBName + "_Log.ldf";
RelocateFile rf = new RelocateFile(p_confItem.StrDBName, dataFileLocation);
sqlRestore.RelocateFiles.Add(new RelocateFile(p_confItem.StrDBName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(p_confItem.StrDBName + "_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 10;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
objFormCall.Controls["groupControl1"].Controls["lblStatus"].Text = COMMAND.MessageUtils.Message.GetMessageById("BKRS_E_014");
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[p_confItem.StrDBName];
db.SetOnline();
sqlServer.Refresh();
}
catch (Exception ex)
{
if (objFormCall != null)
{
objFormCall.Controls["groupControl1"].Controls["lblStatus"].Text = COMMAND.MessageUtils.Message.GetMessageById("BKRS_E_010");
objFormCall.Enabled = true;
LogUtils.Logger.AppLog.Debug(objFormCall.Name, p_confItem.Login_UserName,
objFormCall.Controls["groupControl1"].Controls["lblStatus"].Text, ex);
}
}
}
public void RestoreDB(string fileName, string databaseName, string DBPath)
{
Console.WriteLine("*** Restoring***");
// Don't drop it! /// If you do, anyone with the default database of that DB will croak!
//server.Databases[databaseName].Drop();
Restore restore = new Restore();
restore.Devices.Clear();
//string fileName = string.Format("{0}\\{1}.bak", testFolder, databaseName);
restore.Devices.Add(new BackupDeviceItem(fileName, DeviceType.File));
// Just give it a new name
//string destinationDatabaseName = string.Format("{0}_newly_restored", databaseName);
string destinationDatabaseName = databaseName;
// Go grab the current database's logical names for the data and log files
// For this example, we assume there are 1 for each.
Database currentDatabase = server.Databases[databaseName];
string currentLogicalData = "";
string currentLogicalLog = "";
if (currentDatabase != null)
{
currentLogicalData = currentDatabase.FileGroups[0].Files[0].Name;
currentLogicalLog = currentDatabase.LogFiles[0].Name;
if (currentDatabase.ActiveConnections > 0)
{
currentDatabase.SetOffline();
server.KillAllProcesses(destinationDatabaseName);
//currentDatabase.Drop();
}
}
else
{
currentDatabase = server.Databases["master"];
DataSet ds = currentDatabase.ExecuteWithResults(@"restore filelistonly from disk='" + fileName + "'");
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
currentLogicalData = ds.Tables[0].Rows[0].ItemArray[0].ToString();
currentLogicalLog = ds.Tables[0].Rows[1].ItemArray[0].ToString();
}
}
// Now relocate the data and log files
RelocateFile reloData = new RelocateFile(currentLogicalData, string.Format(@"{0}\{1}.mdf", DBPath, destinationDatabaseName));
RelocateFile reloLog = new RelocateFile(currentLogicalLog, string.Format(@"{0}\{1}_Log.ldf", DBPath, destinationDatabaseName));
restore.RelocateFiles.Add(reloData);
restore.RelocateFiles.Add(reloLog);
restore.Database = destinationDatabaseName;
restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 1;
restore.PercentComplete += new PercentCompleteEventHandler(restore_PercentComplete);
restore.Complete += new ServerMessageEventHandler(restore_Complete);
restore.SqlRestore(server);
}
private bool restoreDatabase(String _filePath)
{
if (new CDataConnection().openConnection())
{
m_serverCoon = new ServerConnection();
m_serverCoon.ServerInstance = CDatabase.m_ServerName;
m_server = new Server(m_serverCoon);
m_server.KillAllProcesses(CDatabase.m_DatabaseName);
try
{
Restore _restore = new Restore();
_restore.Devices.AddDevice(_filePath, DeviceType.File);
RelocateFile DataFile = new RelocateFile();
string MDF = _restore.ReadFileList(m_server).Rows[0][1].ToString();
DataFile.LogicalFileName = _restore.ReadFileList(m_server).Rows[0][0].ToString();
DataFile.PhysicalFileName = m_server.Databases[CDatabase.m_DatabaseName].FileGroups[0].Files[0].FileName;
RelocateFile LogFile = new RelocateFile();
string LDF = _restore.ReadFileList(m_server).Rows[1][1].ToString();
LogFile.LogicalFileName = _restore.ReadFileList(m_server).Rows[1][0].ToString();
LogFile.PhysicalFileName = m_server.Databases[CDatabase.m_DatabaseName].LogFiles[0].FileName;
_restore.RelocateFiles.Add(DataFile);
_restore.RelocateFiles.Add(LogFile);
_restore.Database = CDatabase.m_DatabaseName;
_restore.NoRecovery = false;
_restore.ReplaceDatabase = true;
_restore.PercentCompleteNotification = 10;
_restore.PercentComplete += new PercentCompleteEventHandler(percentComplete);
_restore.SqlRestore(m_server);
m_server.Refresh();
m_serverCoon.Disconnect();
return(true);
}
catch (SmoException)
{
//DevExpress.XtraEditors.XtraMessageBox.Show(ex.ToString());
// throw new SmoException(ex.Message, ex.InnerException);
return(false);
}
catch (IOException)
{
//throw new IOException(ex.Message, ex.InnerException);
return(false);
}
}
else
{
return(false);
//DevExpress.XtraEditors.XtraMessageBox.Show("Don't restore data from file");
}
}
public static void RestoreDatabase(string serverName = "(local)", string databaseName = "qlcdv", string filePath = "FullBackUp.bak")
{
conn = new ServerConnection();
conn.ServerInstance = serverName;
srv = new Server(conn);
try
{
Database database = new Database(srv, databaseName);
database.Refresh();
srv.KillAllProcesses(databaseName);
database.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
database.Alter(TerminationClause.RollbackTransactionsImmediately);
Restore res = new Restore();
res.Devices.AddDevice(filePath, 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 = false;
res.ReplaceDatabase = true;
res.SqlRestore(srv);
database.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple;
conn.Disconnect();
}
catch (SmoException ex)
{
throw new SmoException(ex.Message, ex.InnerException);
}
catch (IOException ex)
{
throw new IOException(ex.Message, ex.InnerException);
}
}
public static void Execute(Server server, string folder, string databaseName)
{
var bak = Directory.EnumerateFiles(folder, "*.bak").First(x => x.Contains(databaseName));
//If the database doesn't exist, create it so that we have something
//to overwrite.
if (!server.Databases.Contains(databaseName))
{
Consoler.Information($"Database does not exist... Creating {databaseName}");
var database = new Database(server, databaseName);
database.Create();
}
var targetDatabase = server.Databases[databaseName];
targetDatabase.RecoveryModel = RecoveryModel.Simple;
targetDatabase.Alter();
Restore restore = new Restore
{
Database = databaseName,
ReplaceDatabase = true
};
restore.Devices.AddDevice(bak, DeviceType.File);
restore.Information += Restore_Information;
var fileList = restore.ReadFileList(server);
// restore to new location
var dataFile = new RelocateFile
{
LogicalFileName = fileList.Rows[0][0].ToString(),
PhysicalFileName = Path.Combine(folder, databaseName.WithoutExtension() + ".mdf")
};
var logFile = new RelocateFile
{
LogicalFileName = fileList.Rows[1][0].ToString(),
PhysicalFileName = Path.Combine(folder, databaseName.WithoutExtension() + "_log.log")
};
restore.RelocateFiles.Add(dataFile);
Consoler.Information($"dataFile {dataFile.LogicalFileName}");
restore.RelocateFiles.Add(logFile);
Consoler.Information($"logFile {logFile.LogicalFileName}");
server.KillAllProcesses(databaseName);
Consoler.Information($"KillAllProcesses");
restore.SqlRestore(server);
Consoler.Information($"Database restored ");
}
public static void RestoreDatabase(string serverName, string databaseName, string filePath, string userName,
string password)
{
Console.WriteLine("started restoring up {0}", databaseName);
var conn = new ServerConnection(serverName, userName, password)
{
ConnectTimeout = 200 * 200
};
var srv = new Server(conn);
srv.ConnectionContext.StatementTimeout = 200 * 200;
try
{
var res = new Restore();
res.Devices.AddDevice(filePath, DeviceType.File);
var dataFile = new RelocateFile
{
LogicalFileName = res.ReadFileList(srv).Rows[0][0].ToString(),
PhysicalFileName = srv.Databases[databaseName].FileGroups[0].Files[0].FileName
};
var logFile = new RelocateFile
{
LogicalFileName = res.ReadFileList(srv).Rows[1][0].ToString(),
PhysicalFileName = srv.Databases[databaseName].LogFiles[0].FileName
};
res.RelocateFiles.Add(dataFile);
res.RelocateFiles.Add(logFile);
res.Database = databaseName;
res.NoRecovery = false;
res.ReplaceDatabase = true;
srv.KillAllProcesses(databaseName);
res.SqlRestore(srv);
conn.Disconnect();
Console.WriteLine("finished restoring up {0}", databaseName);
}
catch (SmoException ex)
{
throw new SmoException(ex.Message, ex.InnerException);
}
catch (IOException ex)
{
throw new IOException(ex.Message, ex.InnerException);
}
}
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);
}
}
/***
* Restores a Bak file to a SQL Server
***/
public static bool RestoreBakFile(string dbServerMachineName, string username, string password, string dbName, string databaseBakFile)
{
var serverConnection = new ServerConnection(dbServerMachineName, username, password);
Server server = new Server(serverConnection);
Database database = new Database(server, dbName);
//If Need
database.Create();
database.Refresh();
//Restoring
Restore restore = new Restore
{
NoRecovery = false,
Action = RestoreActionType.Database
};
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(databaseBakFile, DeviceType.File);
restore.Devices.Add(bdi);
restore.Database = dbName;
restore.ReplaceDatabase = true;
RelocateFile DataFile = new RelocateFile();
string MDF = restore.ReadFileList(server).Rows[0][1].ToString();
DataFile.LogicalFileName = restore.ReadFileList(server).Rows[0][0].ToString();
DataFile.PhysicalFileName = server.Databases[dbName].FileGroups[0].Files[0].FileName;
RelocateFile LogFile = new RelocateFile();
string LDF = restore.ReadFileList(server).Rows[1][1].ToString();
LogFile.LogicalFileName = restore.ReadFileList(server).Rows[1][0].ToString();
LogFile.PhysicalFileName = server.Databases[dbName].LogFiles[0].FileName;
restore.RelocateFiles.Add(DataFile);
restore.RelocateFiles.Add(LogFile);
restore.PercentCompleteNotification = 10;
restore.SqlRestore(server);
database.Refresh();
database.SetOnline();
server.Refresh();
return(true);
}
public static bool RestoreDatabase(string databaseName, string filePath, string connectionString)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
conn = new ServerConnection(builder.DataSource, builder.UserID, builder.Password);
srv = new Server(conn);
bool success = false;
try
{
Restore res = new Restore();
Database db = new Database(srv, databaseName);
db.Create();
res.Devices.AddDevice(filePath, 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 = false;
res.ReplaceDatabase = true;
res.SqlRestore(srv);
conn.Disconnect();
success = true;
}
catch (SmoException ex)
{
throw new SmoException(ex.Message, ex.InnerException);
}
catch (IOException ex)
{
throw new IOException(ex.Message, ex.InnerException);
}
return(success);
}
public static void RestoreDatabase(string serverName, string databaseName, string filePath)
{
conn = new ServerConnection();
conn.ServerInstance = serverName;
conn.LoginSecure = false;
conn.Login = "******";
conn.Password = "******";
srv = new Server(conn);
Database db;
db = new Database(srv, databaseName);
db.Create();
try
{
Restore res = new Restore();
res.Devices.AddDevice(filePath, 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 = false;
res.ReplaceDatabase = true;
res.SqlRestore(srv);
conn.Disconnect();
}
catch (SmoException ex)
{
throw new SmoException(ex.Message, ex.InnerException);
}
catch (IOException ex)
{
throw new IOException(ex.Message, ex.InnerException);
}
}
public static void RestoreDatabase(string serverName, string databaseName, string filePath)
{
_conn = new ServerConnection();
_conn.ServerInstance = serverName;
_srv = new Server(_conn);
try
{
var res = new Restore();
_srv.KillAllProcesses(databaseName);
res.Devices.AddDevice(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\" +
filePath, DeviceType.File);
var 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;
var 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 = false;
res.ReplaceDatabase = true;
_srv.KillDatabase(databaseName);
res.SqlRestore(_srv);
_conn.Disconnect();
}
catch (SmoException ex)
{
throw new SmoException(ex.Message, ex.InnerException);
}
catch (IOException ex)
{
throw new IOException(ex.Message, ex.InnerException);
}
}
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);
}
public void Restore(string _BackupFileName)
{
RestartTheService();
Restore dbRestore = new Restore();
ServerConnection sc = new ServerConnection();
dbRestore.Database = _DbName;
dbRestore.Action = RestoreActionType.Database;
dbRestore.ReplaceDatabase = true;
BackupDeviceItem device = new BackupDeviceItem
(_BackupFileName, DeviceType.File);
dbRestore.PercentComplete +=
new PercentCompleteEventHandler(CompletionStatusInPercent);
sc.ServerInstance = ".";
Server MyServer = new Server(sc);
try
{
dbRestore.Devices.Add(device);
RelocateFile DataFile = new RelocateFile();
//string MDF = dbRestore.ReadFileList(MyServer).Rows[0][1].ToString();
DataFile.LogicalFileName = dbRestore.ReadFileList(MyServer).Rows[0][0].ToString();
DataFile.PhysicalFileName = MyServer.Databases[_DbName].FileGroups[0].Files[0].FileName;
RelocateFile LogFile = new RelocateFile();
//string LDF = dbRestore.ReadFileList(MyServer).Rows[1][1].ToString();
LogFile.LogicalFileName = dbRestore.ReadFileList(MyServer).Rows[1][0].ToString();
LogFile.PhysicalFileName = MyServer.Databases[_DbName].LogFiles[0].FileName;
dbRestore.RelocateFiles.Add(DataFile);
dbRestore.RelocateFiles.Add(LogFile);
dbRestore.SqlRestore(MyServer);
sc.Disconnect();
}
catch (Exception exc)
{
dbRestore.Abort();
throw exc;
}
}
public static Exception RestoreDatabase(Info_Server info_Server, string databaseName, string filePath)
{
conn = new ServerConnection(info_Server.Server_Name, info_Server.User, info_Server.Pass);
//conn.ServerInstance = serverName;
srv = new Server(conn);
try
{
Restore res = new Restore();
res.Devices.AddDevice(filePath, 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 = false;
res.ReplaceDatabase = true;
res.SqlRestore(srv);
conn.Disconnect();
}
catch (SmoException ex)
{
return(new Exception(ex.Message, ex.InnerException));
}
catch (IOException ex)
{
return(new Exception(ex.Message, ex.InnerException));
}
return(null);
}
public static void RestoreDatabase(string serverName, string databaseName, string filePath)
{
serverConnection = new ServerConnection {
ServerInstance = serverName
};
server = new Server(serverConnection);
try
{
Restore res = new Restore();
res.Devices.AddDevice(filePath, DeviceType.File);
RelocateFile dataFile = new RelocateFile
{
LogicalFileName = res.ReadFileList(server).Rows[0][0].ToString(),
PhysicalFileName = server.Databases[databaseName].FileGroups[0].Files[0].FileName
};
RelocateFile 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);
serverConnection.Disconnect();
}
catch (SmoException ex)
{
throw new SmoException(ex.Message, ex.InnerException);
}
catch (IOException ex)
{
throw new IOException(ex.Message, ex.InnerException);
}
}
public static void RestoreDb(string path)
{
Server server = new Server("localhost\\SQLEXPRESS");
Restore restore = new Restore();
DirectoryInfo directory = new DirectoryInfo(path);
if (directory.Exists)
{
string fileName = string.Format("{0}\\{1}.bak", path, "LibDB");
restore.Devices.Add(new BackupDeviceItem(fileName, DeviceType.File));
string destinationDatabaseName =
string.Format("LibDB");
Database currentDatabase = server.Databases["LibDB"];
string currentLogicalData =
currentDatabase.FileGroups[0].Files[0].Name;
string currentLogicalLog = currentDatabase.LogFiles[0].Name;
// Now relocate the data and log files
RelocateFile reloData =
new RelocateFile(currentLogicalData,
string.Format(@"{0}\{1}.mdf", path,
destinationDatabaseName));
RelocateFile reloLog =
new RelocateFile(currentLogicalLog,
string.Format(@"{0}\{1} _Log.ldf", path,
destinationDatabaseName));
restore.RelocateFiles.Add(reloData);
restore.RelocateFiles.Add(reloLog);
restore.Database = destinationDatabaseName;
restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 10;
server.KillAllProcesses(destinationDatabaseName);
restore.SqlRestore(server);
}
}
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);
}
}
/// <summary>
/// Creates the new field data base.
/// </summary>
public static void CreateNewFieldDataBase()
{
string applicationDataPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
string userName = "******";
DirectoryInfo dInfo1 = new DirectoryInfo(applicationDataPath);
DirectorySecurity dSecurity1 = dInfo1.GetAccessControl();
dSecurity1.AddAccessRule(new FileSystemAccessRule(userName, FileSystemRights.FullControl, InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow));
dInfo1.SetAccessControl(dSecurity1);
string localFilePath = applicationDataPath + "\\TerrascanFieldDB_Data.bak";
SqlConnection sqlConnection = new SqlConnection(connectionStringMaster);
ServerConnection serverConnection = new ServerConnection(sqlConnection);
Server serverObject = new Server(serverConnection);
Server svr;
svr = serverObject;
Restore rest = new Restore();
rest.Database = TerraScanCommon.FieldDataBaseName;
RelocateFile DATAFILE = new RelocateFile();
RelocateFile LOGFILE = new RelocateFile();
rest.Action = Microsoft.SqlServer.Management.Smo.RestoreActionType.Database;
rest.Devices.AddDevice(localFilePath, DeviceType.File);
string mdf = applicationDataPath + "\\TerrascanFieldDB_Data.mdf";
DATAFILE.LogicalFileName = rest.ReadFileList(svr).Rows[0][0].ToString();
DATAFILE.PhysicalFileName = Path.Combine(Path.GetDirectoryName(applicationDataPath + "\\TerrascanFieldDB_Data.mdf"), TerraScanCommon.FieldDataBaseName + Path.GetExtension(mdf));
string ldf = applicationDataPath + "\\TerrascanFieldDB_Data.ldf";
LOGFILE.LogicalFileName = rest.ReadFileList(svr).Rows[1][0].ToString();
LOGFILE.PhysicalFileName = Path.Combine(Path.GetDirectoryName(ldf), TerraScanCommon.FieldDataBaseName + Path.GetExtension(ldf));
rest.RelocateFiles.Add(DATAFILE);
rest.RelocateFiles.Add(LOGFILE);
rest.PercentCompleteNotification = 10;
rest.ReplaceDatabase = true;
rest.NoRecovery = false;
rest.SqlRestore(svr);
}
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);
}
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 void Import(DataFile file)
{
if (context.DatabaseExists(file.database))
{
throw new InvalidOperationException("database already exists");
}
Restore restore = new Restore();
BackupDeviceItem backup = new BackupDeviceItem(file.path, DeviceType.File);
restore.Database = file.database;
restore.Devices.Add(backup);
RelocateFile relocateDataFile = new RelocateFile(LookupLogicalName(restore, "D"), string.Format(savePath, file.database, DataConfig.mdfExt));
RelocateFile relocateLogFile = new RelocateFile(LookupLogicalName(restore, "L"), string.Format(savePath, file.database, DataConfig.ldfExt));
restore.RelocateFiles.Add(relocateDataFile);
restore.RelocateFiles.Add(relocateLogFile);
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = false;
restore.SqlRestore(SQLServerContext.Server);
context.ChangeDatabase(file.database);
}
void worker_DoWork(object sender, DoWorkEventArgs e)
{
if (e.Argument.ToString() == "getList")
{
e.Result = DbUtility.GetListOfAvailibleDatabaseServers();
}
else
{
try
{
Restore sqlRestore = new Restore();
string assemblyDirPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
string backupPath = assemblyDirPath + @"\DBBackup\SharedAPI.bak";
string databaseName = "Chapter7SharedDatabase";
BackupDeviceItem buDevice = new BackupDeviceItem(backupPath, DeviceType.File);
sqlRestore.Devices.Add(buDevice);
sqlRestore.Database = databaseName;
string serverName = e.Argument.ToString();
ServerConnection connection = new ServerConnection(serverName);
Server sqlServer = new Server(connection);
Information info = sqlServer.Information;
//use default database directories
string dataFilePath = info.MasterDBPath;
string logFilePath = info.MasterDBLogPath;
Database database = sqlServer.Databases[databaseName];
if (database == null)
{
database = new Database(sqlServer, databaseName);
database.Create();
}
String dataFileLocation = dataFilePath + "\\" + databaseName + ".mdf";
String logFileLocation = logFilePath + "\\" + databaseName + "_Log.ldf";
Database db = sqlServer.Databases[databaseName];
RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
sqlRestore.Complete += sqlRestore_Complete;
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.SqlRestore(sqlServer);
}
catch (Microsoft.SqlServer.Management.Smo.FailedOperationException error)
{
SearchingLabel.Dispatcher.BeginInvoke(
new UpdateTextDel(UpdateText),
string.Format("The sample database creation failed with the following error:{0}.",
error.Message)
);
}
catch (ConnectionFailureException connectError)
{
SearchingLabel.Dispatcher.BeginInvoke(
new UpdateTextDel(UpdateText),
string.Format("We could not connect to the database. error:{0}.",
connectError.Message)
);
}
}
}
private void Restore(string databaseName,string backupPath,string dbLogicalName,string dbRelocatePath,
string logLogicalName,string logRelocatePath)
{
Restore restore = new Restore
{
Database = databaseName,
PercentCompleteNotification = 10,
NoRecovery = _diffBackupPending,
ReplaceDatabase = true,
ContinueAfterError = true
};
if (dbRelocatePath != null)
{
RelocateFile rf = new RelocateFile();
rf.LogicalFileName = dbLogicalName;
rf.PhysicalFileName = dbRelocatePath;
restore.RelocateFiles.Add(rf);
RelocateFile rfl = new RelocateFile();
rfl.LogicalFileName = logLogicalName;
rfl.PhysicalFileName = logRelocatePath;
restore.RelocateFiles.Add(rfl);
}
restore.PercentComplete += restore_PercentComplete;
restore.Complete += restore_Complete;
restore.Devices.AddDevice(backupPath, DeviceType.File);
restore.SqlRestoreAsync(_server);
}
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 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();
}
}
}
private void PhucHoiDuLieu()
{
GetDatabaseInformation();
var cnn = new SqlConnection(_connectionString);
cnn.Open();
var conn = new ServerConnection(cnn);
var server = new Server(conn);
cnn.Close();
Restore restore = new Restore();
//string fileName = string.Format("{0}\\{1}.bak", testFolder, databaseName);
string fileName = txtTenTapTin.Text;
restore.Devices.Add(new BackupDeviceItem(fileName, DeviceType.File));
// Just give it a new name
//string destinationDatabaseName = string.Format("{0}_newly_restored", databaseName);
string destinationDatabaseName = txtCoSoDuLieu.Text;
// Go grab the current database’s logical names for the data
// and log files. For this example, we assume there are 1 for each.
//Database currentDatabase = server.Databases[databaseName];
//string currentLogicalData_ = currentDatabase.FileGroups[0].Files[0].Name;
//string currentLogicalLog_ = currentDatabase.LogFiles[0].Name;
// Now relocate the data and log files
//RelocateFile reloData = new RelocateFile(currentLogicalData, string.Format(@"{0}\{1}.mdf", testFolder, destinationDatabaseName));
//RelocateFile reloLog = new RelocateFile(currentLogicalLog, string.Format(@"{0}\{1}_Log.ldf", testFolder, destinationDatabaseName));
RelocateFile reloData = new RelocateFile(currentLogicalData, physicalFileName);
RelocateFile reloLog = new RelocateFile(currentLogicalLog, physicalFileLogName);
//MessageBox.Show(fileName);
//MessageBox.Show(destinationDatabaseName);
//MessageBox.Show(currentLogicalData);
//MessageBox.Show(currentLogicalLog);
//MessageBox.Show(currentLogicalData_);
//MessageBox.Show(currentLogicalLog_);
//MessageBox.Show(string.Format(@"{0}\{1}.mdf", testFolder, destinationDatabaseName));
//MessageBox.Show(string.Format(@"{0}\{1}_Log.ldf", testFolder, destinationDatabaseName));
//MessageBox.Show(physicalFileName);
//MessageBox.Show(physicalFileLogName);
restore.RelocateFiles.Add(reloData);
restore.RelocateFiles.Add(reloLog);
restore.Database = destinationDatabaseName;
restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += restore_PercentComplete;
//restore.Complete += restore_Complete;
restore.ReplaceDatabase = true;
server.KillAllProcesses(txtCoSoDuLieu.Text);
restore.SqlRestore(server);
server.Refresh();
MessageBox.Show("Quá trình hoàn tất");
Bar.EditValue = 0;
Bar.Refresh();
//progressBar1.Value = 0;
//progressBar1.Refresh();
}
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();
}
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 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);
}
}
}
