Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 15, 2007

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6

By Muthusamy Anantha Kumar aka The MAK

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]


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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM