SQL database backup script

PowerShell makes it easier to manage even your database backups and restore.

To do a SQL Server backup in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:

Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.

Other assemblies you may want to load are:

?123456 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null#Need SmoExtended for smo.backup [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
Also another point to note is the types of backup you can do. BackupActionType specifies the type of backup. Valid values for this option are Database, Files, Log

Here’s the script. This script is for one specific database. If you want to use this for several database, you will just need to use this code inside a loop.
Better yet, put this in a function, and call this in a loop. I will try to do that sometime soon.

?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657 #============================================================ # Backup a Database using PowerShell and SQL Server SMO # Script below creates a full backup # Donabel Santos #============================================================   #specify database to backup #ideally this will be an argument you pass in when you run #this script, but let's simplify for now $dbToBackup = "test"  #clear screen cls  #load assemblies #note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008 #otherwise may get this error #Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure #the assembly containing this type is loaded.   [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null#Need SmoExtended for smo.backup [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null  #create a new server object $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"$backupDirectory = $server.Settings.BackupDirectory   #display default backup directory "Default Backup Directory: " + $backupDirectory  $db = $server.Databases[$dbToBackup] $dbName = $db.Name   $timestamp = Get-Date -format yyyyMMddHHmmss $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")   #BackupActionType specifies the type of backup. #Options are Database, Files, Log #This belongs in Microsoft.SqlServer.SmoExtended assembly   $smoBackup.Action = "Database"$smoBackup.BackupSetDescription = "Full Backup of " + $dbName$smoBackup.BackupSetName = $dbName + " Backup"$smoBackup.Database = $dbName$smoBackup.MediaDescription = "Disk"$smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak", "File") $smoBackup.SqlBackup($server)   #let's confirm, let's list list all backup files $directory = Get-ChildItem $backupDirectory  #list only files that end in .bak, assuming this is your convention for all backup files $backupFilesList = $directory | where {$_.extension -eq ".bak"} $backupFilesList | Format-Table Name, LastWriteTime

Thank you.

You both were very helpfull and i managed to fix the whole thing by following the instructions from Daniel.

Thanks again.