Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Backup from SSMS or from .NET
Message
De
29/04/2016 15:53:32
 
 
À
Tous
Information générale
Forum:
ASP.NET
Catégorie:
Bases de données
Titre:
Backup from SSMS or from .NET
Versions des environnements
SQL Server:
SQL Server 2014
OS:
Windows 8.1
Network:
Windows 2008 Server
Divers
Thread ID:
01635667
Message ID:
01635667
Vues:
102
When I backup the database directly in SSMS, I have it in 12 minutes. When I use .NET, it takes 2.5 hours.

The code below is the backup class I am using. Anyone could tell me if this is really normal it is that slow from .NET?
Public Class BackupDatabase

    Public cDatabase As String = ""
    Public cDatabaseBackupSynchronizationDirectory As String = ""
    Public cPath As String = ""
    Public cWinZipDirectory As String = "C:\Program Files\WinZip"
    Public cWinZipEXE As String = "WinZip32.exe"
    Public lCompression As Boolean = True
    Public lDatabaseBackupSynchronization As Boolean = False
    Public lDatabaseShrink As Boolean = False
    Public lDatabaseShrinkLog As Boolean = False
    Public lInit As Boolean = True
    Public lSequence As Boolean = False
    Public lWinZip As Boolean = False
    Public lZip As Boolean = False
    Public oOriginator As Robot = Nothing
    Public oStatus As Object = Nothing
    Private nNoLanguage As Integer = 0
    Private oApp As App = Nothing
    Private oProcess As LXProcess = Nothing

    ' This is when we access the class in a desktop mode
    Sub New(ByVal toApplication As App)
		oApp = toApplication
		nNoLanguage = oApp.nLanguage
	End Sub

    ' This is when we access the class in a Web mode
    Sub New(ByVal toProcess As LXProcess)
		oProcess = toProcess
		oApp = oProcess.oApp
		nNoLanguage = oProcess.nLanguage
	End Sub

	Public Function Backup() As Boolean
        Dim lcBackingUpDatabase As String = ""
        Dim lcScript As String = ""
        Dim lcSequence As String = ""
        Dim lcZippingTheBackup As String = ""
        Dim lnSequence As Integer = 0
        Dim loBackup As Microsoft.SqlServer.Management.Smo.Backup = New Microsoft.SqlServer.Management.Smo.Backup
        Dim loData As Data = New Data(oApp)
        Dim loFileFunction As FileFunction = New FileFunction(oApp)
        Dim loServer As Microsoft.SqlServer.Management.Smo.Server = New Microsoft.SqlServer.Management.Smo.Server
        Dim loZip As Zip = Nothing

        ' Get the proper definition as per the current scope
        If oProcess Is Nothing Then
            loZip = New Zip(oApp)
        Else
            loZip = New Zip(oProcess)
        End If

        ' Based on the language
        Select Case nNoLanguage

            ' English
            Case 1
                lcBackingUpDatabase = "Backing up database"
                lcZippingTheBackup = "Zipping the backup"

                ' French
            Case 2
                lcBackingUpDatabase = "Prise de copie de la base de données"
                lcZippingTheBackup = "Compression de la prise de copie de la base de données"

                ' Spanish
            Case 3
                lcBackingUpDatabase = "Backing up database"
                lcZippingTheBackup = "Zipping the backup"

                ' Portuguese
            Case 4
                lcBackingUpDatabase = "Backing up database"
                lcZippingTheBackup = "Zipping the backup"

        End Select

        ' If we shrink the database
        If lDatabaseShrink Then

            oOriginator.AddStatus("Shrinking database...")

            ' Create the fields
            lcScript = lcScript + "DBCC ShrinkFile (" + cDatabase + ",1)" + oApp.cCR

            If Not loData.ExecuteSQLScript(lcScript) Then
                Return False
            End If

        End If

        ' If we shrink the database log
        If lDatabaseShrinkLog Then

            oOriginator.AddStatus("Shrinking database log...")

            ' Create the fields
            lcScript = lcScript + "DBCC ShrinkFile (" + cDatabase + "_Log,1)" + oApp.cCR

            If Not loData.ExecuteSQLScript(lcScript) Then
                Return False
            End If

        End If

        oOriginator.AddStatus(lcBackingUpDatabase + "...")

        loBackup.Devices.AddDevice(cPath + "\" + cDatabase + ".bak", Microsoft.SqlServer.Management.Smo.DeviceType.File)
        loBackup.Database = cDatabase
        loBackup.Action = Microsoft.SqlServer.Management.Smo.BackupActionType.Database
        loBackup.Initialize = True
        loBackup.PercentCompleteNotification = 1

        ' If we use the compression
        If lCompression Then
            loBackup.CompressionOption = Microsoft.SqlServer.Management.Smo.BackupCompressionOptions.On
        End If

        ' If we create a new archive
        If lInit Then
            loBackup.Initialize = True
        End If

        AddHandler loBackup.PercentComplete, AddressOf ProgressEventHandler

        loBackup.SqlBackup(loServer)

        ' If we have to zip
        If lZip Then
            oOriginator.AddStatus(lcZippingTheBackup + "...")

            ' Zip the .bak file so it would be ready to be downloaded
            loZip.cSourceFolder = cPath
            loZip.AddFile(cDatabase + ".bak")
            loZip.cDestinationFile = cPath + "\" + cDatabase + ".zip"
            loZip.cWinZipDirectory = cWinZipDirectory
            loZip.cWinZipEXE = cWinZipEXE

            ' If we use WinZip
            If lWinZip Then
                loZip.cSourceFolder = cPath + "\" + cDatabase + ".bak"
            End If

            If Not loZip.Zip() Then

                ' Get the proper definition as per the current scope
                If oProcess Is Nothing Then
                    oApp.ErrorSetup(, loZip.cMessage)
                Else
                    oProcess.ErrorSetup(, loZip.cMessage)
                End If

            End If

        End If

        ' If we synchronize the backup to another server
        If lDatabaseBackupSynchronization Then

            oOriginator.AddStatus("Copying " + cDatabase + ".bak...")

            ' If we use a sequence
            If lSequence Then

                ' Just so we can have at least two backups at the same time
                lnSequence = Date.Now.DayOfWeek Mod 2

                lcSequence = lnSequence.ToString
            End If

            ' Zip class needs an extension, otherwise, it will try to zip a file name ending with .zip
            loFileFunction.cSource = cPath + "\" + cDatabase + ".bak"
            loFileFunction.cDestination = cDatabaseBackupSynchronizationDirectory + "\" + cDatabase + lcSequence + ".bak"
            If loFileFunction.CopyFile() Then
            End If

        End If

        Return True
    End Function

    Private Sub ProgressEventHandler(ByVal sender As Object, ByVal e As Microsoft.SqlServer.Management.Smo.PercentCompleteEventArgs)
        oOriginator.AddStatus("Backing up database: " + e.Percent.ToString + "% completed...")
        System.Windows.Forms.Application.DoEvents()
    End Sub

    Public Function Restore() As Boolean
        Dim lcRestoringDatabase As String = ""
        Dim loRestore As Microsoft.SqlServer.Management.Smo.Restore = New Microsoft.SqlServer.Management.Smo.Restore
        Dim loServer As Microsoft.SqlServer.Management.Smo.Server = New Microsoft.SqlServer.Management.Smo.Server

        ' Based on the language
        Select Case nNoLanguage

            ' English
            Case 1
                lcRestoringDatabase = "Restoring database"

                ' French
            Case 2
                lcRestoringDatabase = "Restore de la base de données"

                ' Spanish
            Case 3
                lcRestoringDatabase = "Restoring database"

                ' Portuguese
            Case 4
                lcRestoringDatabase = "Restoring database"

        End Select

        oStatus.Text = lcRestoringDatabase + "..."

        loRestore.Devices.AddDevice(cPath + "\" + cDatabase + ".bak", Microsoft.SqlServer.Management.Smo.DeviceType.File)
        loRestore.Database = cDatabase
        loRestore.Action = Microsoft.SqlServer.Management.Smo.RestoreActionType.Database
        loRestore.ReplaceDatabase = True
        loRestore.PercentCompleteNotification = 1
        AddHandler loRestore.PercentComplete, AddressOf ProgressEventHandler2
        loRestore.SqlRestore(loServer)

        Return True
    End Function

    Private Sub ProgressEventHandler2(ByVal sender As Object, ByVal e As Microsoft.SqlServer.Management.Smo.PercentCompleteEventArgs)
        oStatus.Text = "Restoring database: " + e.Percent.ToString + "% completed..."
        System.Windows.Forms.Application.DoEvents()
    End Sub

End Class
Michel Fournier
Level Extreme Inc.
Designer, architect, owner of the Level Extreme Platform
Subscribe to the site at https://www.levelextreme.com/Home/DataEntry?Activator=55&NoStore=303
Subscription benefits https://www.levelextreme.com/Home/ViewPage?Activator=7&ID=52
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform