Introduction
If you want to backup or restore from our application. NET, the best way is using SMO types.
Using the Code
We add references to the SMO assemblies in SQL Server 2008.
Locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder, and then select the following files.
These are the minimum files that are required to build an SMO application:
- Microsoft.SqlServer.ConnectionInfo.dll
- Microsoft.SqlServer.SqlEnum.dll
- Microsoft.SqlServer.Smo.dll
- Microsoft.SqlServer.SmoExtended.dll
- Microsoft.SqlServer.Management.Sdk.Sfc.dll
Then, import the namespace that we will need:
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Data.SqlClient
The following methods are shown to perform backup and restore, each method has as parameter the path of the file to be generated in the backup or restore file path, and which connectionString
server name user and password for the connection.
Construct
object from connectionString
:
Dim builder As New SqlConnectionStringBuilder(connectionString)
Connect to SQL Server in default database:
Dim connection As New ServerConnection(builder.DataSource, builder.UserID, builder.Password)
Dim sqlServer As New Server(connection)
BackUp
Select database to backup:
bk.Database = builder.InitialCatalog
Set path file to save:
bk.Devices.AddDevice(path, DeviceType.File)
Create the backup:
bk.SqlBackup(sqlServer)
Code to generate the backup:
Public Sub BackUp(path As String, connectionString As String)
Dim builder As New SqlConnectionStringBuilder(connectionString)
Dim connection As New ServerConnection(builder.DataSource, builder.UserID, builder.Password)
Dim sqlServer As New Server(connection)
Dim bk As New Backup
bk.Database = builder.InitialCatalog
bk.Action = BackupActionType.Database
bk.BackupSetDescription = "Full backup of " & bk.Database
bk.BackupSetName = bk.Database
bk.Devices.AddDevice(path, DeviceType.File)
bk.Incremental = False
bk.LogTruncation = BackupTruncateLogType.Truncate
bk.SqlBackup(sqlServer)
End Sub
Restore
Select database to restore:
rs.Database = builder.InitialCatalog
Set path file to restore:
rs.Devices.AddDevice(path, DeviceType.File)
Kill all processes that are using the database to restore:
sqlServer.KillAllProcesses(builder.InitialCatalog)
Wait for the current restore operation to complete before moving on to the next process.
rs.Wait()
Create the restore:
rs.SqlRestore(sqlServer)
Code to generate the restore:
Public Sub Restore(path As String, connectionString As String)
Dim builder As New SqlConnectionStringBuilder(connectionString)
Dim connection As New ServerConnection(builder.DataSource, builder.UserID, builder.Password)
Dim sqlServer As New Server(connection)
Dim rs As New Restore
rs.Database = builder.InitialCatalog
rs.NoRecovery = False
rs.Action = BackupActionType.Database
rs.ReplaceDatabase = True
rs.Devices.AddDevice(path, DeviceType.File)
sqlServer.KillAllProcesses(builder.InitialCatalog)
rs.Wait()
rs.SqlRestore(sqlServer)
End Sub