Using Powershell – Backup Database

In my last post (Using PowerShell – Remove Old Files) I showed how PowerShell script can help Database Administrators and Developers to automate the routine processes. There are many areas where PowerShell script helps to break the border such as network.

For example, recently my colleagues were facing the problem while scheduling backup to some x location (other than server machine) from one DB server. After analyzing the problem we figured out that SQL Server agent was running under Service Account and that account does not have permission to backup location.

To resolve this conflict we wrote the PowerShell script which run from location machine and takes the backup to backup location, as the User running the script from Local machine does got permission to backup machine we schedule the windows Job to complete this task. This is just a one way of doing it. There is much more we can do with PowerShell!

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null

[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

$server1 = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) (“TESTSRV”)

$a = ” Test “

$b = $a  + “.bak”

$bakFile = “C:MISC_BACKUP_FILES” + $b


$dbBackup = new-Object (“Microsoft.SqlServer.Management.Smo.Backup”)

$dbBackup.Database = $a

$dbBackup.BackupSetName = $a + ” Backup”

$dbBackup.Devices.AddDevice($bakFile, “File”)

$dbBackup.Action= “Database”

$dbBackup.Initialize = $TRUE



Enjoy Scripting!


Using PowerShell – Remove Old Files

PowerShell is a powerful scripting shell that lets administrators and developers automate server administration and application deployment. The PowerShell language supports more complex logic than Transact-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts.

One of the routine example is purge Old files. Administrator needs to remove the old files from the disk or archive older files to some location. We can do this by writing vb script, c# script; PowerShell even by SQL Server Maintenance plans as well. But here I am going to demonstrate the same using PowerShell script in just few lines.

$Now = Get-Date$Days = “5”

$LastWrite = $Now.AddDays(-$days)

$TargetFolder = “\\server\folder\”

$Files = get-childitem $TargetFolder | Where {$_.LastWriteTime -le “$LastWrite”}

foreach ($File in $Files)

{write-host “Deleting File $TargetFolder$File” -foregroundcolor “Red”; Remove-Item $TargetFolder$File}

The above script helps to delete the files older than 5 days.

How to use it :

  • Save the script using extension .ps1 E.g. CleanupFile.ps1
  • Run the script using command prompt or you can run it directly from powershell command prompt.

powershell C:\CleanupFile.ps1

Enjoy Scripting!