Imports Microsoft.SqlServer.management
Imports Microsoft.SqlServer.Management.NotificationServices
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Smo.Broker
Imports Microsoft.SqlServer.Management.Smo.Mail
Imports Microsoft.SqlServer.Management.Smo.RegisteredServers
Imports Microsoft.SqlServer.Management.Smo.Wmi
Imports Microsoft.SqlServer.Management.Trace'Code for backup database
Public Sub BackupDatabase(databaseName As String, userName As String, password As [String], serverName As [String], destinationPath As [String])
Dim sqlBackup As New Backup()
sqlBackup.Action = BackupActionType.Database
sqlBackup.BackupSetDescription = "ArchiveDataBase:" & DateTime.Now.ToShortDateString()
sqlBackup.BackupSetName = "Archive"
sqlBackup.Database = databaseName
Dim deviceItem As New BackupDeviceItem(destinationPath, DeviceType.File)
Dim connection As New ServerConnection(serverName, userName, password)
Dim sqlServer As New Server(connection)
Dim db As Database = sqlServer.Databases(databaseName)
sqlBackup.Initialize = True
sqlBackup.Checksum = True
sqlBackup.ContinueAfterError = True
sqlBackup.Devices.Add(deviceItem)
sqlBackup.Incremental = False
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3)
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate
sqlBackup.FormatMedia = False
sqlBackup.SqlBackup(sqlServer)
End Sub'Code for restoration od database:
Public Sub RestoreDatabase(databaseName As [String], filePath As [String], serverName As [String], userName As [String], password As [String], dataFilePath As [String], _
logFilePath As [String])
Dim sqlRestore As New Restore()
Dim deviceItem As New BackupDeviceItem(filePath, DeviceType.File)
sqlRestore.Devices.Add(deviceItem)
sqlRestore.Database = databaseName
Dim connection As New ServerConnection(serverName, userName, password)
Dim sqlServer As New Server(connection)
Dim db As Database = sqlServer.Databases(databaseName)
sqlRestore.Action = RestoreActionType.Database
Dim dataFileLocation As [String] = Convert.ToString(dataFilePath + databaseName) & ".mdf"
Dim logFileLocation As [String] = Convert.ToString(logFilePath + databaseName) & "_Log.ldf"
db = sqlServer.Databases(databaseName)
Dim rf As New RelocateFile(databaseName, dataFileLocation)
sqlRestore.RelocateFiles.Add(New RelocateFile(databaseName, dataFileLocation))
sqlRestore.RelocateFiles.Add(New RelocateFile(Convert.ToString(databaseName) & "_log", logFileLocation))
sqlRestore.ReplaceDatabase = True
sqlRestore.Complete += New ServerMessageEventHandler(sqlRestore_Complete)
sqlRestore.PercentCompleteNotification = 10
sqlRestore.PercentComplete += New PercentCompleteEventHandler(sqlRestore_PercentComplete)
sqlRestore.SqlRestore(sqlServer)
db = sqlServer.Databases(databaseName)
db.SetOnline()
sqlServer.Refresh()
End Sub
