Solved

Cannot backup my database via code.

Posted on 2014-01-24
12
199 Views
Last Modified: 2014-01-24
Hi,

I have found this methode below but it does not work. What do I have to do to specify at line where I have OleDbCommand declared?. And as you can see the connection closes then reopens and then closes again. Is this right? Because my form which has a grid on it has a connection to a database. And save the data of the grid to the database when the form closes. Does anyone know how to fix this or have a better example.

        private void bbBackup_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.DBRootConnectionString);
            SaveFileDialog saveFileDlg = new SaveFileDialog(); 
            saveFileDlg.FileName = "DBRoot.bak"; 
            saveFileDlg.Filter = "Backup File (*.bak)|*.bak"; 
            try
            {
                saveFileDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                if (saveFileDlg.ShowDialog() == DialogResult.OK)
                {
                    OleDbCommand cmd = new OleDbCommand(saveFileDlg.FileName, conn);
                    conn.Close();
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Backup Successful!", "CPS Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
            }
        }

Open in new window


Greetings,

Peter Kiers
0
Comment
Question by:peterkiers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 150 total points
ID: 39807720
Are you trying to backup the entire database?  If so this is not the way to do it.

Since you're instatiating a new object there's no reason to close the connection first.  It's not recommeneded that you run the backup from within your code though, that's usually done through a scheduled plan of some kind (typically sql server agent).  You could make the .bak file available after the backup, but that would be something different.

Please explain what you're intending to do.
0
 
LVL 1

Author Comment

by:peterkiers
ID: 39807749
Yes, I am trying to backup an entire database. On my form I have a several components and a button. The components are all connected to the tables of the database. And the data of the components will be saved to the tables when the form is closed. I want to make a button for the user to backup the entire database.

Peter
0
 
LVL 40
ID: 39807756
What you put there is very dependant on the database you use. And it does not do much, only provide a connection to the database. What is important is the cmd object, that defines an SQL command that will create the backup, something that can vary from database to database. My hint is that if he knew what he was doing, the programmer needed a backup out of the conventions, where only part of the information was backed up, and he built a custom way to do backup.

So solution might be useless to you. You do not specify which database you use your OleDBConnection. Is it Access, SQL Server, something else?

With Access, you usually do not backup through OleDB instructions, you simply copy the .mdb or .accdb file through IO.File.Copy, one line of code.

With SQL Server, it is always better to use SqlClient instead of OleBD instructions. SQLClient is built specifically for SQL Server and offers a little more possibilites, and more performance than OleDB that is designed as a generic library.

And for a backup, there is already a tool provided with SQL Server that enables you to peform backups, so it might be the best thing to use. It's called osql.exe and is located by default in C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ (110 might vary depending on your version of SQL Server). It can be used with code similar to the following (JBCuisine is the name of the database, JBFI.Server is the name of the server):

		Const BackupLocation As String = "F:\Backups\JBCuisine.bak"
		If System.IO.File.Exists(BackupLocation) Then
			System.IO.File.Delete(BackupLocation)
		End If
		Dim info As New ProcessStartInfo("C:\Program Files\Microsoft SQL Server\110\Tools\Binn\osql.exe", "-S """ & JBFI.Server & """ -E -Q ""BACKUP DATABASE JBCuisine TO DISK='" & BackupLocation & "'""")
		info.UserName = ""
		Dim password As New Security.SecureString()
		info.Password = password
		info.UseShellExecute = False
		info.CreateNoWindow = True
		Process.Start(info)

Open in new window


Note that you might encounter problems depending on how SQL Server is configured. The user who triggers the backup must have backup rights enabled in the database, and it might be required that he is administrator to run that code.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:peterkiers
ID: 39807765
Sorry for the incomplete information. I use OleDB.
0
 
LVL 40
ID: 39807772
OleDB is not a database, it is a Library that can access almost any database. It uses standards that as common to most databases. But the way to backup a database is not standard and can vary from one database to another.

What we need to know is the database that you use: Access, SQL Server, Oracle, MySQL...
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39807773
OleDB is the way you are connecting . . . the question is what are you connecting to?
0
 
LVL 1

Author Comment

by:peterkiers
ID: 39807776
Access
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 350 total points
ID: 39807894
Then, a simple file copy, with an error trap in case the database is in a situation where the database cannot be copied.

		Try
			IO.File.Copy("YourPath/YourFile.mdb", "YourBackupPath/YouBackupFile.mdb")
		Catch ex As Exception
			MessageBox.Show("Database in use, try later.")
		End Try

Open in new window


The error trapping could be more elaborate, but that can be sufficient in many simple applications.
0
 

Expert Comment

by:GrumpyTroll64
ID: 39807898
Because Access is a file-based database, you could simply copy the .mdb (or .accdb) file to create a backup. It's much simpler to create back-up copies of a file-based database than if you were trying to create back-ups of a server-based database.
0
 
LVL 1

Author Comment

by:peterkiers
ID: 39807918
Oke, thanks but I have just one question. I have a grid on my form connected to the database. And a button for backup my db. When I change some data in the grid and then press the button to create the backup are the changed that I made in the grid also in the backup. Because I have this in the close-event to save the changes to the database when the form closes:

            glucsTA.Update(glucsDS);
            glucsDS.AcceptChanges();

GlucsTA=TableAdapter
GlucsDS=Dataset
And I have also a glucsBS (BindingSource)

Do I have to save the data from the grid first in order to make a file cop?

Peter
0
 
LVL 40
ID: 39807948
No, the change made in the grid are not yet in the database if you click on the button.

You can however copy the code that updates the database in the button so that the update is done before the copy.

Or you can disregard the button and automatically copy the file at the end of the closing event.
0
 
LVL 1

Author Comment

by:peterkiers
ID: 39807950
Oke thank you all for the information/advice. Greetings, Peter Kiers
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question