Solved

Cannot backup my database via code.

Posted on 2014-01-24
12
193 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 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 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

12 Experts available now in Live!

Get 1:1 Help Now