Restore MS SQL Server database from within your application(s)
Introduction
This post is a follow-up of my previous post; Backup MS SQL Server database from within your application(s). It presents a simple way in which you could incorporate a restore tool from with in your application.
Simply download the source code with the restore form that you need to just add into your application
Key Methods include
LoadServers; this method loads all local servers
''' <summary>
''' Loads local servers
''' </summary>
''' <remarks></remarks>
Private Sub LoadServers()
Try
Me.Cursor = Cursors.WaitCursor
Dim servers As New DataTable()
servers = SmoApplication.EnumAvailableSqlServers(True)
Me.cboServerName.Items.Clear()
For Each server As DataRow In servers.Rows
Me.cboServerName.Items.Add(server.Item("Name"))
Next
Catch ex As Exception
Return
Finally
Me.Cursor = Cursors.Default
End Try
End Sub
LoadDataFiles; this method first checks the validity of the device, and then loads data and log files from the backup device
''' <summary>
''' loads data and log files
''' </summary>
''' <param name="fileName"></param>
''' <remarks></remarks>
Private Sub LoadDataFiles(ByVal fileName As String)
Dim restoreServer As New Server()
Dim dataRestore As New Restore()
Dim data As New DataTable()
Dim valid As Boolean
Try
Me.Cursor = Cursors.WaitCursor
Me.dgvRestore.Rows.Clear()
dataRestore.Devices.AddDevice(fileName, DeviceType.File)
valid = dataRestore.SqlVerify(restoreServer)
If valid Then
data = dataRestore.ReadFileList(restoreServer)
If data Is Nothing OrElse data.Rows.Count < 1 Then Return
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For pos As Integer = 0 To data.Rows.Count - 1
' Ensure that you add a new row
Me.dgvRestore.Rows.Add()
Me.dgvRestore.Item(Me.colLogicalName.Name, pos).Value = data.Rows(pos).Item("LogicalName")
Me.dgvRestore.Item(Me.colPhysicalName.Name, pos).Value = data.Rows(pos).Item("PhysicalName")
Me.dgvRestore.Item(Me.colSize.Name, pos).Value = data.Rows(pos).Item("Size")
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Me.btnRestore.Enabled = True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Else
Me.ClearControls()
Throw New ArgumentException("Invalid backup file!")
End If
Catch exSMO As SmoException
Throw exSMO
Catch ex As Exception
Throw ex
Finally
Me.Cursor = Cursors.Default
End Try
End Sub
Restore button performs the restoration process
Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
Try
Cursor.Current = Cursors.WaitCursor
Dim dataLogicalName As String
Dim logLogicalName As String
Dim dataPhysicalName As String
Dim logPhysicalName As String
Dim serverName As String = Me.cboServerName.Text.ToString()
Dim dataBaseName As String = Me.txtDataBaseName.Text.ToString()
Dim fileName As String = Me.txtBackupFile.Text.ToString()
If String.IsNullOrEmpty(serverName) Then Throw New ArgumentException("Enter server name!")
If String.IsNullOrEmpty(dataBaseName) Then Throw New ArgumentException("Enter data base name!")
If String.IsNullOrEmpty(fileName) Then Throw New ArgumentException("Load backup file!")
Dim restoreServer As New Server(serverName)
Dim dataRestore As New Restore()
dataRestore.Action = RestoreActionType.Database
dataRestore.Database = dataBaseName
''set the devices: file, tape etc
dataRestore.Devices.AddDevice(fileName, DeviceType.File)
''progress meter stuff
Me.proRestoreDatabase.Minimum = 0
Me.proRestoreDatabase.Value = 10
Me.proRestoreDatabase.Maximum = 100
''this gives the % complete by handling the event
''provided by SMO on the percent complete; we will
''update the progress meter in the event handler
''set the progress meter to 10% by default
dataRestore.PercentCompleteNotification = 10
dataRestore.ReplaceDatabase = Me.chkOverwriteDatabase.Checked
AddHandler dataRestore.PercentComplete, AddressOf Me.RestoreProgressEventHandler
''use below to relocate ths mdf files
If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 0).Value)) Then
dataLogicalName = String.Empty
Else : dataLogicalName = CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 0).Value)
End If
If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 0).Value)) Then
dataPhysicalName = String.Empty
Else : dataPhysicalName = CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 0).Value)
End If
If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 1).Value)) Then
logLogicalName = String.Empty
Else : logLogicalName = CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 1).Value)
End If
If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 1).Value)) Then
logPhysicalName = String.Empty
Else : logPhysicalName = CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 1).Value)
End If
With dataRestore.RelocateFiles
.Add(New RelocateFile(dataLogicalName, dataPhysicalName))
.Add(New RelocateFile(logLogicalName, logPhysicalName))
End With
dataRestore.SqlRestore(restoreServer)
MessageBox.Show("Restore completed successfully!")
Catch exSMO As SmoException
Me.proRestoreDatabase.Value = 0
MessageBox.Show(exSMO.Message)
Catch ex As Exception
Me.proRestoreDatabase.Value = 0
MessageBox.Show(ex.Message)
Finally
Me.proRestoreDatabase.Value = 0
Cursor.Current = Cursors.Default
End Try
End Sub
Private Sub RestoreProgressEventHandler(ByVal sender As System.Object, ByVal e As PercentCompleteEventArgs)
''increase the progress bar up by the percent
Me.proRestoreDatabase.Value = e.Percent
End Sub
Download the source code RestoreDatabase.zip attached below
Wilson Kutegeka | Microsoft MVP - VB
Developer | Promoter | ClinicMaster Software
Cel: +256 772 609113 | Web: www.clinicmaster.net