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 Task<BackupResult> CreateBackup(string backupFilePath, string instanceName, string database, string username, string password)
{
return new Task<BackupResult>(() =>
{
var filePath = string.Format("{0}\\{1}_{2}.bak", backupFilePath, DateTime.Now.ToString("yyyyMMdd_HHmm"), database);
var backupDeviceItem = new BackupDeviceItem(filePath, DeviceType.File);
var backup = new Backup
{
Database = database,
Initialize = true,
Incremental = false,
CopyOnly = true,
Action = BackupActionType.Database,
};
backup.Devices.Add(backupDeviceItem);
var serverConnection = new ServerConnection(instanceName, username, password);
var server = new Server(serverConnection);
backup.PercentComplete += PercentComplete;
backup.SqlBackup(server);
return new BackupResult(filePath, BackupType.Sql);
});
}
/// <summary>
/// Method used to restore the database
/// </summary>
/// <param name="databaseName">database name</param>
/// <param name="filePath">file path of the database</param>
/// <param name="connectionString">sql connection</param>
/// <param name="dataFilePath">mdf file path</param>
/// <param name="logFilePath">ldf file path</param>
/// <param name="dataBaseFileName">database file name of the restored.</param>
public void RestoreDatabase(string databaseName, string filePath, SqlConnection connectionString, string dataFilePath, string logFilePath, string dataBaseFileName)
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(connectionString);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
string dataFileLocation = dataFilePath + dataBaseFileName + ".mdf";
string logFileLocation = logFilePath + dataBaseFileName + "_Log.ldf";
db = sqlServer.Databases[databaseName];
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(this.SqlRestoreComplete);
sqlRestore.PercentCompleteNotification = 10;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(this.SqlRestorePercentComplete);
sqlRestore.SqlRestore(sqlServer);
//db = sqlServer.Databases[databaseName];
//db.SetOnline();
sqlServer.Refresh();
}
public bool RestoreDatabase()
{
timer1.Tick -= timer1_Tick;
try
{
Restore sqlRestore = new Restore();
sqlRestore.Action = RestoreActionType.Database;
sqlRestore.Database = BancoDados;
BackupDeviceItem deviceItem = new BackupDeviceItem(ArqBackup, DeviceType.File);
ServerConnection connection = new ServerConnection(Servidor, Usuario, Senha);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[BancoDados];
sqlRestore.Devices.Add(deviceItem);
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
// AQUI VC SETA O VALOR PARA 1;
sqlRestore.PercentCompleteNotification = 1;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
sqlRestore.SqlRestore(sqlServer);
timer1.Tick += timer1_Tick;
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Ocorreram problemas ao Restaurar o Backup!!!\n\n" + ex.InnerException.Message + "\n" + ex.Message, "Controller ERP", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
public static void LogBackup(string fileShare, SMO.Server sourceServer, string dbName)
{
string backupFilePath;
string fileName = string.Format(backupFileNameTemplate, dbName, BackupActionType.Log.ToString());
backupFilePath = Path.Combine(fileShare, fileName);
//delete the backup file
File.Delete(backupFilePath);
try
{
BackupDeviceItem backupDeviceItem = new BackupDeviceItem(backupFilePath, DeviceType.File);
//backup the database from the source server
Backup backup = new Backup();
backup.Action = BackupActionType.Log;
backup.Database = dbName;
backup.Devices.Add(backupDeviceItem);
backup.Incremental = true;
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.SqlBackup(sourceServer);
}
catch (Exception ex)
{
//if an exception happens, delete the file
File.Delete(backupFilePath);
Console.WriteLine("transilent backup failed");
}
}
public void RestoreDatabase(SqlConnectionStringBuilder sqlConnection, String backUpFile)
{
ServerConnection serverConnection = null;
try
{
if (!FileManager.FileExists(backUpFile))
{
throw new FileNotFoundException();
}
serverConnection = new ServerConnection(sqlConnection.DataSource, sqlConnection.UserID, sqlConnection.Password);
Server sqlServer = new Server(serverConnection);
Restore restoreDatabase = new Restore()
{
Action = RestoreActionType.Database,
Database = sqlConnection.InitialCatalog,
};
BackupDeviceItem backupItem = new BackupDeviceItem(backUpFile, DeviceType.File);
restoreDatabase.Devices.Add(backupItem);
restoreDatabase.ReplaceDatabase = true;
restoreDatabase.SqlRestore(sqlServer);
}
finally
{
if (serverConnection != null && serverConnection.IsOpen)
{
serverConnection.Disconnect();
}
}
}
/// <summary>
/// Создать резервную копию БД.
/// </summary>
/// <param name="dbName"> название БД </param>
/// <param name="backupFolderName"> папка, где будет размещена резервная копия </param>
/// <param name="sqlConnect"> строка подключения к БД </param>
/// <returns> результат выполнения </returns>
public static ActionResult BackupDB(string dbName, string backupFolderName, string sqlConnect)
{
string msg = "";
bool isSuccessful = false;
try
{
if (!Directory.Exists(backupFolderName))
{
Directory.CreateDirectory(backupFolderName);
}
string filePath = Path.Combine(backupFolderName, string.Format("{0}-{1}.bak", dbName, DateTime.Now.ToString("yyyy-MM-dd")));
SqlConnection con = new SqlConnection(sqlConnect);
Server server = new Server(new ServerConnection(con));
Backup source = new Backup();
source.CompressionOption = BackupCompressionOptions.On;
source.Action = BackupActionType.Database;
source.Database = dbName;
BackupDeviceItem destination = new BackupDeviceItem(filePath, DeviceType.File);
source.Devices.Add(destination);
source.SqlBackup(server);
isSuccessful = true;
}
catch (Exception e)
{
msg = e.Message;
}
return(new ActionResult(isSuccessful, msg));
}
public static void BackupDatabase(String databaseName, String destinationPath, SqlConnection connection)
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase:" +
DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database = databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
ServerConnection serverConn = new ServerConnection(connection);
Server sqlServer = new Server(serverConn);
//Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
}
public void Backup()
{
// Define a Backup object variable.
Backup bk = new Backup();
// Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database;
bk.BackupSetDescription = $"Full backup of {Name}";
bk.BackupSetName = $"{Name} Backup";
bk.Database = $"{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($"Full_Backup_{Name}_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.bak", DeviceType.File);
// Add the device to the Backup object.
bk.Devices.Add(bdi);
// Set the Incremental property to False to specify that this is a full database backup.
bk.Incremental = false;
// Set the expiration date.
bk.ExpirationDate = DateTime.Now.AddYears(1);
// Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate;
// Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(SmoServer);
}
public static void Backup(string nameDatenbank, string conString, string dateiAusgabe)
{
var connection = new SqlConnection(conString);
var serverConnection = new ServerConnection(connection);
var server = new Server(serverConnection);
var database = server.Databases[nameDatenbank];
Backup backup = new Backup
{
Action = BackupActionType.Database,
BackupSetDescription = $"{nameDatenbank} - full backup",
BackupSetName = $"{nameDatenbank} backup",
Database = nameDatenbank,
PercentCompleteNotification = 10,
Incremental = false,
LogTruncation = BackupTruncateLogType.Truncate,
};
var deviceItem = new BackupDeviceItem(dateiAusgabe, DeviceType.File);
backup.Devices.Add(deviceItem);
backup.Information += (sender, e) => Ausgabe(ConsoleColor.White, $"DB Info: {e.ToString().Replace("Microsoft.Data.SqlClient.SqlError: ", "")}");
backup.NextMedia += (sender, e) => Ausgabe(ConsoleColor.Gray, $"DB Media: {e.Error}");
backup.PercentComplete += (sender, e) => Ausgabe(ConsoleColor.White, $"DB Message: {e.Message}");
backup.Complete += (sender, e) => Ausgabe(ConsoleColor.Yellow, "DB BackUp abgeschlossen.");
backup.SqlBackup(server);
return;
}
private static void RestoreDB(ServerConnection connection, string dbName, string backupPath)
{
Server smoServer = new Server(connection);
Database db = smoServer.Databases[dbName];
if (db == null)
{
db = new Database(smoServer, dbName);
db.Create();
db.Refresh();
}
Restore restore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(backupPath, DeviceType.File);
restore.Devices.Add(deviceItem);
restore.Database = dbName;
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;
restore.NoRecovery = false;
var mdfPath = $"{smoServer.DefaultFile}{dbName}.mdf";
var ldfPath = $"{smoServer.DefaultFile}{dbName}_log.ldf";
System.Data.DataTable logicalRestoreFiles = restore.ReadFileList(smoServer);
restore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[0][0].ToString(), mdfPath));
restore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[1][0].ToString(), ldfPath));
smoServer.KillAllProcesses(dbName);
restore.SqlRestore(smoServer);
db = smoServer.Databases[dbName];
db.SetOnline();
smoServer.Refresh();
db.Refresh();
}
//Method, pass your databaseName, username, password, server name and destination path to save backup file
public bool BackupDatabase(string databaseName, string destinationPath)
{
try
{
//Define a Backup object variable.
Backup sqlBackup = new Backup();
//Specify the type of backup, the description, the name, and the database to be backed up.
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = string.Format("BackUp_{0}_{1}.bak", databaseName, DateTime.Now.ToString("yyyyMMddHHmmss"));
sqlBackup.BackupSetName = string.Format("BackUp_{0}_{1}.bak", databaseName, DateTime.Now.ToString("yyyyMMddHHmmss"));
sqlBackup.Database = databaseName;
//Declare a BackupDeviceItem
//Nombre de la Base de Datos.
BackupDeviceItem deviceItem = new BackupDeviceItem(string.Format("{0}BackUp_{1}_{2}.bak", destinationPath, databaseName, DateTime.Now.ToString("yyyyMMddHHmmss")), DeviceType.File);
////Define Server connection if diferent to LOCAL
//var connectionString = new SqlConnection(@"Data Source =.\sebas; Initial Catalog = PuroEscabio; Integrated Security = True");
//ServerConnection connection = new ServerConnection(connectionString);//To Avoid TimeOut Exception
Server sqlServer = new Server();
sqlServer.LoginMode = ServerLoginMode.Integrated;
sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
//Add the device to the Backup object.
sqlBackup.Devices.Add(deviceItem);
//Set the Incremental property to False to specify that this is a full database backup.
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
//Specify that the log must be truncated after the backup is complete.
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
//Run SqlBackup to perform the full database backup on the instance of SQL Server.
sqlBackup.SqlBackup(sqlServer);
//Remove the backup device from the Backup object.
sqlBackup.Devices.Remove(deviceItem);
return(true);
}
catch (Exception ex)
{
return(false);
}
}
// TODO: Abstract parameters into class.
public static void BackupDatabase(Server server, String databaseName, String destinationPath, int completionCallbackInterval, Action<int, String> completionCallback, Action<SqlError> errorCallback)
{
// TODO: Expose as parameter.
String backupSetName = String.Format("Backup_{0}", databaseName);
// TODO: Expose as parameter.
String backupSetDescription = "User-requested backup for Self-Service Tools";
// Define a Backup object variable.
var bk = new Backup
{
Action = BackupActionType.Database,
BackupSetDescription = backupSetDescription,
BackupSetName = backupSetName,
Database = databaseName,
Incremental = false, // This is a full database backup.
ExpirationDate = DateTime.UtcNow.AddYears(-1), // Already expired. Allows us to overwrite them easily with subsequent backups.
LogTruncation = BackupTruncateLogType.NoTruncate, // I'm not sure what the implications of truncating the log are, so don't do that.
CopyOnly = true,
PercentCompleteNotification = completionCallbackInterval,
Initialize = true,
};
// We're going to save this backup as a file.
String backupDeviceName = String.Format(destinationPath, databaseName);
var bdi = new BackupDeviceItem(backupDeviceName, DeviceType.File);
bk.Devices.Add(bdi);
//bk.Information += (sender, args) => { bk.Devices.Remove(bdi); errorCallback(args.Error); };
bk.PercentComplete += (sender, args) => { bk.Devices.Remove(bdi); completionCallback(args.Percent, args.Message); };
bk.SqlBackup(server);
}
public static string BackUpServerDatabase(Server server, string path)
{
try
{
var agencyName = "NoAgencyName";
var biosSn = "00000";
try
{
agencyName = Singleton.Agency.AgencyName;
biosSn = Singleton.ProductActivation.BiosSn;
agencyName = agencyName.Substring(0, agencyName.IndexOf(' '));
}
catch
{
}
var bkpDatabase = new Backup {
Action = BackupActionType.Database, Database = "PinnaFaceDbProd"
}; //
var bkpDevice =
new BackupDeviceItem(
path + "\\" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + agencyName + "_" + biosSn + ".bak",
DeviceType.File);
bkpDatabase.Devices.Add(bkpDevice);
bkpDatabase.SqlBackup(server);
return("");
}
catch (Exception x)
{
return(x.Message);
}
}
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;
}
}
// Backup database
#region BackupDatabase
/// <summary>
/// Backup database.
/// </summary>
/// <param name="serverName"></param>
/// <param name="databaseName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <returns>Name of the file.</returns>
protected static string BackupDatabase(string serverName, string databaseName, string userName, string password)
{
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.BackupSetName = "Archive";
backup.Database = databaseName;
string fileName = Guid.NewGuid().ToString();
BackupDeviceItem deviceItem = new BackupDeviceItem(fileName, DeviceType.File);
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
backup.Initialize = true;
backup.Checksum = true;
backup.ContinueAfterError = true;
backup.Devices.Add(deviceItem);
backup.Incremental = false;
backup.ExpirationDate = DateTime.Now.AddDays(3);
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.FormatMedia = false;
backup.SqlBackup(sqlServer);
return(fileName);
}
public static void PerformBackup(BackupInformation b)
{
string backupDeviceName = BaseBackupLocation +
b.Database +
DateTime.Now.Date.ToString("yyyyMMdd") +
".bak";
BackupDeviceItem bdi =
new BackupDeviceItem(backupDeviceName, DeviceType.File);
Backup bu = new Backup();
bu.Database = b.Database;
bu.Devices.Add(bdi);
bu.Initialize = true;
// add percent complete handler
// bu.PercentComplete += new PercentCompleteEventHandler(Backup_PercentComplete);
// add complete event handler
bu.Complete += new ServerMessageEventHandler(Backup_Complete);
Server server = new Server(b.Server);
bu.SqlBackup(server);
}
public bool Restore(string path)
{
Connect();
if (srvSql != null)
{
try
// If the user has chosen the file from which he wants the database to be restored
{
// Create a new database restore operation
Restore rstDatabase = new Restore();
// Set the restore type to a database restore
rstDatabase.Action = RestoreActionType.Database;
// Set the database that we want to perform the restore on
rstDatabase.Database = DBName;
BackupDeviceItem bkpDevice = new BackupDeviceItem(path, DeviceType.File);
// Add the backup device to the restore type
rstDatabase.Devices.Add(bkpDevice);
// If the database already exists, replace it
rstDatabase.ReplaceDatabase = true;
// Perform the restore
rstDatabase.SqlRestore(srvSql);
}
catch (Exception ex) {
TLog.WriteErr("TBackup.cs_retore ", ex.Message);
return(false);
}
}
else
{
TLog.WriteErr("TBackup.cs_retore ", "Not Connected to Server");
return(false);
}
return(true);
}
private void button1_Click(object sender, EventArgs e)
{
DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);
dataGridView2.DataSource = dataTable;
Server srv = new Server(@".\SQLEXPRESS");
if (srv == null)
{
Console.WriteLine(srv.Information.Version);
Database db = srv.Databases["AssociGestorDb"];
Backup bk = new Backup();
bk.Action = BackupActionType.Database;
bk.BackupSetDescription = "Full backup of AssociGestorDb";
bk.BackupSetName = "AssociGestorDb Backup";
bk.Database = "AssociGestorDb";
BackupDeviceItem bdi = new BackupDeviceItem(@"c:\temp\AssociGestorDb" + DateTime.Now.ToString("_yyyyMMdd"), DeviceType.File);
bk.Devices.Add(bdi);
bk.Incremental = false;
bk.LogTruncation = BackupTruncateLogType.Truncate;
bk.SqlBackup(srv);
}
else
{
MessageBox.Show("No server found.");
}
}
// Restore Database and trigger the stored procedure.
private void RestoreDatabase(String databaseName, String filePath, String serverName, String userName, String password)
{
//System.Diagnostics.Debugger.Launch(); //FOR DEBUGGING
String connectionString = "Server=" + serverName + "; DataBase=" + databaseName + ";Integrated Security=SSPI";
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;
db = sqlServer.Databases[databaseName];
sqlRestore.ReplaceDatabase = true;
// Restoration in process. The Database is Restored at a Default location. Under the DATA Folder.
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
// Trigger Stored Procedure after restore.
triggerSP(connectionString);
}
public void BackupDatabase(String databaseName, String userName, String password, String serverName, String destinationPath)
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database = databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
}
public string BackupSQL()
{
Server myServer = new Server(@"(local)");
myServer.ConnectionContext.LoginSecure = true;
myServer.ConnectionContext.Connect();
var db = new Database(myServer, "pokedex");
Backup buFull = new Backup();
buFull.Action = BackupActionType.Database;
buFull.Database = db.Name;
BackupDeviceItem BDI = new BackupDeviceItem();
BDI.DeviceType = DeviceType.File;
string dbTime = db.Name + "-" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day
+ DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second;
BDI.Name = @".\" + dbTime + ".bak";
buFull.Devices.Add(BDI);
buFull.BackupSetName = db.Name + " Backup";
buFull.BackupSetDescription = db.Name + " - Full Backup";
buFull.SqlBackup(myServer);
if (myServer.ConnectionContext.IsOpen)
{
myServer.ConnectionContext.Disconnect();
}
return(myServer.BackupDirectory);
}
private void BackupData(object sender, EventArgs e)
{
try
{
var connectionString = SqlHelper.GetConnectionString;
SqlHelper sqlHelper = new SqlHelper(connectionString);
ServerConnection serverConnection = sqlHelper.Username != null ? new ServerConnection(sqlHelper.ServerName, sqlHelper.Username, sqlHelper.Password) : new ServerConnection(sqlHelper.ServerName);
Server dbServer = new Server(serverConnection);
Backup dbBackup = new Backup
{
Action = BackupActionType.Database,
Database = sqlHelper.DatabaseName
};
BackupDeviceItem destination = new BackupDeviceItem(view.Path + (view.Path.EndsWith(@"\") ? "" : @"\") + "backup_" + DateTime.Now.ToString("dd.MM.yyyy") + ".bak", DeviceType.File);
dbBackup.Devices.Add(destination);
dbBackup.SqlBackup(dbServer);
serverConnection.Disconnect();
view.Path = "";
view.BackupMessage = "Succeed";
}
catch (Exception)
{
view.BackupMessage = "Failed";
}
}
private void RestoreData(object sender, EventArgs e)
{
try
{
var connectionString = SqlHelper.GetConnectionString;
SqlHelper sqlHelper = new SqlHelper(connectionString);
ServerConnection serverConnection = sqlHelper.Username != null ? new ServerConnection(sqlHelper.ServerName, sqlHelper.Username, sqlHelper.Password) : new ServerConnection(sqlHelper.ServerName);
Server dbServer = new Server(serverConnection);
Restore dbRestore = new Restore
{
Action = RestoreActionType.Database,
Database = sqlHelper.DatabaseName,
ReplaceDatabase = true,
NoRecovery = false
};
BackupDeviceItem destination = new BackupDeviceItem(view.Path, DeviceType.File);
dbRestore.Devices.Add(destination);
dbRestore.SqlRestore(dbServer);
serverConnection.Disconnect();
view.Path = "";
view.RestoreMessage = "Succeed";
}
catch (Exception)
{
view.RestoreMessage = "Failed";
}
}
private void BackupDb()
{
_dbName = ((Database)cmbBackupDb.SelectedItem).Name;
Backup dbBackup = new Backup();
try
{
dbBackup.Action = BackupActionType.Database;
dbBackup.Database = _dbName;
dbBackup.BackupSetName = $"{_dbName} backup set.";
dbBackup.BackupSetDescription = $"Database: {_dbName}:Date: {DateTime.Now:dd.MM.yyyy hh:mm}.";
dbBackup.MediaDescription = "Disk";
BackupDeviceItem device = new BackupDeviceItem(txtFileToBackUp.Text, DeviceType.File);
dbBackup.Devices.Add(device);
txtBackupScript.Text = dbBackup.Script(_sqlServer);
Logger.LogMessage("SQL Backup Script: " + txtBackupScript.Text);
progBar.Visible = true;
progBar.Value = 0;
dbBackup.Complete += dbBackup_Complete;
dbBackup.PercentCompleteNotification = 10;
dbBackup.PercentComplete += PercentComplete;
dbBackup.SqlBackup(_sqlServer);
}
catch (Exception exc)
{
dbBackup.Abort();
Logger.LogMessage($@"Exception occured. Message: {exc.Message}");
}
progBar.Visible = false;
}
private void btnBackup_Click(object sender, EventArgs e)
{
var servidor = txtServidor.Text;
var usuarioServidor = txtUsuarioServidor.Text;
var password = mskSenhaUsuarioServidor.Text;
var dataBaseModelo = cboBasesDados.SelectedValue != null?cboBasesDados.SelectedValue.ToString() : null;
if (ValidaCampos(servidor, usuarioServidor, "não necessita dessa informação", dataBaseModelo, "não necessita dessa informação"))
{
var server = new Server(servidor);
server.ConnectionContext.LoginSecure = false; // set to true for Windows Authentication
server.ConnectionContext.Login = usuarioServidor;
server.ConnectionContext.Password = password;
Console.WriteLine(server.Information.Version); // connection is established
var backup = new Backup();
string nomeArquivoBackup = string.Format("{0}_{1:yyyyMMdd_HHmmss}.bak", dataBaseModelo, DateTime.Now);
var bdi = new BackupDeviceItem(nomeArquivoBackup, DeviceType.File);
backup.Database = dataBaseModelo;
backup.Devices.Add(bdi);
backup.Incremental = false;
var backupdate = new DateTime();
backupdate = DateTime.Now;
backup.ExpirationDate = backupdate;
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.SqlBackup(server);
server.Refresh();
MessageBox.Show(string.Format("Backup '{0}' concluído com sucesso.", nomeArquivoBackup), "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private static void BackupDb(string dbName, string fileName)
{
Backup dbBackup = new Backup();
try
{
dbBackup.Action = BackupActionType.Database;
dbBackup.Database = dbName;
dbBackup.BackupSetName = string.Format("{0} backup set.", dbName);
dbBackup.BackupSetDescription = string.Format("Database: {0}. Date: {1}.",
dbName, DateTime.Now.ToString("dd.MM.yyyy hh:m"));
dbBackup.MediaDescription = "Disk";
BackupDeviceItem device = new BackupDeviceItem(fileName, DeviceType.File);
dbBackup.Devices.Add(device);
dbBackup.SqlBackup(sqlServer);
}
catch (Exception exc)
{
dbBackup.Abort();
MessageBox.Show(string.Format("Exception occurred.\nMessage: {0}", exc.Message));
}
finally
{
sqlConn.Close();
}
}
private void ImportRecords()
{
string conString = string.Empty; var dir = @"C:/Ajoor App Database file";
using (StreamReader reader = new StreamReader(connectionPath))
{
while (!reader.EndOfStream)
{
conString = reader.ReadLine();
}
}
SqlConnection con = new SqlConnection(conString);
string databaseName = "Ajo";
ServerConnection connection = new ServerConnection(con);
Server sqlServer = new Server(connection);
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
rstDatabase.Database = databaseName;
string backupfileName = $"{databaseName}.bak";
string backedUpFile = Path.Combine(dir, backupfileName);
BackupDeviceItem bkpDevice = new BackupDeviceItem(backedUpFile, DeviceType.File);
rstDatabase.Devices.Add(bkpDevice);
rstDatabase.ReplaceDatabase = true;
rstDatabase.SqlRestore(sqlServer);
}
/// <summary>
/// To Restote the database and return the string message
/// </summary>
/// <returns> return the string message</returns>
#region Restore Database
public string restoreDatabase(string path, string DatabaseName)
{
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database database = srv.Databases[DatabaseName];
Restore restoreBackUp = new Restore();
restoreBackUp.Action = RestoreActionType.Database;
restoreBackUp.Database = DatabaseName;
BackupDeviceItem source = new BackupDeviceItem(path, DeviceType.File);
restoreBackUp.Devices.Add(source);
restoreBackUp.ReplaceDatabase = true;
restoreBackUp.NoRecovery = false;
restoreBackUp.SqlRestore(srv);
SqlServerDisconnect();
return("Database Restore Sucessfull");
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return("Sorry Error While Restore DB");
}
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 void BackupDataBase()
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "BackUp of:" + "Max" + "on" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = txtBackupName.Text;
sqlBackup.Database = "Max";
txtBackupName.Text = txtBackupName.Text.Trim();
BackupDeviceItem deviceItem = new BackupDeviceItem(Server.MapPath("~/Backup/") + txtBackupName.Text + DateTime.Now.ToShortDateString() + ".bak", DeviceType.File);
ServerConnection connection1 = new ServerConnection(z.con);
Server sqlServer = new Server(connection1);
sqlServer.ConnectionContext.StatementTimeout = 60 * 60 * 60;
Database db = sqlServer.Databases["Max"];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
sqlBackup.Devices.Remove(deviceItem);
}
/// <summary>
/// 利用SMO备份数据库
/// </summary>
public static void BackupDatabase()
{
string databaseName = "msdb"; //备份的数据库名
string bkPath = @"C:\"; //存放备份后的数据的文件夹
//创建ServerConnection的实例
ServerConnection connection = new ServerConnection();
//指定连接字符串
connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";
//实例化Server
Server server = new Server(connection);
#region [创建数据库备份对象]
Backup backup = new Backup();
backup.Action = BackupActionType.Database;//完全备份
backup.Database = databaseName;
backup.BackupSetDescription = "Full backup of master";
backup.BackupSetName = "master Backup";
//创建备份设备
BackupDeviceItem bkDeviceItem = new BackupDeviceItem();
bkDeviceItem.DeviceType = DeviceType.File;
bkDeviceItem.Name = bkPath + databaseName + ".bak";
backup.Devices.Add(bkDeviceItem);
backup.Incremental = false;
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.SqlBackup(server);
#endregion
}
// TODO: Abstract parameters into class.
public static void BackupDatabase(Server server, String databaseName, String destinationPath, int completionCallbackInterval, Action <int, String> completionCallback, Action <SqlError> errorCallback)
{
// TODO: Expose as parameter.
String backupSetName = String.Format("Backup_{0}", databaseName);
// TODO: Expose as parameter.
String backupSetDescription = "User-requested backup for Self-Service Tools";
// Define a Backup object variable.
var bk = new Backup
{
Action = BackupActionType.Database,
BackupSetDescription = backupSetDescription,
BackupSetName = backupSetName,
Database = databaseName,
Incremental = false, // This is a full database backup.
ExpirationDate = DateTime.UtcNow.AddYears(-1), // Already expired. Allows us to overwrite them easily with subsequent backups.
LogTruncation = BackupTruncateLogType.NoTruncate, // I'm not sure what the implications of truncating the log are, so don't do that.
CopyOnly = true,
PercentCompleteNotification = completionCallbackInterval,
Initialize = true,
};
// We're going to save this backup as a file.
String backupDeviceName = String.Format(destinationPath, databaseName);
var bdi = new BackupDeviceItem(backupDeviceName, DeviceType.File);
bk.Devices.Add(bdi);
//bk.Information += (sender, args) => { bk.Devices.Remove(bdi); errorCallback(args.Error); };
bk.PercentComplete += (sender, args) => { bk.Devices.Remove(bdi); completionCallback(args.Percent, args.Message); };
bk.SqlBackup(server);
}
/// <summary>
/// This method restores the specified database from the backup file (.bak)
/// </summary>
public void RestoreDatabase()
{
string _extractedBakFile = String.Empty;
try
{
if (Server != null)
{
Restore _restoreDatabase = new Restore();
_restoreDatabase.Action = RestoreActionType.Database;
_restoreDatabase.Database = DbName;
//Extracts the zip file
if (FilePath.EndsWith(".zip"))
{
_extractedBakFile = ExtractZipFile(FilePath);
}
else
{
_extractedBakFile = FilePath;
}
if (String.IsNullOrEmpty(_extractedBakFile))
{
throw new InvalidArgumentException("File extraction failed. Please try again!");
}
BackupDeviceItem bkpDevice = new BackupDeviceItem(_extractedBakFile, DeviceType.File);
_restoreDatabase.Devices.Add(bkpDevice);
_restoreDatabase.ReplaceDatabase = true;
_restoreDatabase.PercentComplete += new PercentCompleteEventHandler(PercentComplete);
_restoreDatabase.SqlRestore(Server);
IsCompleted = true;
}
else
{
IsCompleted = false;
throw new InvalidArgumentException("A connection to the database server could not be established");
}
}
catch (Exception ex)
{
throw new InvalidOperationException("Database restore could not be successfully completed\n" + ex.Message);
}
finally
{
//check if the .bak is an extracted file. If so, delete .bak and leave the .zip file
string _zipFile = _extractedBakFile.Replace(".bak", ".zip");
if (!String.IsNullOrEmpty(_extractedBakFile) && File.Exists(_zipFile))
{
File.Delete(_extractedBakFile);
}
}
}
} // выбор каталога для сохранения архива
private void FolderArhiveBtn_Click(object sender, EventArgs e)
{
try
{
Server srv = new Server("DESKTOP-EPNEITS");
Database db = default(Database);
db = srv.Databases["Rating"];
int recoverymod;
recoverymod = (int)db.DatabaseOptions.RecoveryModel;
Backup bk = new Backup();
bk.Action = BackupActionType.Database;
bk.BackupSetDescription = "Full backup of Rating";
bk.BackupSetName = "Rating Backup";
bk.Database = "Rating";
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem("Rating " + DateTime.Now.ToString("yyyy-MM-dd") + " Backup", DeviceType.File);
bk.Devices.Add(bdi);
bk.Incremental = false;
System.DateTime backupdate = new System.DateTime();
backupdate = new System.DateTime(2018, 10, 5);
bk.ExpirationDate = backupdate;
bk.LogTruncation = BackupTruncateLogType.Truncate;
bk.SqlBackup(srv);
bk.Devices.Remove(bdi);
if (string.IsNullOrEmpty(FolderNameTextBox.Text))
{
MessageBox.Show("Выберете папку!", "Сообщение", MessageBoxButtons.OK, MessageBoxIcon.Information);
FolderNameTextBox.Focus();
return;
}
string pat = "BackupDB.zip";
string path = FolderNameTextBox.Text;
using (ZipFile zip = new ZipFile())
{
zip.CompressionLevel = Ionic.Zlib.CompressionLevel.BestCompression;
zip.AddDirectory(@"C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup");
zip.Save(path + " " + DateTime.Now.ToString("yyyy-MM-dd") + " " + pat);
}
MessageBox.Show("Архивирование данных прошло успешно!");
}
catch
{
MessageBox.Show("Во время архивации данных произошла ошибка, убедитесь в правильности указания каталога!");
}
} // архивация бекапа БД
private Restore GetSqlRestore(string file)
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(file, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
return(sqlRestore);
}
public bool BackupDataBase(Configuracoes config)
{
#region Declaração
if (config.SqlServer != null)
{
Backup bkpDataBase = null;
string data = DateTime.Now.Date.ToString("dd-mm-yyyy");
BackupDeviceItem bkpDeviceItem = null;
string fileName = string.Empty;
string path = string.Empty;
#endregion
#region Implementação
fileName = "Backup_" + data + ".bak";
bkpDataBase = new Backup();
bkpDeviceItem = new BackupDeviceItem();
bkpDataBase.Action = BackupActionType.Database;
bkpDataBase.Database = config.Database;
path = "C:\\Sistema\\Backup\\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = path + fileName.Replace(" ","_").Replace("/","_").Replace(":","_");
if (!File.Exists(path))
{
File.CreateText(path);
}
bkpDataBase.Incremental = false;
bkpDataBase.BackupSetName = fileName;
bkpDataBase.Checksum = true;
bkpDataBase.Devices.Add(new BackupDeviceItem(path, DeviceType.File));
bkpDataBase.SqlBackup(config.SqlServer);
return true;
}
else
return false;
#endregion
}
public static void BackupDatabase(string backUpFile)
{
ServerConnection con = new ServerConnection(Constants.DatabaseServerName);
Server server = new Server(con);
Backup source = new Backup();
source.Action = BackupActionType.Database;
source.Database = Constants.DatabaseName;
BackupDeviceItem destination = new BackupDeviceItem(backUpFile, DeviceType.File);
source.Devices.Add(destination);
source.SqlBackup(server);
con.Disconnect();
}
public static void BackupDatabase(string backUpFile)
{
ServerConnection con = new ServerConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\works\Work\.Net\Divan\Divan\bin\Debug\Database.mdf;Integrated Security=True;Connect Timeout=30");
Server server = new Server(con);
Backup source = new Backup();
source.Action = BackupActionType.Database;
source.Database = "divan";
BackupDeviceItem destination = new BackupDeviceItem(backUpFile, DeviceType.File);
source.Devices.Add(destination);
source.SqlBackup(server);
con.Disconnect();
}
public void Backup(string BackupPath)
{
BackupDeviceItem d = new BackupDeviceItem(BackupPath, DeviceType.File);
Backup b = new Backup() {
Database = to,
CompressionOption = BackupCompressionOptions.Default,
Initialize = true,
Action = BackupActionType.Database,
Incremental = false
};
b.Devices.Add(d);
b.SqlBackup(smoServer);
}
public static void BackupDatabase(string backUpFile)
{
ServerConnection con = new ServerConnection(@"YasarMalik-PC\SQLEXPRESS");
Server server = new Server(con);
Backup source = new Backup();
source.Action = BackupActionType.Database;
source.Database = "AQSMS";
BackupDeviceItem destination = new BackupDeviceItem(backUpFile, DeviceType.File);
source.Devices.Add(destination);
source.SqlBackup(server);
con.Disconnect();
}
static void Main4(string[] args)
{
string templateDbName = "MyTestDB";
string templateServer = "localhost";
Server server = new Server(templateServer);
Database templateDb = server.Databases[templateDbName];
//Backup backup = new Backup();
//backup.SqlBackup
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = templateDbName;
string backUpFilePath = @"D:\myDb20160504.bak";
BackupDeviceItem backupDeviceItem = new BackupDeviceItem(backUpFilePath, DeviceType.File);
backup.Devices.Add(backupDeviceItem);
backup.Initialize = true;
Console.WriteLine("begin back。。。");
String script = backup.Script(server);
backup.SqlBackup(server);
string sqlFilePath = @"D:\sqlScript_backUp.sql";
using (StreamWriter sw = new StreamWriter(sqlFilePath, true, Encoding.UTF8))
{
sw.WriteLine(script);
sw.WriteLine("GO");
}
//--------方式1-----------
//--------方式2-----------
Console.WriteLine("end。。。");
Console.ReadKey();
}
public static void backup()
{
string caminho = System.IO.Directory.GetCurrentDirectory();
caminho = caminho + @"\Backup";
if (!System.IO.Directory.Exists(caminho))
{
System.IO.Directory.CreateDirectory(caminho);
}
var path = caminho + @"\SGA-" + System.DateTime.Now.ToString("dd-MM-yyy") + @".bkp";
try
{
SqlConnection objConexao = new SqlConnection(ConfigurationManager.ConnectionStrings["sga"].ConnectionString);
ServerConnection objServers = new ServerConnection(objConexao);
Server objServer = new Server(objServers);
objServer.ConnectionContext.Connect();
string edition = objServer.Information.Edition;
Microsoft.SqlServer.Management.Smo.Backup objBackup = new Backup();
objBackup.Action = BackupActionType.Database;
objBackup.Database = "sga";
objBackup.MediaName = "FileSystem";
BackupDeviceItem objDevice = new BackupDeviceItem();
objDevice.DeviceType = DeviceType.File;
objDevice.Name = path;
objBackup.Checksum = true;
objBackup.Incremental = false;
objBackup.Devices.Add(objDevice);
objBackup.Initialize = true;
objBackup.SqlBackup(objServer);
objBackup.Devices.Remove(objDevice);
}
catch (Exception err)
{
throw new Exception(err.Message);
}
}
public bool BackUp(string path)
{
Connect();
// If there was a SQL connection created
if (srvSql != null)
{
try
{
// Create a new backup operation
Backup bkpDatabase = new Backup();
// Set the backup type to a database backup
bkpDatabase.Action = BackupActionType.Database;
// Set the database that we want to perform a backup on
bkpDatabase.Database = DBName;
// Set the backup device to a file
if (File.Exists(path))
{
int i = path.LastIndexOf('\\');
path = NameFile(path.Substring(0, i));
}
//làm gì thì tùy
BackupDeviceItem bkpDevice = new BackupDeviceItem(path+".bak", DeviceType.File);
// Add the backup device to the backup
bkpDatabase.Devices.Add(bkpDevice);
// Perform the backup
bkpDatabase.SqlBackup(srvSql);
}
catch (Exception ex)
{
TLog.WriteErr("TBackup.cs_bak ", ex.Message);
return false;
}
}
else
{
TLog.WriteErr("TBackup.cs_Backup ", "Not Connected to Server");
return false;
}
return true;
}
private void BackupDB(string databaseName, string serverName, string path)
{
try
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database = databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(path, DeviceType.File);
ServerConnection connection = new ServerConnection(serverName);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
//sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
MessageBox.Show(Constant.MESSAGE_SUCCESS_BACKUP,
Constant.CAPTION_CONFIRMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show(Constant.MESSAGE_ERROR_BACKUP + Constant.MESSAGE_NEW_LINE + Constant.MESSAGE_ERROR_BACKUP_PATH,
Constant.CAPTION_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public Boolean PerformRemoteBackup(String backupDir)
{
if (!System.IO.Directory.Exists(backupDir))
{
System.IO.Directory.CreateDirectory(backupDir);
}
Server sqlServer = Connect();
if (sqlServer != null)
{
DatabaseCollection dbc = sqlServer.Databases;
if (dbc.Contains(DbName))
{
Backup bkpDatabase = new Backup
{
Action = BackupActionType.Database,
Database = DbName,
Incremental = false,
Initialize = true,
};
BackupDeviceItem bkpDevice = new BackupDeviceItem(backupDir + "\\" + DbName + ".bak", DeviceType.File);
bkpDatabase.Devices.Add(bkpDevice);
// Perform the backup
bkpDatabase.SqlBackup(sqlServer);
//TODO retrieve backup file
if (System.IO.File.Exists(backupDir + "\\" + DbName + ".bak"))
{
return true;
}
//todo throw useful exception
return false;
}
//todo throw useful exception
return false;
}
//todo throw useful exception
return false;
}
/// <summary>
/// Backups the Database.
/// </summary>
/// <param name="sqlConnection">
/// The SQL connection of the database to be backup.
/// </param>
/// <param name="destinationPath">
/// The backup file path.
/// </param>
public static void BackupDatabase(SqlConnection sqlConnection, string destinationPath)
{
if (sqlConnection == null)
throw new ArgumentNullException("sqlConnection", "The sql connection cannot be null.");
if (string.IsNullOrWhiteSpace(destinationPath))
throw new ArgumentException("A valid back up file name is required.");
if (Path.IsPathRooted(destinationPath))
{
string directoryName = destinationPath.Substring(0, destinationPath.LastIndexOf('\\'));
if (!Directory.Exists(directoryName))
Directory.CreateDirectory(directoryName);
}
var serverConnection = new ServerConnection(sqlConnection);
var server = new Server(serverConnection);
var backup =
new Backup
{
Action = BackupActionType.Database,
BackupSetDescription = string.Format("ArchiveDataBase: {0}", DateTime.Now.ToShortDateString()),
BackupSetName = sqlConnection.Database,
Database = sqlConnection.Database,
Initialize = true,
Checksum = true,
ContinueAfterError = true,
Incremental = false
};
var deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
backup.Devices.Add(deviceItem);
backup.SqlBackup(server);
backup.Devices.Remove(deviceItem);
serverConnection.Disconnect();
}
public static void PerformBackup(BackupInformation b)
{
string backupDeviceName = BaseBackupLocation +
b.Database +
DateTime.Now.Date.ToString("yyyyMMdd") +
".bak";
BackupDeviceItem bdi =
new BackupDeviceItem(backupDeviceName, DeviceType.File);
Backup bu = new Backup();
bu.Database = b.Database;
bu.Devices.Add(bdi);
bu.Initialize = true;
// add percent complete handler
// bu.PercentComplete += new PercentCompleteEventHandler(Backup_PercentComplete);
// add complete event handler
bu.Complete += new ServerMessageEventHandler(Backup_Complete);
Server server = new Server(b.Server);
bu.SqlBackup(server);
}
public void BackupDatabase(SqlConnectionStringBuilder sqlConnection, string destinationPath)
{
ServerConnection serverConnection = null;
try
{
serverConnection = new ServerConnection(sqlConnection.DataSource, sqlConnection.UserID, sqlConnection.Password);
Server sqlServer = new Server(serverConnection);
Backup backupDatabase = new Backup()
{
Action = BackupActionType.Database,
Database = sqlConnection.InitialCatalog,
};
BackupDeviceItem backupItem = new BackupDeviceItem(destinationPath, DeviceType.File);
backupDatabase.Devices.Add(backupItem);
backupDatabase.SqlBackup(sqlServer);
}
finally
{
if (serverConnection != null && serverConnection.IsOpen)
{
serverConnection.Disconnect();
}
}
}
public bool RealizarBackupBD()
{
bool resultado = false;
try
{
string carpetaBackup = ConfigurationManager.AppSettings["CarpetaBackup"].ToString();
string baseDeDatos = ConfigurationManager.AppSettings["BaseDeDatos"].ToString();
if (!Directory.Exists(carpetaBackup))
{
Directory.CreateDirectory(carpetaBackup);
}
using (SqlConnection conexion = Conexion.ObtenerInstancia().CrearConexionSQL())
{
//string file = String.Format("{0}{1}-{2}.bak", carpetaBackup, baseDeDatos, DateTime.Now.ToString("yyyy-MM-dd"));
//string query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'", baseDeDatos, file);
//using (SqlCommand comando = conexion.CreateCommand())
//{
// comando.CommandText = query;
// conexion.Open();
// comando.ExecuteNonQuery();
//}
ServerConnection conexionServer = new ServerConnection(conexion);
Server servidor = new Server(conexionServer);
Backup backupBD = new Backup();
backupBD.Action = BackupActionType.Database;
backupBD.Database = baseDeDatos;
string archivoBackup = String.Format("{0}{1}-{2}.bak", carpetaBackup, baseDeDatos, DateTime.Now.ToString("yyyy-MM-dd"));
BackupDeviceItem deviceItem = new BackupDeviceItem(archivoBackup, DeviceType.File);
backupBD.Devices.Add(deviceItem);
backupBD.SqlBackup(servidor);
conexionServer.Disconnect();
resultado = true;
}
return resultado;
}
catch (AccesoBDException ex)
{
throw new DALException("BDDAO", "RealizarBackupBD", "AccesoBD", ex.Message, ex);
}
catch (SqlException ex)
{
throw new DALException("BDDAO", "RealizarBackupBD", "SQL", ex.Message, ex);
}
catch (Exception ex)
{
throw new DALException("BDDAO", "RealizarBackupBD", "General: " + ex.GetType().ToString(), ex.Message, ex);
}
}
private Restore GetSqlRestore(string file)
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(file, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
return sqlRestore;
}
private void Button_Click_2(object sender, RoutedEventArgs e)
{
Restore rest = new Restore();
string PathtobackUp ="";
OpenFileDialog dlg = new OpenFileDialog();
dlg.InitialDirectory = "c:\\";
dlg.Filter = "Backup Files (*.bak)|*.bak|All Files (*.*)|*.*";
dlg.RestoreDirectory = true;
if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
PathtobackUp = dlg.FileName.ToString();
}
else
{
return;
}
string fileName = PathtobackUp;
//this.Cursor = Cursors.WaitCursor;
//this.dataGridView1.DataSource = string.Empty;
try
{
rest.Devices.AddDevice(fileName, DeviceType.File);
Server srv = new Server("(localdb)\\v11.0");
bool verifySuccessful = rest.SqlVerify(srv);
string databaseName = "LoanManagement.Domain.finalContext";
if (verifySuccessful)
{
System.Windows.MessageBox.Show("Backup Verified!", "Info");
System.Windows.MessageBoxResult dr = System.Windows.MessageBox.Show("Do you want to restore?","Question",MessageBoxButton.YesNo);
if (dr == MessageBoxResult.Yes)
{
//fileName = dlg.FileName.Replace(Directory.GetCurrentDirectory(), "");
System.Windows.MessageBox.Show(fileName);
rest.Database = databaseName;
rest.Action = RestoreActionType.Database;
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(fileName, DeviceType.File);
rest.Devices.Add(bdi);
//rest.Devices.Add(bdi);
rest.ReplaceDatabase = true;
srv = new Server("(localdb)\\v11.0");
rest.SqlRestore(srv);
srv.Refresh();
System.Windows.MessageBox.Show("Restore of " + databaseName +" Complete!");
}
}
else
{
System.Windows.MessageBox.Show("ERROR: Backup not verified!", "Error");
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("The system has been successfully restored");
}
finally
{
//this.Cursor = Cursors.Default;
}
}
public bool RealizarRestoreBD(string rutaArchivo)
{
bool resultado = false;
try
{
string baseDeDatos = ConfigurationManager.AppSettings["BaseDeDatos"].ToString();
if (File.Exists(rutaArchivo))
{
using (SqlConnection conexion = Conexion.ObtenerInstancia().CrearConexionSQLMaster())
{
ServerConnection conexionServer = new ServerConnection(conexion);
Server servidor = new Server(conexionServer);
Restore restoreBD = new Restore();
restoreBD.Action = RestoreActionType.Database;
restoreBD.Database = baseDeDatos;
String query = "ALTER DATABASE " + baseDeDatos + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
using(SqlCommand comando = new SqlCommand(query, conexion))
{
comando.Connection.Open();
comando.ExecuteNonQuery();
}
BackupDeviceItem deviceItem = new BackupDeviceItem(rutaArchivo, DeviceType.File);
restoreBD.Devices.Add(deviceItem);
restoreBD.ReplaceDatabase = true;
restoreBD.SqlRestore(servidor);
conexionServer.Disconnect();
}
resultado = true;
}
return resultado;
}
catch (AccesoBDException ex)
{
throw new DALException("BDDAO", "RealizarRestoreBD", "AccesoBD", ex.Message, ex);
}
catch (SqlException ex)
{
throw new DALException("BDDAO", "RealizarRestoreBD", "SQL", ex.Message, ex);
}
catch (Exception ex)
{
throw new DALException("BDDAO", "RealizarRestoreBD", "General: " + ex.GetType().ToString(), ex.Message, ex);
}
}
private void btn_restore_Click(object sender, EventArgs e)
{
if (cmbDataBase.SelectedIndex.ToString().Equals(""))
{
MessageBox.Show("Please choose Database", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
// If there was a SQL connection created
if (_srvr != null)
{
try
{
btn_backup.Enabled = false;
btn_restore.Enabled = false;
db_change.Enabled = false;
server_change.Enabled = false;
Cursor = Cursors.WaitCursor;
// If the user has chosen the file from which he wants the database to be restored
// Create a new database restore operation
var rstDatabase = new Restore
{
Action = RestoreActionType.Database,
Database = cmbDataBase.SelectedItem.ToString()
};
// Set the restore type to a database restore
// Set the database that we want to perform the restore on
// Set the backup device from which we want to restore, to a file
var bkpDevice = new BackupDeviceItem(_dBpath + "\\Backup.bak", DeviceType.File);
// Add the backup device to the restore type
rstDatabase.Devices.Add(bkpDevice);
// If the database already exists, replace it
rstDatabase.ReplaceDatabase = true;
// Perform the restore
rstDatabase.SqlRestore(_srvr);
MessageBox.Show("Database " + cmbDataBase.Text + " succefully restored", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception)
{
MessageBox.Show("ERROR: An error ocurred while restoring the database", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor = Cursors.Arrow;
btn_backup.Enabled = true;
btn_restore.Enabled = true;
db_change.Enabled = true;
server_change.Enabled = true;
}
}
else
{
MessageBox.Show("ERROR: A connection to a SQL server was not established.", "Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
Cursor = Cursors.Arrow;
}
}
public static bool BackupDatabase(String databaseName, String destinationPath, SqlConnection connection)
{
bool flag = false;
try
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase:" +
DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database = databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
ServerConnection serverConn = new ServerConnection(connection);
Server sqlServer = new Server(serverConn);
//Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
sqlBackup.UnloadTapeAfter = true;
flag = true;
}
catch (Exception e)
{
throw e;
}
return flag;
}
//------------------------Back up/Restore---------------------------------------------------
public void BackupDatabase(string backUpFile)
{
ServerConnection serverConnection = new ServerConnection(@"ROHAN-PC\SQLEXPRESS");
Server myServer = new Server(serverConnection);
Backup source = new Backup();
source.Action = BackupActionType.Database;
source.Database = "RecordKeeper";
BackupDeviceItem destination = new BackupDeviceItem(backUpFile, DeviceType.File);
source.Devices.Add(destination);
source.SqlBackup(myServer);
serverConnection.Disconnect();
}
private void btn_backup_Click(object sender, EventArgs e)
{
if (cmbDataBase.SelectedIndex.ToString().Equals(""))
{
MessageBox.Show("Please choose Database", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (_srvr != null)
{
try
{
btn_backup.Enabled = false;
btn_restore.Enabled = false;
db_change.Enabled = false;
server_change.Enabled = false;
//Use this line if you have already created a bakup file.
File.Delete(_dBpath + "\\backup.bak");
Cursor = Cursors.WaitCursor;
// If the user has chosen a path where to save the backup file
// Create a new backup operation
var bkpDatabase = new Backup
{
Action = BackupActionType.Database,
Database = cmbDataBase.SelectedItem.ToString()
};
// Set the backup type to a database backup
// Set the database that we want to perform a backup on
// Set the backup device to a file
var bkpDevice = new BackupDeviceItem(_dBpath + "\\Backup.bak", DeviceType.File);
// Add the backup device to the backup
bkpDatabase.Devices.Add(bkpDevice);
// Perform the backup
bkpDatabase.SqlBackup(_srvr);
MessageBox.Show("Bakup of Database " + cmbDataBase.Text + " successfully created", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception x)
{
MessageBox.Show("ERROR: An error ocurred while backing up DataBase"+x, "Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor = Cursors.Arrow;
btn_backup.Enabled = true;
btn_restore.Enabled = true;
db_change.Enabled = true;
server_change.Enabled = true;
}
}
else
{
MessageBox.Show("ERROR: A connection to a SQL server was not established.", "Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
Cursor = Cursors.Arrow;
}
}
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(string backUpFile)
{
ServerConnection serverConnection = new ServerConnection(@"ROHAN-PC\SQLEXPRESS");
Server myServer = new Server(serverConnection);
Restore destination = new Restore();
destination.Action = RestoreActionType.Database;
destination.Database = "RecordKeeper";
BackupDeviceItem source = new BackupDeviceItem(backUpFile, DeviceType.File);
destination.Devices.Add(source);
destination.ReplaceDatabase = true;
destination.SqlRestore(myServer);
}
public void BackupDB(string backupDestinationFilePath)
{
try
{
string filePath = BuildBackupPathWithFilename(backupDestinationFilePath);
Console.WriteLine(filePath);
Console.WriteLine("Backup operation started");
Backup backup = new Backup();
//Set type of backup to be performed to database
backup.Action = BackupActionType.Database;
backup.BackupSetDescription = "BackupDataBase description";
//Set the name used to identify a particular backup set.
backup.BackupSetName = "Backup";
//specify the name of the database to back up
backup.Database = "TEST";
//Set up the backup device to use filesystem.
BackupDeviceItem deviceItem = new BackupDeviceItem(
backupDestinationFilePath,
DeviceType.File);
backup.Devices.Add(deviceItem);
// Setup a new connection to the data server
ServerConnection connection = new
ServerConnection(@"SERVER_NAME");
// Log in using SQL authentication
connection.LoginSecure = false;
connection.Login = "******";
connection.Password = "******";
Server sqlServer = new Server(connection);
//Initialize devices associated with a backup operation.
backup.Initialize = true;
backup.Checksum = true;
//Set it to true to have the process continue even
//after checksum error.
backup.ContinueAfterError = true;
//Set the backup expiry date.
backup.ExpirationDate = DateTime.Now.AddDays(3);
//truncate the database log as part of the backup operation.
backup.LogTruncation = BackupTruncateLogType.Truncate;
//start the back up operation
backup.SqlBackup(sqlServer);
Console.WriteLine("Backup operation succeeded");
}
catch (Exception ex)
{
Console.WriteLine("Backup operation failed");
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
