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 #backup $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 $dbBackup.SqlBackup($server1)

 

Enjoy Scripting!