Solved

Cannot backup my database via code.

Posted on 2014-01-24
12
192 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
 
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
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: 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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Achieve json result 2 65
Help with sorting data in Listbox using VB.NET 3 53
Tool Box 2 35
What do you call this line of code in this .NET Core page? 4 28
A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

22 Experts available now in Live!

Get 1:1 Help Now