DevShot - Database Snapshots for Developers

Imports Microsoft.SqlServer.Management.Smo

Namespace DevShot
    ''' <summary>
    ''' DBAdapter for backing up and restoring Microsoft SQL Server 2005 databases.
    ''' </summary>
    ''' <remarks></remarks>
    Public Class MSSQL2005Adapter
        Inherits DBAdapter

        Public Overrides Function Backup(Optional ByVal servername As String = ".\SQLEXPRESS") As Integer
            My.Application.Log.WriteEntry("Started backup with MSSQL2005Adapter")
            Me.State = States.BackupInProgress

            Dim server As New Microsoft.SqlServer.Management.Smo.Server(servername)

            ' Set connectionstring for connecting to server
            server.ConnectionContext.ConnectionString = Me.ConnectionString

            For Each db As Microsoft.SqlServer.Management.Smo.Database In server.Databases

                If UCase(db.Name) = UCase(Me.DatabaseName) Then ' Case insensitive
                    ' We found database with same name, let's back it up

                    My.Application.Log.WriteEntry("Found named database " & db.Name)

                    Try
                        My.Application.Log.WriteEntry("Preparing backup object")

                        Dim dBackup As New Backup

                        dBackup.Action = BackupActionType.Database
                        dBackup.Database = db.Name
                        Dim dBackupDevice As New BackupDeviceItem(Me.BackupFile & "_TEMP", DeviceType.File)
                        dBackup.BackupSetName = db.Name
                        dBackup.Devices.Add(dBackupDevice)
                        dBackup.Checksum = True
                        dBackup.ContinueAfterError = True
                        dBackup.Incremental = False
                        dBackup.LogTruncation = BackupTruncateLogType.Truncate

                        My.Application.Log.WriteEntry("Executing backup procedure")

                        dBackup.SqlBackup(server)
                    Catch ex As Exception
                        Throw New Exception("Could not backup data", ex)
                    Finally
                        If IO.File.Exists(Me.BackupFile & "_TEMP") Then
                            If IO.File.Exists(Me.BackupFile) Then
                                IO.File.Delete(Me.BackupFile)
                            End If

                            IO.File.Move(Me.BackupFile & "_TEMP", Me.BackupFile)
                        End If

                        My.Application.Log.WriteEntry("Ended backup with MSSQL2005Adapter")
                        Me.State = States.BackupCompleted
                    End Try
                End If
            Next

            ' Dispose server
            server.ConnectionContext.Disconnect()
            server = Nothing

        End Function

        Public Overrides Function Restore(Optional ByVal servername As String = ".\SQLEXPRESS") As Integer
            My.Application.Log.WriteEntry("Started restore with MSSQL2005Adapter")
            Me.State = States.RestoreInProgress

            Dim server As New Microsoft.SqlServer.Management.Smo.Server(servername)

            ' Set connectionstring for connecting to server
            server.ConnectionContext.ConnectionString = Me.ConnectionString

            For Each db As Microsoft.SqlServer.Management.Smo.Database In server.Databases
                If UCase(db.Name) = UCase(Me.DatabaseName) Then
                    My.Application.Log.WriteEntry("Found named database " & db.Name)

                    server.DetachDatabase(db.Name, True)

                    Try
                        If IO.File.Exists(Me.BackupFile) Then
                            Dim dRestore As New Restore
                            dRestore.ReplaceDatabase = True
                            dRestore.Action = RestoreActionType.Database
                            Dim d As New BackupDeviceItem(Me.BackupFile, DeviceType.File)
                            dRestore.Devices.Add(d)
                            dRestore.Database = db.Name
                            dRestore.SqlRestore(server)
                        Else
                            Throw New Exception("Could not locate backup file " & Me.BackupFile)
                        End If
                    Catch ex As Exception
                        Throw New Exception("Could not restore backup.", ex)
                    Finally
                        My.Application.Log.WriteEntry("Ended restore with MSSQL2005Adapter")
                        Me.State = States.RestoreCompleted

                    End Try

                    Exit For ' Can't continue since collection is modified
                End If
            Next

            ' Dispose server
            server.ConnectionContext.Disconnect()
            server = Nothing

        End Function
    End Class
End Namespace