SQL Server backup and powershell

  • Article

In my previous post, I give some basic sample on how to use powershell for SQL Server. Here we are going to build a sample script to backup database.

1) Perfom a full backup for a specified database :

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "myServer\myInstance"

#Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

#Set the Database property to myDB
$dbBackup.Database = "myDB"

#Add the backup file to the Devices collection and specify File as the backup type
$dbBackup.Devices.AddDevice("C:\backups\myDB_FULL.bak", "File")

#Specify the Action property to generate a FULL backup
$dbBackup.Action="Database"

#Call the SqlBackup method to generate the backup
$dbBackup.SqlBackup($s)

2) Perfom a full backup for all databases :

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "myServer\myInstance"

$bkdir = "D:\backups"
$dbs = $s.Databases
foreach ($db in $dbs)
{
if($db.Name -ne "tempdb") #We don't want to backup the tempdb database
{
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmm
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")
$dbBackup.SqlBackup($s)
}
}

3) Perform a transaction log backup for all databases :

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "myServer\myInstance"

$bkdir = "D:\backups"
$dbs = $s.Databases
foreach ($db in $dbs)
{

if($db.RecoveryModel -ne 3) #Don't do Log backups for DBs with RecoveryModel=3 or SIMPLE
{
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmm
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Log"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_log_" + $dt + ".trn", "File")
$dbBackup.SqlBackup($s)
}

}

 

4) monitoring backup

the following script give info about the last databases backup

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "MyServer\MyInstance"

$dbs=$s.Databases

$dbs | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize