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 akutbinayi wrote:
Please it is a bit urgent, Thanks...
Not even when the code is on fire. The code looks like it swallows all exceptions. You could try and change the
SqlBackupAsynctoSqlBackup; might be throwing something async :) --edit; The same question is in the database-forum; choose a forum and stick with it, cross-posting is annoying.Bastard Programmer from Hell :suss:
-
kutbinayi wrote:
Please it is a bit urgent, Thanks...
Not even when the code is on fire. The code looks like it swallows all exceptions. You could try and change the
SqlBackupAsynctoSqlBackup; might be throwing something async :) --edit; The same question is in the database-forum; choose a forum and stick with it, cross-posting is annoying.Bastard Programmer from Hell :suss:
Yes but when I use SqlBackup method then it nevers succeed backup operation. and catches an exception on data object System.Collections.ListDictionaryInternals. I think this object is used by SqlBackup method. If I use SqlBackupAsync then no exception is thrown. I catched all exceptions but it is not seen in the above code. I just use it while I was trying. Is it normal that SqlBackupAsync succeeds the backup operation but restoring the database does not affect the database ? What could be other reasons ?
-
Yes but when I use SqlBackup method then it nevers succeed backup operation. and catches an exception on data object System.Collections.ListDictionaryInternals. I think this object is used by SqlBackup method. If I use SqlBackupAsync then no exception is thrown. I catched all exceptions but it is not seen in the above code. I just use it while I was trying. Is it normal that SqlBackupAsync succeeds the backup operation but restoring the database does not affect the database ? What could be other reasons ?
kutbinayi wrote:
If I use SqlBackupAsync then no exception is thrown.
I bet it is, only on another thread. That would mean that there could have been an exception, without it being reported.
kutbinayi wrote:
Is it normal that SqlBackupAsync succeeds the backup operation but restoring the database does not affect the database ?
No, the software usually does as advertised.
kutbinayi wrote:
What could be other reasons ?
Some application open (editing the database) while you're doing a restore? That would lock the database and result in an abort. Can you paste the entire exception that you're getting?
Bastard Programmer from Hell :suss:
-
kutbinayi wrote:
If I use SqlBackupAsync then no exception is thrown.
I bet it is, only on another thread. That would mean that there could have been an exception, without it being reported.
kutbinayi wrote:
Is it normal that SqlBackupAsync succeeds the backup operation but restoring the database does not affect the database ?
No, the software usually does as advertised.
kutbinayi wrote:
What could be other reasons ?
Some application open (editing the database) while you're doing a restore? That would lock the database and result in an abort. Can you paste the entire exception that you're getting?
Bastard Programmer from Hell :suss:
The whole exception is this :
Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server 'EMRE-PC\SQLEXPRESS'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Cannot open backup device 'C:\Users\EMRE\Desktop\YEDEK.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.
konum: Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
konum: Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
--- İç özel durum yığını izlemesinin sonu ---
konum: Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
konum: Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
konum: Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
konum: Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
konum: Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
--- İç özel durum yığını izlemesinin sonu ---
konum: Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
konum: _15_49KADINIZLEM.DbConnection.BackUpDB(String DBpath) C:\Users\EMRE\documents\visual studio 2010\Projects\15-49KADINIZLEM\15-49KADINIZLEM\DbConnection.cs içinde: satır 107Backup failed for Server 'EMRE-PC\SQLEXPRESS'.
-
The whole exception is this :
Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server 'EMRE-PC\SQLEXPRESS'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Cannot open backup device 'C:\Users\EMRE\Desktop\YEDEK.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.
konum: Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
konum: Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
--- İç özel durum yığını izlemesinin sonu ---
konum: Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
konum: Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
konum: Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
konum: Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
konum: Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
--- İç özel durum yığını izlemesinin sonu ---
konum: Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
konum: _15_49KADINIZLEM.DbConnection.BackUpDB(String DBpath) C:\Users\EMRE\documents\visual studio 2010\Projects\15-49KADINIZLEM\15-49KADINIZLEM\DbConnection.cs içinde: satır 107Backup failed for Server 'EMRE-PC\SQLEXPRESS'.
Cannot open backup device 'C:\Users\EMRE\Desktop\YEDEK.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.There's your answer. Try a different path.
Bastard Programmer from Hell :suss:
-
Cannot open backup device 'C:\Users\EMRE\Desktop\YEDEK.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.There's your answer. Try a different path.
Bastard Programmer from Hell :suss:
-
what could be the reason of not accepting this file path ? Because there is no restriction to save file to my Desktop folder.
kutbinayi wrote:
what could be the reason of not accepting this file path ? Because there is no restriction to save file to my Desktop folder.
This is the place where you post whether a different path actually worked or not. Is there a readonly-file in that location?
Bastard Programmer from Hell :suss:
-
Cannot open backup device 'C:\Users\EMRE\Desktop\YEDEK.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.There's your answer. Try a different path.
Bastard Programmer from Hell :suss:
-
kutbinayi wrote:
Ok, but why this does not occur when I use SqlBackupAsync method ?
It does. It fails, throwing the exact same exception on another thread. Methods that end with "Async" are executed in a different thread, and you don't get the exceptions that they throw.
Bastard Programmer from Hell :suss:
-
kutbinayi wrote:
Ok, but why this does not occur when I use SqlBackupAsync method ?
It does. It fails, throwing the exact same exception on another thread. Methods that end with "Async" are executed in a different thread, and you don't get the exceptions that they throw.
Bastard Programmer from Hell :suss:
-
Ok, thank you for all of your replies. As a result using SqlBackup method instead of SqlBackupAsync is better solution for me. And if i solve the file path problem in my code, then everything seems to work great, right.