There are a lot of programs to backup Microsoft SQL Server databases. Here we will write our own program using .NET to do this process. In addition, we will compress the backup file and upload it to FTP server. Settings for this application will be saved in an INI file. We will slipt this project to 4 parts:
- Creating Class to read settings from INI file
- Creating Class to Compress the backup file
- The main program to backup, compress and upload databases Open Visual Studio then select Windows Desktop, Console App (.NET Framework)
You will need to add FluentFTP using NuGet as shown in the picture below
1. CREATING CLASS TO READ SETTINGS FROM INI FILE
Public Class iniFile
Private Declare Auto Function GetPrivateProfileString Lib "kernel32" _
(ByVal lpAppName As String,
ByVal lpKeyName As String,
ByVal lpDefault As String,
ByVal lpReturnedString As StringBuilder,
ByVal nSize As Integer,
ByVal lpFileName As String) As Integer
Private Declare Function WritePrivateProfileString Lib "kernel32" _
Alias "WritePrivateProfileStringA" _
(ByVal lpApplicationName As String,
ByVal lpKeyName As String,
ByVal lpStringAsAny As String,
ByVal lpFileNameAsString As String) As Long
Public Function ReadSettings(ByVal FileName As String, ByVal Section As String,
ByVal KeyName As String, ByVal DefaultValue As String) As String
Dim res As Integer
Dim sb As StringBuilder
sb = New StringBuilder(500)
res = GetPrivateProfileString(Section, KeyName, DefaultValue, sb, sb.Capacity, FileName)
Return sb.ToString
End Function
Public Function WriteSettings(ByVal FileName As String, ByVal Section As String,
ByVal KeyName As String, ByVal KeyValue As String) As Boolean
Try
Return WritePrivateProfileString(Section, KeyName, KeyValue, FileName)
Catch ex As Exception
Return False
End Try
End Function
End Class
2. CREATING CLASS TO COMPRESS THE BACKUP FILE
Imports System.IO
Imports System.IO.Compression
Public Class Zipper
Public Event Progress(Percent As Integer)
Public Event Complete()
Public Event StatusChanged(Status As String)
Private _Cancel As Boolean
Public Property Cancel As Boolean
Get
Return _Cancel
End Get
Set(value As Boolean)
If _Cancel = True Then Exit Property
_Cancel = value
End Set
End Property
Private _Compression As CompressionLevel
Public Property CompressionLevel As CompressionLevel
Get
Return _Compression
End Get
Set(value As CompressionLevel)
_Compression = value
End Set
End Property
Private _Target As String
Public Property TargetURL As String
Get
Return _Target
End Get
Set(value As String)
_Target = value
End Set
End Property
Private _Source As String
Public Property SourceURL As String
Get
Return _Source
End Get
Set(value As String)
_Source = value
End Set
End Property
Private _IsDir As Boolean
Public ReadOnly Property SourceIsDirectory
Get
Return _IsDir
End Get
End Property
Private _Overwrite As Boolean
Public Property OverwriteTarget As Boolean
Get
Return _Overwrite
End Get
Set(value As Boolean)
_Overwrite = value
End Set
End Property
Private _IncludeRootDir As Boolean
Public Property IncludeRootDir As Boolean
Get
Return _IncludeRootDir
End Get
Set(value As Boolean)
_IncludeRootDir = value
End Set
End Property
Private _SessionLength As Int64
Private _SessionFiles As String()
Private _RootDir As String
Public Sub New(Source As String, Target As String, CompressionLevel As CompressionLevel)
_Overwrite = False
_IncludeRootDir = True
_Target = Target
_Compression = CompressionLevel
_Cancel = False
If IsDir(Source) <> 1 Then
_IsDir = IsDir(Source)
_Source = Source
Else
Throw New Exception("Source file or directory doesn't exist or cannot be accessed.")
End If
End Sub
Private Function GetSessionLength() As Int64
Dim sLen As Int64 = 0
For Each SessionFile As String In _SessionFiles
sLen += New FileInfo(SessionFile).Length
If Cancel = True Then Exit For
Next
Return sLen
End Function
Private Function IsDir(Source As String) As Int16
If File.Exists(Source) Then
Return 0
ElseIf Directory.Exists(Source) Then
Return -1
Else
Return 1
End If
End Function
Public Sub Compress()
RaiseEvent StatusChanged("Gathering Required Information...")
If SourceIsDirectory Then
_SessionFiles = Directory.GetFiles(SourceURL, "*", SearchOption.AllDirectories)
Else
_SessionFiles = New String() {SourceURL}
End If
RaiseEvent StatusChanged("Examining Files...")
_SessionLength = GetSessionLength()
If SourceIsDirectory And IncludeRootDir = False Then
_RootDir = SourceURL & "\"
Else
_RootDir = String.Join("\", SourceURL.Split("\").ToArray,
0, SourceURL.Split("\").ToArray.Length - 1) & "\"
End If
RaiseEvent StatusChanged("Compressing...")
Try
ZipItUp()
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try
If Cancel = True Then
RaiseEvent StatusChanged("Cancelled.")
RaiseEvent Progress(100)
Else
RaiseEvent StatusChanged("Completed")
End If
RaiseEvent Complete()
End Sub
Private Sub ZipItUp()
If Cancel = True Then Exit Sub
Dim BlockSizeToRead As Int32 = 1048576 '1Mib Buffer
Dim Buffer As Byte() = New Byte(BlockSizeToRead - 1) {}
Dim BytesRead As Int64, TotalBytesRead As Int64
Dim LiveProg As Int16 = 0
Dim PrevProg As Int16 = 0
If File.Exists(_Target) And OverwriteTarget = False Then
Throw New Exception("Target File Already Exists.")
Else
File.Delete(_Target)
End If
Using FS As FileStream = New FileStream(_Target, FileMode.CreateNew, FileAccess.Write)
Using Archive As ZipArchive = New ZipArchive(FS, ZipArchiveMode.Create)
Dim Entry As ZipArchiveEntry = Nothing
For Each SessionFile As String In _SessionFiles
Try
Using Reader As FileStream = File.Open(SessionFile, FileMode.Open,
FileAccess.Read)
Entry = Archive.CreateEntry(SessionFile.Replace(_RootDir, ""),
_Compression)
Using Writer As Stream = Entry.Open()
While (InlineAssignHelper(BytesRead,
Reader.Read(Buffer, 0, Buffer.Length - 1))) > 0
Writer.Write(Buffer, 0, BytesRead)
TotalBytesRead += BytesRead
LiveProg = CInt((100 / _SessionLength) * TotalBytesRead)
If LiveProg <> PrevProg Then
PrevProg = LiveProg
RaiseEvent Progress(LiveProg)
End If
If Cancel = True Then Exit While
End While
End Using
End Using
Catch Ex As Exception
TotalBytesRead += New FileInfo(SessionFile).Length
Console.WriteLine(String.Format("Unable to add file to archive: {0} Error:{1}",
SessionFile, Ex.Message))
End Try
If Cancel = True Then Exit For
Next
End Using
End Using
If Cancel = True Then
File.Delete(_Target)
End If
End Sub
Private Function InlineAssignHelper(Of T)(ByRef target As T, value As T) As T
target = value
Return value
End Function
End Class
3. THE MAIN PROGRAM TO BACKUP, COMPRESS AND UPLOAD DATABASES
Module mdlMain
Private SqlCon As New Data.SqlClient.SqlConnection
Private ServerName As String = "(local)"
Private SqlUser As String = "sa"
Private SqlPassword As String = ""
Private BackupPath As String = "D:\SqlBackup"
Private DeleteExisting As Boolean = True
Private CompressBackup As Boolean = True
Structure stftpSettings
Dim Server As String
Dim Port As Integer
Dim User As String
Dim Password As String
Dim Enabled As Boolean
Dim Folder As String
Dim DeleteOlderThan As Integer
End Structure
Dim ftpSettings As stftpSettings
Dim WithEvents zip As Zipper
Sub Main()
ReadIniFile()
If ftpSettings.Enabled = True Then
DeleteOldFilesOnFtp()
End If
CheckFolder()
BackupDatabases()
End Sub
Private Sub ReadIniFile()
Dim iniReader As New iniFile
Dim IniFileName As String = System.AppDomain.CurrentDomain.BaseDirectory & "\Settings.ini"
ServerName = iniReader.ReadSettings(IniFileName, "SqlServer", "Server", "")
SqlUser = iniReader.ReadSettings(IniFileName, "SqlServer", "User", "")
SqlPassword = iniReader.ReadSettings(IniFileName, "SqlServer", "Password", "")
BackupPath = iniReader.ReadSettings(IniFileName, "SqlServer", "BackupPath", "")
SqlPassword = iniReader.ReadSettings(IniFileName, "SqlServer", "Password", "")
If iniReader.ReadSettings(IniFileName, "SqlServer", "DeleteExisting", "").ToUpper = "TRUE" Then
DeleteExisting = True
Else
DeleteExisting = False
End If
If iniReader.ReadSettings(IniFileName, "SqlServer", "CompressBackup", "").ToUpper = "TRUE" Then
CompressBackup = True
Else
CompressBackup = False
End If
Dim DeleteOlderThan As String = String.Empty
With ftpSettings
If iniReader.ReadSettings(IniFileName, "Ftp", "enable", "").ToUpper = "TRUE" Then
.Enabled = True
Else
.Enabled = False
End If
.Server = iniReader.ReadSettings(IniFileName, "ftp", "Server", "")
.Port = iniReader.ReadSettings(IniFileName, "ftp", "port", "")
.User = iniReader.ReadSettings(IniFileName, "ftp", "user", "")
.Password = iniReader.ReadSettings(IniFileName, "ftp", "password", "")
.Folder = iniReader.ReadSettings(IniFileName, "ftp", "folder", "")
DeleteOlderThan = iniReader.ReadSettings(IniFileName, "ftp", "DeleteOlderThan", "").ToUpper
If InStr(DeleteOlderThan, "H") Then .DeleteOlderThan = Val(DeleteOlderThan) * 60
If InStr(DeleteOlderThan, "D") Then .DeleteOlderThan = Val(DeleteOlderThan) * 24 * 60
If InStr(DeleteOlderThan, "M") Then .DeleteOlderThan = Val(DeleteOlderThan)
End With
End Sub
Private Sub CheckFolder()
If IO.Directory.Exists(BackupPath) Then
If DeleteExisting = True Then
IO.Directory.Delete(BackupPath, True)
IO.Directory.CreateDirectory(BackupPath)
End If
Else
IO.Directory.CreateDirectory(BackupPath)
End If
End Sub
Private Sub BackupDatabases()
If IO.Directory.Exists(BackupPath) = False Then
IO.Directory.CreateDirectory(BackupPath)
End If
Console.WriteLine("Connecting to server: " & ServerName)
Dim ConBldr As New Data.SqlClient.SqlConnectionStringBuilder
With ConBldr
.DataSource = ServerName
.InitialCatalog = "master"
.IntegratedSecurity = False
.Password = SqlPassword
.UserID = SqlUser
End With
SqlCon.ConnectionString = ConBldr.ConnectionString
SqlCon.Open()
Console.WriteLine("Connected to server: " & ServerName)
Console.WriteLine("Retriving list of databases...")
Dim sqlCmd As New Data.SqlClient.SqlCommand
Dim SqlDA As New Data.SqlClient.SqlDataAdapter("SELECT name FROM SYS.databases WHERE database_id >4 ORDER BY name", SqlCon)
Dim DT As New DataTable
SqlDA.Fill(DT)
SqlDA.Dispose()
sqlCmd.Connection = SqlCon
Console.WriteLine(DT.Rows.Count.ToString & " Databases found")
Console.WriteLine("Started Databases Backup Procedure...")
Console.WriteLine()
Console.WriteLine("***************************************************************************")
Console.WriteLine()
Dim BackupFile As String
Dim CompressedFile As String
For Each DR As DataRow In DT.Rows
Console.WriteLine("Current Database: " & DR("Name"))
BackupFile = BackupPath & "\" & DR("NAME") & "_" & Format(Now, "yyyyMMdd_HHmmss") & ".bak"
sqlCmd.CommandTimeout = 900
sqlCmd.CommandText = "BACKUP DATABASE " & DR("NAME") & " TO DISK ='" & BackupFile & "'"
sqlCmd.ExecuteNonQuery()
Dim finfo As New IO.FileInfo(BackupFile)
Console.WriteLine("Backup File Size: " & Format(finfo.Length / 1024 / 1024, "#,##0.00") & " MB")
If CompressBackup Then
CompressedFile = CompressFile(BackupFile)
IO.File.Delete(BackupFile)
BackupFile = CompressedFile
End If
If ftpSettings.Enabled Then
UploadToFTP(BackupFile)
Console.WriteLine("Uploaded to FTP Server: Done!")
End If
Console.WriteLine()
Console.WriteLine("***************************************************************************")
Console.WriteLine()
Next
Console.WriteLine("Backup Finished")
End Sub
Private Function CompressFile(ByVal FileName As String) As String
Console.WriteLine("Compress Database Backup File: " & IO.Path.GetFileName(FileName))
Dim zipPath As String = IO.Path.GetDirectoryName(FileName) & "\" & IO.Path.GetFileNameWithoutExtension(FileName) & ".zip"
zip = New Zipper(FileName, zipPath, CompressionLevel.Optimal)
zip.SourceURL = FileName
zip.TargetURL = zipPath
zip.OverwriteTarget = True
zip.Compress()
Return zipPath
End Function
Private Sub UploadToFTP(ByVal FileName As String)
Using ftp = New FluentFTP.FtpClient
ftp.Host = ftpSettings.Server
ftp.Credentials = New Net.NetworkCredential(ftpSettings.User, ftpSettings.Password)
ftp.SetWorkingDirectory(ftpSettings.Folder)
ftp.Port = ftpSettings.Port
ftp.UploadFile(FileName, IO.Path.GetFileName(FileName), FluentFTP.FtpRemoteExists.Overwrite, True)
End Using
End Sub
Private Sub DeleteOldFilesOnFtp()
Using ftp = New FluentFTP.FtpClient
ftp.Host = ftpSettings.Server
ftp.Credentials = New Net.NetworkCredential(ftpSettings.User, ftpSettings.Password)
ftp.SetWorkingDirectory(ftpSettings.Folder)
For Each item In ftp.GetListing(ftp.GetWorkingDirectory())
Dim FileDate As Date = item.Modified
If DateDiff(DateInterval.Minute, FileDate, Now) >= ftpSettings.DeleteOlderThan Then
ftp.DeleteFile(item.FullName)
End If
Next
End Using
End Sub
Private Sub zip_Complete() Handles zip.Complete
Dim finfo As New IO.FileInfo(zip.TargetURL)
Console.WriteLine("Zip File Size: " & Format(finfo.Length / 1024 / 1024, "#,##0.00") & " MB")
End Sub
Private Sub zip_Progress(Percent As Integer) Handles zip.Progress
Console.Write("{0}Percentage: {1}%", vbCr, Percent)
If Percent = 100 Then
Console.WriteLine()
End If
End Sub
Private Sub zip_StatusChanged(Status As String) Handles zip.StatusChanged
Console.WriteLine(Status)
End Sub
End Module