Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

BackUp and Restore with SMO of SQL Server 2008

4.27/5 (3 votes)
26 Oct 2015CPOL1 min read 14K   1.1K  
This tip will show how to perform a backup and restore database from an application in VB.NET 2010.

Image 1

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:

VB.NET
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:

VB.NET
Dim builder As New SqlConnectionStringBuilder(connectionString) 

Connect to SQL Server in default database:

VB.NET
Dim connection As New ServerConnection(builder.DataSource, builder.UserID, builder.Password)
Dim sqlServer As New Server(connection) 

BackUp

Select database to backup:

VB.NET
bk.Database = builder.InitialCatalog 

Set path file to save:

VB.NET
bk.Devices.AddDevice(path, DeviceType.File) 

Create the backup:

VB.NET
bk.SqlBackup(sqlServer) 

Code to generate the backup:

VB.NET
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:

VB.NET
rs.Database = builder.InitialCatalog 

Set path file to restore:

VB.NET
rs.Devices.AddDevice(path, DeviceType.File)

Kill all processes that are using the database to restore:

VB.NET
sqlServer.KillAllProcesses(builder.InitialCatalog)

Wait for the current restore operation to complete before moving on to the next process.

VB.NET
rs.Wait() 

Create the restore:

VB.NET
rs.SqlRestore(sqlServer) 

Code to generate the restore:

VB.NET
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  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)