Solved

Cannot backup my database via code.

Posted on 2014-01-24
12
189 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 39

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

Expert Comment

by:Kyle Abrahams
ID: 39807773
OleDB is the way you are connecting . . . the question is what are you connecting to?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now