Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database - C# Discussion Boards

27-Sep-11 5:19

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 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 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;                

                // If specified database does not exist create it by using master database
                if (!srvr.Databases.Contains(Program.databaseName))
                {
                    try
                    {
                        DbConnection dbcon = new DbConnection("Data Source=" + Program.serverName + ";Initial Catalog=master;Integrated Security=True");
                        dbcon.ExecuteCommand("CREATE DATABASE "+Program.databaseName, null);
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("Veritabanı yaratılırken hata oluştu. Program yine de yedeğinizi geri yüklemeye çalışacak. Ancak bu değişiklik programınıza yansımayabilir. Bu durumda bizimle iletişime geçiniz.","Uyarı",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                    }
                }
                
                // Set the database that we want to perform the restore on
                rstDatabase.Database = Program.databaseName;
                
                rstDatabase.Devices.AddDevice(@DBpath, DeviceType.File);

                // If the database already exists, replace it
                rstDatabase.ReplaceDatabase = true;
                // Perform the restore
                rstDatabase.SqlRestoreAsync(srvr);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

I searched on this issue a lot and all of the codes seems correct however I could not understand why it does not perform correctly. Anyone can help me on this issue ? Please it is a bit urgent, Thanks...

Crosspost

Eddy Vluggen27-Sep-11 6:22

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 6:22

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 SqlBackupAsync to SqlBackup; might be throwing something async Smile | :)

--edit;
The same question is in the database-forum; choose a forum and stick with it, cross-posting is annoying.

Bastard Programmer from Hell Suspicious | :suss:

kutbinayi27-Sep-11 6:30

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 6:30

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 ?

Eddy Vluggen27-Sep-11 6:36

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 6:36

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 Suspicious | :suss:

kutbinayi27-Sep-11 6:41

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 6:41

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 107

Backup failed for Server 'EMRE-PC\SQLEXPRESS'.

Eddy Vluggen27-Sep-11 6:48

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 6:48

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 Suspicious | :suss:

kutbinayi27-Sep-11 7:53

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 7:53

what could be the reason of not accepting this file path ? Because there is no restriction to save file to my Desktop folder.

Eddy Vluggen27-Sep-11 8:19

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 8:19

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 Suspicious | :suss:

kutbinayi27-Sep-11 8:29

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 8:29

Ok, but why this does not occur when I use SqlBackupAsync method ?

Eddy Vluggen27-Sep-11 8:35

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 8:35

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 Suspicious | :suss:

kutbinayi27-Sep-11 8:48

Re: SQL SERVER 2008 Express Backup and Restore using SMO does not effect database

27-Sep-11 8:48

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.

Eddy Vluggen27-Sep-11 11:55

27-Sep-11 11:55

kutbinayi wrote:
Ok, thank you for all of your replies.

You're welcome Smile | :)

(Rich)TextBox problem!

OlivierPD27-Sep-11 4:07

27-Sep-11 4:07

Hello,

i have a strange problem with a TextBox.

I use a TextBox to show a ComPort Log. A communication Thread fires a datarecieved event and the Form with the Textbox handles this Event and uses Invoke to change the Content of the (multiline) TextBox.

void ComPortLog_PropertyChangedEvent() {
  MethodInvoker ComPortLogChanged = delegate {
    textBox2.Text = ComPortLog.Log; 
  };
  if (textBox2.InvokeRequired) Invoke(ComPortLogChanged);
  else ComPortLogChanged();
}

After a Timespan (0-30 Minutes) this stops Working? The string in ComPortLog.Log is correct and complete but the string in the Textbox.Text isnt...

somehow this isnt working anymore:

textBox2.Text = ComPortLog.Log;

The string in textBox2.Text is truncated to a value which differs from program run to programrun. Why? The Length of the ComPortLog.Log string is far below MaxLength Property of the textbox.

I tried to use a richTextBox instead, i tried to use the Lines Property instead of Text Property but nothing helps.

The Textbox refuses to show the complete ComPortLog.Log string after a while.

Any suggestions/ideas about this?

Luc Pattyn27-Sep-11 4:21

Luc Pattyn

27-Sep-11 4:21

The situation isn't quite clear, and a lot of things could go wrong. Here are some suggestions:

1.
as a test, forget about the GUI, just collect the serial port information and log it to a file, using File.AppendAllText. See if that fails at all.

2.
add try-catch constructs to all related code, and log all exceptions (their ToString value) in a separate file, again File.AppendAllText is the right method.

If you need further assistance, please provide more context, a complete description of your serial source, your serial port's code, and examples of the data when it fails.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

OlivierPD27-Sep-11 6:25

27-Sep-11 6:25

Hi,

The Program controls a spectrometer via serail port and triggers multiple measuremets and than collects and processes the results. And it works fine... except the textBox which does not show the ComPortLog in the Program correctly.

to 1: there is already a routine which saves the Log to a File and it also works fine. The textfile contains the complete log string.

to 2: already done... almost every possible exception is catched and written to the trace.

The serialport Communication isn't the problem (i think) because it works without problems, like the whole program. The measurement results are all valid...

I use an own ComPort Class and not the .Net SerialPort due to its unreliability.
If data is send or recieved an Event is fired

public event Action<string> OnSendReceive;

if (OnSendReceive != null)
  OnSendReceive(ComPort.PortName + ": Recieved: " + message);

if (OnSendReceive != null)
  OnSendReceive(ComPort.PortName + ": Send: " + command);

these Events are handled by a DeviceManager Class which then adds a Timestamp to the string and calls the writeline Method of a global instance (defined in MDI ParentForm) of the following class:

public class LogString {
        public delegate void changedEvent();
        public event changedEvent PropertyChanged;
        private StringBuilder log;
        public string Log { get { return log.ToString(); } }
        public int Length { get { return log.Length; } } 
        public string BackupFileName { get; set; }
        public LogString(string InitialString = null) {
            BackupFileName = null;
            log = new StringBuilder(InitialString);
        }
        public override string ToString() {
            return log.ToString();
        }
        public void Write(string txt) { 
            log.Append(txt);
            UpdateFile(); 
            FireEvent(); 
        }
        public void WriteLine(string txt) {  
            log.AppendLine(txt);  
            UpdateFile(); 
            FireEvent(); 
        }
        public void ClearLog() { log.Clear(); FireEvent(); }
        public bool SaveToFile(string FileName, FileMode FMode) {
            try {
                using (Stream Str = new FileStream(FileName, FMode))
                    using (TextWriter TW = new StreamWriter(Str))
                        TW.Write(this.log.ToString());
            } catch { return false; }
            return true;
        }
        private void UpdateFile() {
             if (BackupFileName != null) {
                using (Stream Str = new FileStream(BackupFileName, FileMode.Append))
                    using (TextWriter TW = new StreamWriter(Str))
                        TW.Write(message);
            }
        }
        private void FireEvent() {
            if (PropertyChanged != null) PropertyChanged();
        }
    }

So the Log is written to a File on the Disk an the PropertyChanged Event is fired.

there is a separate Form, which shows the Logs in TextBoxes:

public partial class LogWindow : Form {
        private Tools.StringTraceListener STL;
        private MainWindow parent;
        private MethodInvoker ComPortLogChanged, ProgramLogChanged;
        private string ComPortLog = string.Empty, ProgramLog = string.Empty;
        public LogWindow(Tools.StringTraceListener st, MainWindow p) {
            InitializeComponent();
            parent = p;
            STL = st;
            STL.PropertyChanged += new PropertyChangedEventHandler(STL_PropertyChanged);
            parent.ComPortLog.PropertyChanged += new Classes.Tools.LogString.changedEvent(ComPortLog_PropertyChangedEvent);
            this.CreateHandle();
            ComPortLogChanged = delegate {
                textBox2.Text = ComPortLog;
                textBox2.SelectionStart = textBox2.Text.Length;
                textBox2.ScrollToCaret();
            };
            ProgramLogChanged = delegate {
                textBox1.Text = ProgramLog;
                textBox1.SelectionStart = textBox1.Text.Length;
                textBox1.ScrollToCaret();
            };
        }
        void ComPortLog_PropertyChangedEvent() {
            ComPortLog = string.Empty;
            ComPortLog = parent.ComPortLog.Log;
            if (this.InvokeRequired) Invoke(ComPortLogChanged);
            else ComPortLogChanged();
        }
        void STL_PropertyChanged(object sender, PropertyChangedEventArgs e) {
            ProgramLog = string.Empty;
            ProgramLog = STL.Trace;
            if (this.InvokeRequired) Invoke(ProgramLogChanged);
            else ProgramLogChanged();
        }
}

This works fine for a while and the Log in the Textbox looks like this:

...
27.09.2011 18:12:37 COM3: Recieved: !01 000000 000071 000291 000106 000150
27.09.2011 18:12:37 COM3:     Send: #01c170
27.09.2011 18:12:39 COM3: Recieved: !01 000000 000170 000235 000202 000289
27.09.2011 18:12:39 COM3:     Send: #01c171
27.09.2011 18:12:41 COM3: Recieved: !01 000000 000171 000502 000202 000305
27.09.2011 18:12:41 COM3:     Send: #01c172
27.09.2011 18:12:44 COM3: Recieved: !01 000000 000172 000521 000203 000309
27.09.2011 18:12:44 COM3:     Send: #01c650
27.09.2011 18:12:46 COM3: Recieved: !01 000000 000650 000081 002057 002081
27.09.2011 18:12:46 COM3:     Send: #01c651
27.09.2011 18:12:48 COM3: Recieved: !01 000000 000651 000200 002049 002079
27.09.2011 18:12:48 COM3:     Send: #01c652
27.09.2011 18:12:50 COM3: Recieved: !01 000000 000652 000207 002046 002088
27.09.2011 18:12:51 COM3:     Send: #01c070
27.09.2011 18:12:54 COM3: Recieved: !01 000000 000070 000126 000105 000138
27.09.2011 18:12:54 COM3:     Send: #01c071
27.09.2011 18:12:56 COM3: Recieved: !01 000000 000071 000289 000105 000151
27.09.2011 18:12:56 COM3:     Send: #01c170
27.09.2011 18:12:58 COM3: Recieved: !01 000000 000170 000199 000201 000299
27.09.2011 18:12:58 COM3:     Send: #01c171
27.09.2011 18:13:01 COM3: Recieved: !01 000000 000171 000503 000201 000303
27.09.2011 18:13:01 COM3:     Send: #01c172
27.09.2011 18:13:03 COM3: Recieved: !01 000000 000172 000538 000201 000317
27.09.2011 18:13:03 COM3:     Send: #01c650
27.09.2011 18:13:05 COM3: Recieved: !01 000000 000650 000077 002045 002070
27.09.2011 18:13:05 COM3:     Send: #01c651
27.09.2011 18:13:07 COM3: Recieved: !01 000000 000651 000202 002044 002075
27.09.2011 18:13:08 COM3:     Send: #01c652
27.09.2011 18:13:10 COM3: Recieved: !01 000000 000652 000205 002049 002078
27.09.2011 18:13:11 COM3:     Send: #01c070
27.09.2011 18:13:13 COM3: Recieved: !01 000000 000070 000119 000106 000146
27.09.2011 18:13:13 COM3:     Send: #01c071
27.09.2011 18:13:15 COM3: Recieved: !01 000000 000071 000292 000106 000143
27.09.2011 18:13:15 COM3:     Send: #01c170
27.09.2011 18:13:18 COM3: Recieved: !01 000000 000170 000221 000202 000300
27.09.2011 18:13:18 COM3:     Send: #01c171

but after a couple of minutes (up to 30) it stops working, and the Text in the Textbox does not change anymore and seem truncated:

...
27.09.2011 18:14:56 COM3: Recieved: !01 000000 000171 000513 000204 000303
27.09.2011 18:14:56 COM3:     Send: #01c172
27.09.2011 18:14:58 COM3: Recieved: !01 000000 000172 000546 000204 000304
27.09.2011 18:14:59 COM3:     Send: #01c650
27.09.2011 18:15:01 COM3: Recieved: !01 000000 000650 000087 002070 002095
27.09.2011 18:15:01 COM3:     Send: #01c651
27.09.2011 18:15:03 COM3: Recieved: !01 000000 000651 000198 002071 002102
27.09.2011 18:15:03 COM3:     Send: #01c652
27.09.2011 18:15:05 COM3: Recieved: !01 000000 000652 000197 002071 002102
27.09.2011 18:15:06 COM3:     Send: #01c070
27.09.2011 18:15:08 COM3: Recieved: !01 000000 000070 000124 000107 000142
27.09.2011 18:15:09 COM3:     Send: #01c071
27.09.2011 18:15:11 COM3: Recieved: !01 000000 000071 000283 000107 000145
27.09.2011 18:15:11 COM3:     Send: #01c170
27.09.2011 18:15:13 COM3: Recieved: !01 000000 000170 000216 000204 000303
27.09.2011 18:15:13 COM3:     Send: #01c171
27.09.2011 18:15:15 COM3: Recieved: !01 000000 000171 000531 000203 000319
27.09.2011 18:15:15 COM3:     Send: #01c172
27.09.2011 18:15:18 COM3: Recieved: !01 000000 000172 000505 000203 000307
27.09.2011 18:15:18 COM3:     Send: #01c650
27.09.2011 18:15:20 COM3: Recieved: !01 000000 000650 000080 002070 002097
27.09.2011 18:15:20 COM3:     Send: #01c651
27.09.2011 18:15:22 COM3: Recieved: !01 000000 000651 000212 002071 002098
27.09.2011 18:15:22 COM3:     Send: #01c652
27.09.2011 18:15:25 COM3: Recieved: !01 000000 000652 000209 002071 002102
27.09.2011 18:15:26 COM3:     Send: #01c070
27.09.2011 18:15:28 COM3: Recieved: !01 000000 000070 000116 000107 000140
27.09.2011 18:15:28 COM3:     Send: #01c071
27.09.2011 18:15:30 COM3: Recieved: !01 000000 000071 000290 000107 000146
27.09.2011 18:15:30 COM3:     Send: #01c170
27.09.2011 18:15:32 COM3: Recieved: !01 000000 000170 000230 000203 000297
27.09.2011 18:15:32 COM3:     Send: #01c171
27.09.2011 18:15:35 COM3: Recieved: !01 000000 000171 00054

All the event are fired correctly, and the textbox flashes as it still trys to scroll to the end but Command

textBox2.Text = ComPortLog;

doesn't work correctly... the string ComPortLog contains the complete ComPort Log but the Text Property of the TextBox doesn't...
it is a complete Mystery for me...

Pete O'Hanlon27-Sep-11 6:27

Pete O'Hanlon

27-Sep-11 6:27

Rather than attempting to write a string that's getting bigger and bigger, why don't you simply append to a listbox? It would be far simpler.

Forgive your enemies - it messes with their heads

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

OlivierPD27-Sep-11 6:41

27-Sep-11 6:41

You are rigth, but handling a string with a Length of 100k or more isnt a Problem. I've put many megabytes of stings in textboxes without any problem (ok, it get slow eventually...)

btw: the Log gets flushed every hour, so the string does not become too big.

The thing is, I want to understand, whats going wrong here...

Luc Pattyn27-Sep-11 7:21

Luc Pattyn

27-Sep-11 7:21

OlivierPD wrote:
I use an own ComPort Class and not the .Net SerialPort due to its unreliability.

I never had any trouble with SerialPort except for one issue: it did not exist prior to .NET 2.0 so I needed my own class back then, which I dumped soon after SerialPort emerged.

OTOH I have had lots of trouble when putting lots of characters in a TextBox, so I stopped stuffing more than 10K long ago. For logging, I always use a ListBox, it is a natural fit.

If "All ... is correct" as you keep saying, then it can't go wrong, can it? And yet it does, so your logic is flawed; when the whole thing doesn't work as it should, then each and every part is suspect, you can't exclude some parts from scrutinizing.

I don't see a mystery, I see some "sub-optimal" design choices.

One thing that clearly changes when your TextBox.Text grows in size is timing: the amount of time the main thread is busy re-processing (mostly old) information may cause some problem in your code to emerge, e.g. a synchronization flaw between some of your threads or asynchronous actions; or too much delay in reacting to incoming serial data; or many other things.

Extensive logging (to a file and NOT to the GUI), timestamped with millisecond accuracy is likely to reveal what is really going on.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

BobJanova27-Sep-11 5:15

BobJanova

27-Sep-11 5:15

How long is the string when it stops working?

Is it simply truncating, or is it not updating at all? Try setting the text to DateTime.Now + ": " + ComPortLog.Log or something.

OlivierPD27-Sep-11 6:31