21 Mei 2011

Back Up SQL Server via VB.NET

First you have to add some namespace in your vb file:
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

Tidak ada komentar:

Posting Komentar