SQL Server Backups

Richard Siddaway's Blog

One of the duties of a DBA is to ensure that backups are taken of the databases. Usually the backups will be produced by scheduled SQL Server agent jobs. If you need to take a backup for any other reason it is usually necessary to write the TSQL statements of use the GUI. Using Server Management Objects (SMO) it is possible to create backups via PowerShell.

## load SMO assemblies

## use $null to prevent display of assembly load information

$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”)

$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”)

$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)


## set SMO variable

$Smo = “Microsoft.SqlServer.Management.Smo.”


$server = new-object ($Smo + “Server”) “.”


## database to back up

$bkup = new-object ($smo + “Backup”)

$bkup.Database = “AdventureWorks”


## set backup device and type

$bkup.Devices.AddDevice(“C:BackupsAdventureWorks.bak”, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$bkup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database


## invoke backup



Load the SMO assemblies as usual. Create an object to represent the…

View original post 68 more words


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s