mysqlvb.netdatabase-connectiondatabase-backupsmysql-backup

How to copy .mdf and .ldf files from one location to another while the application is still open?


I have created an application that stores & query data using SQL Database. Everything is ok but just the problem is Backing it up, and after a lot of Searching I found two ways of Backup. First, using the command "use [Database Name] Backup database [Database Name] To Disk='[Backup Location]'. But problem with this method is it required SSM software to be be installed on the machine but I don't want it to be installed as it would allow the user to have more control then he need for that Database file, so if anybody can give me solution for this it would be really helpful. And the Second method is Copying the .mdf file and .ldf file, but problem is I cannot copy those files directly from the application or even indirectly if the application is opened and we can copy that only when we close the app but I don't want that and my main question focuses on this issue.

My Question. How can I copy these files from the default location to backup location using VB.Net coding directly from the application without closing it? Is there anyway to close the connection to these files?

Note: I tried Connection.Close() and Connection.Dispose()


Solution

  • Ok so before answering the question let me say something, most of us know and has seen at many websites that the Second Method (Copying the .mdf and .ldf file) is the least safe way and can cause terrible issue and the First Method (Using Transact-SQL for Backup/ Restore) is more preferable and convenient along with more safer method. But I guess many people (Especially beginners like me) would have problem with the query use [DatabaseName] Backup database [DatabaseName] To Disk='[Backup Location]' because the Database is consisted of two files (.mdf and .ldf) so how can this two files could be used with this code. And the solution I found is adding initial catalog = [DatabaseName] to my ConnectionString, check the following code:

    Dim connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + My.Settings.DBLocation + ";initial catalog=InvoiceDB;Integrated Security=True"
    
    Sub Backup_Click()
        With SaveFileDialogue2
            .Title = "Select Backup Location"
            .Filter = "BAK File Only|*.bak"
            .FileName = "Backup " + Format(Now.ToString("dd MMM yyyy"))
            If SaveFileDialogue2.ShowDialog = DialogResult.OK Then
                If .FileName <> "" Then
                    Dim BackupQuery As String
                    BackupQuery = "Backup database [DatabaseName] To Disk='" + .FileName + "'"
                    Using conn As New SqlConnection(connectionString)
                        Using comm As New SqlCommand()
                            With comm
                                .Connection = conn
                                .CommandType = CommandType.Text
                                .CommandText = BackupQuery
                            End With
                            Try
                                conn.Open()
                                comm.ExecuteNonQuery()
                                MsgBox("Backup Completed!", MsgBoxStyle.OkOnly, "Successful")
                                conn.Close()
                            Catch ex As SqlException
                                MessageBox.Show(ex.Message.ToString(), "Error Message")
                            Finally
                                conn.Dispose()
                            End Try
                        End Using
                    End Using
                End If
            End If
        End With
    
    End Sub
    

    and VOILA it worked! So if anyone else have the same problem you can try this one....

    Note: Change [DatabaseName] to your Database Name and remove "[ ]" too. And I added this answer to another question as they are similar so you can follow anyone. Click Here to go to the other question.