SQL SERVER 2008 Express Backup and Restore using SMO does not effect database
-
Hi, in my project I tried to backup my database and then restore using SMO. While backing up and restoring it gives no errors and catches no exception. All of them seem to work great. However, after I perform backup to a file, I deleted the data and/or a table from my database to see whether restoring operation will perform correctly or not. However, when I deleted a table or just data in the table, if I perform restore operation, neither the table nor the data in the table comes back. As a result either backup or restore operation does not work correctly. My backup and restore methods are as in the following :
public bool BackUpDB(string DBpath)
{
try
{
// Create a new connection to the selected server name
ServerConnection srvConn = new ServerConnection(Program.serverName);
// Log in using SQL authentication instead of Windows authentication
srvConn.LoginSecure = true;
// Create a new SQL Server object using the connection we created
srvr = new Server(srvConn);
// If the user has chosen a path where to save the backup file
// 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 = Program.databaseName;
//set incremental to false because this is full backup
bkpDatabase.Incremental = false;
bkpDatabase.Initialize = true;
//Specify that the log must be truncated after the backup is complete.
bkpDatabase.LogTruncation = BackupTruncateLogType.Truncate;
// Set the backup device to a file
BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath , DeviceType.File);
// Add the backup device to the backup
bkpDatabase.Devices.Add(bkpDevice);
// Perform the backup
bkpDatabase.SqlBackupAsync(srvr);
return true;
}
catch (Exception)
{
return false;
}
}The following is my restore method :
public bool RestoreDB(string DBpath)
{
try
{
// Create a ne -
Hi, in my project I tried to backup my database and then restore using SMO. While backing up and restoring it gives no errors and catches no exception. All of them seem to work great. However, after I perform backup to a file, I deleted the data and/or a table from my database to see whether restoring operation will perform correctly or not. However, when I deleted a table or just data in the table, if I perform restore operation, neither the table nor the data in the table comes back. As a result either backup or restore operation does not work correctly. My backup and restore methods are as in the following :
public bool BackUpDB(string DBpath)
{
try
{
// Create a new connection to the selected server name
ServerConnection srvConn = new ServerConnection(Program.serverName);
// Log in using SQL authentication instead of Windows authentication
srvConn.LoginSecure = true;
// Create a new SQL Server object using the connection we created
srvr = new Server(srvConn);
// If the user has chosen a path where to save the backup file
// 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 = Program.databaseName;
//set incremental to false because this is full backup
bkpDatabase.Incremental = false;
bkpDatabase.Initialize = true;
//Specify that the log must be truncated after the backup is complete.
bkpDatabase.LogTruncation = BackupTruncateLogType.Truncate;
// Set the backup device to a file
BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath , DeviceType.File);
// Add the backup device to the backup
bkpDatabase.Devices.Add(bkpDevice);
// Perform the backup
bkpDatabase.SqlBackupAsync(srvr);
return true;
}
catch (Exception)
{
return false;
}
}The following is my restore method :
public bool RestoreDB(string DBpath)
{
try
{
// Create a neJust a thought - I have not used SMO in many years but does the restore process drop all other connections to the database, I know if you try and restore via SSMS and there is an existing connection it fails, generates a warning but it does fail. Also the connection YOU are using may need to be to the master database not the one you are trying to restore! I love you exception, I wonder what it says :laugh:
Never underestimate the power of human stupidity RAH