Writing your own backup routine? I'm sure you have your reasons, but, just to share this great free tool I use to perform automatic, or on-demand scheduled backup of Sql Server.
https://sqlbackupandftp.com/It places the backup files on any on local or networkfolder, and will also upload to an FTP folder if you define the login and destination.
It performs bull backups and also can configure incremental backups between full backups. All per a schedule.
I LOVE THIS TOOL!
I have reliably restored a database from a full backup+incremental backup to get some data back from rows I had accidentally. So, I know it works.
>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
>
