[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Cannot backup my database via code.

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
peterkiers
Asked:
peterkiers
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
peterkiersAuthor Commented:
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
 
Jacques Bourgeois (James Burger)Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
peterkiersAuthor Commented:
Sorry for the incomplete information. I use OleDB.
0
 
Jacques Bourgeois (James Burger)Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
OleDB is the way you are connecting . . . the question is what are you connecting to?
0
 
peterkiersAuthor Commented:
Access
0
 
Jacques Bourgeois (James Burger)Commented:
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
 
GrumpyTroll64Commented:
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
 
peterkiersAuthor Commented:
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
 
Jacques Bourgeois (James Burger)Commented:
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
 
peterkiersAuthor Commented:
Oke thank you all for the information/advice. Greetings, Peter Kiers
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now