Microsoft Windows PowerShell and SQL Server 2005 SMO – Part 6

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

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”

Fig 1.4

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

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

Fig 1.6

Fig 1.7

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

Fig 1.8

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]

Fig 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

Fig 2.0

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]

Fig 2.1

Fig 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