BackUp and Restore with SMO of SQL Server 2008

• Download source - 47.9 KB
• Download demo - 1.2 MB

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