Question Code to backup & restore

[Che7tzs]

pls help,... thanks..

 

[IanRyder]

Hi,

I am not aware of any .NET functions that have been created to backup databases and you therefore need to create your own backup and restore routines. I use the code below to backup and restore my SQL Server 2008 databases but I have no experience of MySQL so I am not sure if the below will work with MySQL. Maybe you can use this as a starting point with your database?

    My.Settings.DatabaseName = <Your Database Name>
 
    Public Shared Function BackupDatabase(strFilename As String ) As Boolean
      Private Shared sqlConnString As String= My.Settings.BionatureConnectionString
      Private Shared sqlConn As New SqlConnection(sqlConnString)
      Private Shared sqlCmnd As New SqlCommand
      Dim sqlString As String = "ALTER DATABASE " & My.Settings.DatabaseName & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;" & _
                                  "BACKUP DATABASE " & My.Settings.DatabaseName & " TO DISK = @strFilename;" & _
                                  "ALTER DATABASE " & My.Settings.DatabaseName & " SET MULTI_USER;"
      Dim bolResult As Integer
      Try
        'create your connection string and open it here
        sqlConn.Open()
        With sqlCmnd
          .Parameters.Clear()
          .Connection = sqlConn
          .CommandText = sqlString
          .Parameters.AddWithValue("@strFilename", strFilename)
        End With
        bolResult = sqlCmnd.ExecuteNonQuery()
        Return True
      Catch ex As Exception
        Throw ex
        Return False
      Finally
        If sqlConn.State = ConnectionState.Open Then
          sqlConn.Close()
        End If
      End Try
    End Function
 
    Public Shared Function RestoreDatabase(strFilename As String) As Boolean
      Private Shared sqlConnString As String= My.Settings.BionatureConnectionString
      Private Shared sqlConn As New SqlConnection(sqlConnString)
      Private Shared sqlCmnd As New SqlCommand
      Dim sqlString As String = "ALTER DATABASE " & My.Settings.DatabaseName & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;" & _
                                  "USE MASTER;" & _
                                  "RESTORE DATABASE " & My.Settings.DatabaseName & " FROM DISK = @strFilename;" & _
                                  "ALTER DATABASE " & My.Settings.DatabaseName & " SET MULTI_USER;"
      Dim bolResult As Integer
      Try
        'create your connection string and open it here
        sqlConn.Open()
        With sqlCmnd
          .Parameters.Clear()
          .Connection = sqlConn
          .CommandText = sqlString
          .Parameters.AddWithValue("@strFilename", strFilename)
        End With
        bolResult = sqlCmnd.ExecuteNonQuery()
        Return True
      Catch ex As Exception
        Throw ex
        Return False
      Finally
        If sqlConn.State = ConnectionState.Open Then
          sqlConn.Close()
        End If
      End Try
    End Function

Good Luck.

Cheers,

Ian

 

[Che7tzs]

Thanks ianRyder,,..

 

[Herman]

Keep in mind that ExecuteNonQuery is a blocking call, and a database backup might take a long time depending on database size and server recovery profile. It would be better to run the command asynchronously with Begin/EndExecuteNonQuery.

 

[IanRyder]

Very valid coment Herman. Thanks for that.

I typically run these routines through a backgroundworker which takes is out of the current thread to accomodate for the time it may take to complete the routines.

Cheers,

Ian.