Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6

Part I and Part II of this series discussed PowerShell installation and simple SMO, WMI cmdlets. Part III discussed how to script PowerShell and connect to SQL Server. Part IV discussed how to use a PowerShell script to loop through the content of a file and connect to different servers and Part V discussed how to use PowerShell and SMO to create a database.

This installment of the article series illustrates how to use PowerShell and PowerShell script to backup databases, etc.

Method 1

Let us assume we have a database ‘MyDatabase’, on the server ‘HOME’. Now let us assume that we want to take a full backup of the databases onto the folder c:\test.

Execute the following cmdlets one by one as shown below. Refer Fig 1.0

[System.Reflection.Assembly]::LoadWithPartialName
	("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory]::CreateDirectory("C:\test") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME" 
$bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"
$bck.Action = 'Database' 
$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine("C:\test", "MyDatabase" +".bak")
$bck.Devices.Add($fil)
$bck.Database="MyDatabase" 
$bck.SqlBackup($srv)
write-host "Backup of MyDatabase done"

This script will take a full backup of the MyDatabase database onto the folder c:\test as shown below. [Refer Fig 1.1]

Method 2

Let us assume we have a database ‘MyDatabase’, on the server ‘HOME’. Now let us assume that we want to take a Transaction log backup of the databases onto the folder c:\test. Execute the following cmdlets, one by one, as shown below. [Refer Fig 1.2]

[System.Reflection.Assembly]::LoadWithPartialName
	("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory]::CreateDirectory("C:\test") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME" 
$bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"
$bck.Action = 'Log' 
$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine("C:\test", "MyDatabase" +".trn")
$bck.Devices.Add($fil)
$bck.Database="MyDatabase" 
$bck.SqlBackup($srv)
write-host "Log Backup of MyDatabase done"

This script will take the Transaction log backup of the MyDatabase database onto the folder c:\test as shown below. [Refer Fig 1.3]

Method 3

Let us assume we have a database ‘MyDatabase’, on the server ‘HOME’. Now let us assume that we want to take a Differential backup of the databases onto the folder c:\test. Execute the following cmdlets, one by one, as shown below. [Refer Fig 1.4]

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory]::CreateDirectory("C:\test") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME" 
$bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"
$bck.Incremental = 1
$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine("C:\test", "MyDatabase" +".diff")
$bck.Devices.Add($fil)
$bck.Database="MyDatabase" 
$bck.SqlBackup($srv)
write-host "Differential Backup of MyDatabase done"

This script will take the Differential backup of the MyDatabase database on to the folder c:\test as shown below. [Refer Fig 1.5]

Method 4

Now let’s join Method 1, Method 2 and Method 3 together as a PowerShell script that will accept Server name, Database name, Backup type and Folder location as parameters, so that it will be easy to automate. Create the following Backup.ps1 script in C:\PS folder, as shown below. [Fig 1.6, 1.7]

param (
  [string] $ServerName,
  [string] $DatabaseName,
  [string] $Backuptype ,
  [string] $BackupPath
)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory]::CreateDirectory($BackupPath) | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$servername"
$bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"

if ($Backuptype -eq "FULL") 
{
$bck.Action = 'Database' 
$extenstion=".BAK" 
$text1="Full Backup "
}

if ($Backuptype -eq "TRAN") 
{
$bck.Action = 'Log' 
$extenstion=".LOG" 
$text1="Transactional Log Backup "
}

if ($Backuptype -eq "DIFF") 
{ 
$bck.Incremental = 1 
$extenstion=".DIFF"  
$text1="Differential Backup "
}

$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine($BackupPath, $DatabaseName+$extenstion)
$bck.Devices.Add($fil)
$bck.Database=$DatabaseName
$bck.SqlBackup($srv)
write-host $text1 of $Databasename done

Now let us execute the above PowerShell script with the following parameters, as shown below. [Refer Fig 1.8]

./backup.ps1 HOME MyDatabase FULL C:\test
./backup.ps1 HOME MyDatabase DIFF C:\test
./backup.ps1 HOME MyDatabase TRAN C:\test

The execution of above script results in taking a Full, Transaction Log and Differential backup of the Mydatabase database. [Refer Fig 1.8 and 1.9]

Method 5

Let us assume that we need to add the Date Stamp to the file name when doing a backup using the above script. In order to achieve that, update the script with the following code. [Fig 2.0]

param (
  [string] $ServerName,
  [string] $DatabaseName,
  [string] $Backuptype ,
  [string] $BackupPath
)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.IO.Directory]::CreateDirectory($BackupPath) | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$servername" 
$bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"

if ($Backuptype -eq "FULL") 
{
$bck.Action = 'Database' 
$extenstion=".BAK" 
$text1="Full Backup "
}

if ($Backuptype -eq "TRAN") 
{
$bck.Action = 'Log' 
$extenstion=".LOG" 
$text1="Transactional Log Backup "
}

if ($Backuptype -eq "DIFF") 
{ 
$bck.Incremental = 1 
$extenstion=".DIFF"  
$text1="Differential Backup "
}

$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine($BackupPath, $DatabaseName+ "_"+ [DateTime]::Today.ToString("yyyy_MM_dd")+$extenstion)
$bck.Devices.Add($fil)
$bck.Database=$DatabaseName
$bck.SqlBackup($srv)
write-host $text1 of $Databasename done

Now let us execute the above PowerShell script with the following parameters, as shown below. [Refer Fig 2.1]

./backup.ps1 HOME\SQLExpress Admin FULL C:\test
./backup.ps1 HOME\SQLExpress Admin DIFF C:\test
./backup.ps1 HOME\SQLExpress Admin TRAN C:\test

Note: HOME\SQLExpress is the named instance of sql server on the host HOME;

Admin is the database name.

The execution of the above script results in taking a Full, Transaction Log and Differential backup of the database Admin. [Refer Fig 2.1 and 2.2]

Conclusion

Part 6 of this series has illustrated how to use PowerShell and PowerShell script to do Full Backup, Transaction Log backup and Differential backups of the databases with a date stamped file name.

» See All Articles by Columnist MAK