I made a helper class to perform backups and restores for me.
public class SqlUtil
{
public delegate void StatusUpdatedDelegated(string message, int workDone, int totalWork);
public event StatusUpdatedDelegated StatusUpdated;
public delegate void OperationCompleteDelegated(SqlUtil me, Exception Error);
public event OperationCompleteDelegated OperationComplete;
public delegate void InfoDelegated(string message);
public event InfoDelegated Info;
Server _srv = default(Server);
BackupDeviceItem _bdi = default(BackupDeviceItem);
string _dbName = string.Empty;
string _sqlInstance = string.Empty;
public bool Connect(string userName, string password, string sqlInstance, string databaseName = "master")
{
bool result = false;
try
{
_dbName = databaseName;
_sqlInstance = sqlInstance;
_srv = new Server();
_srv.ConnectionContext.ServerInstance = sqlInstance;
_srv.ConnectionContext.LoginSecure = false;
_srv.ConnectionContext.Login = userName;
_srv.ConnectionContext.Password = password;
_srv.ConnectionContext.Connect();
SQLUtility.MyConnectionString = @"Data Source=" + sqlInstance + ";User Id=" + userName + ";Password=" + password + ";database=" + _dbName;
result = true;
}
catch
{
result = false;
}
return result;
}
public void Backup(string fileLocation)
{
if (_srv.Databases.Contains(_dbName))
{
_bdi = new BackupDeviceItem(fileLocation, DeviceType.File);
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase: " + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName =Path.GetFileName(fileLocation);
if (_srv.Configuration.DefaultBackupCompression.Maximum > 0)
{
UpdateInfo("Backing up using Compression");
sqlBackup.CompressionOption = BackupCompressionOptions.On;
}
else
UpdateInfo("SQL Instance doesn't support Compression");
sqlBackup.Database = _dbName;
Database db = _srv.Databases[_dbName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(_bdi);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddYears(999);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.Information += (sv, ev) =>
{
UpdateInfo(ev.Error.Message);
if (ev.Error.Message.Contains("BACKUP DATABASE WITH COMPRESSION is not supported"))
{
UpdateInfo("Attempting back up without compression");
sqlBackup.CompressionOption = BackupCompressionOptions.Off;
sqlBackup.SqlBackupAsync(_srv);
}
};
sqlBackup.PercentComplete += (svv, evv) =>
{
UpdateStatus("Backing up " + _dbName + " from " + _sqlInstance + " (" + evv.Percent + "%)", evv.Percent, 100);
};
sqlBackup.Complete += (sv, ev) =>
{
Complete(new Exception(ev.Error.Message));
};
sqlBackup.SqlBackupAsync(_srv);
}
else
{
Complete(new Exception("The Database " + _dbName + " doesn't exist on the SQL Instance"));
}
}
public bool DatabaseExists()
{
return _srv.Databases.Contains(_dbName);
}
public void Restore(string fileLocation, bool overwriteIfExists)
{
if (_srv.Databases.Contains(_dbName))
{
if (overwriteIfExists)
{
UpdateStatus("Droping " + _dbName + " in " + _sqlInstance, 0, 0);
_srv.Databases[_dbName].Drop();
UpdateStatus("Droping " + _dbName + " Complete", 0, 0);
}
else
{
Complete(new Exception("The database " + _dbName + " already exist. To replace this db check 'Overwrite if Exists'"));
return;
}
}
Restore rs = default(Restore);
_bdi = new BackupDeviceItem(fileLocation, DeviceType.File);
rs = new Restore();
rs.NoRecovery = false;
rs.Devices.Add(_bdi);
rs.Database = _dbName;
rs.ReplaceDatabase = overwriteIfExists;
rs.ContinueAfterError = true;
rs.Information += (sv, ev) =>
{
UpdateInfo(ev.Error.Message);
};
string path = Path.GetDirectoryName(fileLocation).TrimEnd('\\');
foreach (DataRow dr in rs.ReadFileList(_srv).Rows)
{
rs.RelocateFiles.Add(new RelocateFile(dr[0].ToString(), _srv.InstallDataDirectory + "\\DATA\\" + Path.GetFileName(dr[1].ToString())));
}
UpdateStatus("Restoring " + _dbName,0,0);
rs.PercentComplete += (svv, evv) =>
{
UpdateStatus("Restoring " + _dbName + " to " + _sqlInstance + " (" + evv.Percent + "%)", evv.Percent,100);
};
rs.Complete += (sv, ev) =>
{
Complete(new Exception(ev.Error.Message));
};
rs.SqlRestoreAsync(_srv);
}
public List<string> GetDatabases()
{
List<string> databases = new List<string>();
foreach (DataRow dr in SQLUtility.GetDataTable("sp_databases", commandType: CommandType.StoredProcedure).Rows)
{
databases.Add(dr["DATABASE_NAME"].ToString());
}
return databases;
}
private void UpdateStatus(string message, int workDone, int totalWork)
{
if (StatusUpdated != null)
{
StatusUpdated(message, workDone, totalWork);
}
}
private void UpdateInfo(string message)
{
if (Info != null)
{
Info(message);
}
}
private void Complete(Exception Error)
{
if (OperationComplete != null)
OperationComplete(this, Error);
}
}
Hope it helps you.