Backup Database using PowerShell : Microsoft SQL Server

Often there’s a need to perform backup of all multiple databases on an instance or maybe multiple databases on multiple instance across environment. Now, this may well become a laborious task if you don’t have any fancy tool to do the job for you and that’s exactly where PowerShell comes to rescue. PowerShell can be used to perform such task easily.
Let’s Get Started!!!

How to Use the Script:

1. Copy below function to notepad file and save as .psm1
2. Follow instruction in script before function Credentials
3. Import psm1 file using command : Import-Module <Location of psm1 file>
4. Type Get-Help Sql-BackupDatabases to get information and instructions about script

function Sql-BackupDatabases
{
<#
.SYNOPSIS
Backup User databases on specified Instances across Environment.
.DESCRIPTION
This Script uses SMO to backup databases across environment based on parameters provided.
This will create CopyOnly Backup's to avoid breaking Backup Chain.
.PARAMETER backupdirectory
Backup Location for CopyOnly Backups either on local system or on shared location.
.PARAMETER instanceList
List of instances for whih backup needs to be performed.
You may specify a Text file like d:\List.txt containing list of instances.
.EXAMPLE
Backup all user databases on specified Instance.
Sql-BackupDatabases -backupdirectory d:\backup -instanceList "My\Instance"
Sql-BackupDatabases -backupdirectory d:\backup -instanceList d:\list.txt
.NOTES
The SQL Server Service account must have access to Backup Location and You should use an account that has access to all SQL Instances across environment.
#>
param([parameter(Mandatory=$true)] [string] $backupdirectory, $instanceList)
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.SMOExtended")
    if ($instanceList -like "*.txt")
    {
        $list=get-content $instanceList
    }
    else { $list=$instanceList}
if($backupdirectory -eq ""){write-host "please provide Backup Location"}
foreach ($l in $list)
    {
        $instance=Connect-Instance -instanceName $l
        $bkdir = $backupdirectory +"\"+ $l
        if (!(Test-Path $bkdir))
            {
                New-Item -Path $bkdir -ItemType Directory
             }
        $database=$instance.Databases["master"]
        $dbs = $database.ExecuteWithResults("select name from sys.databases where name not in ('master','model','tempdb','msdb') and state_desc='ONLINE'")
        $dbs = $dbs.tables[0]
    foreach ($db in $dbs)
        {
            Backup-Database -data $db.name -server $instance
        }
    }
}
function Connect-Instance
{
        param ([string] $instanceName)
        $s = new-object ("Microsoft.SqlServer.Management.Smo.Server")  $instanceName
        $s.connectioncontext.loginsecure=$false
        $credential=get-credential
        ##$s.connectioncontext.set_login("sa")
        $s.connectioncontext.set_login($credential.UserName)
        ##$credential=Credentials
        ##$s.connectioncontext.set_securepassword($credential)
        $s.connectioncontext.set_securepassword($credential.Password)    
        return $s
}
### Instruction To generate password use below command #####
in powershell command prompt type: [byte[]] $key=(1..16)
$pass="Your Password"| ConvertTo-SecureString -Force -AsPlainText | ConvertFrom-SecureString –Key $key
This will generate a secure password using Key variable. Use the contents of $pass variable for $password value in below function
##############################################
function Credentials
{
[byte[]] $key=(1..16)
    $password="use $pass value from above"
    $credential=new-object system.management.automation.pscredential "sa",($password | convertto-securestring –key $key)
    return $credential.Password
}
Function Backup-Database
{
    Param ([string] $data,$server)
$back= new-object ("Microsoft.SqlServer.Management.Smo.Backup")
            $back.database=$db.name
            $dbname = $db.name
            $back.action="Database"
            $back.CopyOnly=$true
            $back.devices.adddevice($bkdir + "\" + $db.name + ".bak","File")
            $back.backupsetname="BackupTest"
            $percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
                     Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
            }
                    $back.add_PercentComplete($percent)
                    $back.add_Complete($complete)
            Try
                {
                        $back.sqlbackup($server) 
                    Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))            
                }
            Catch
                {
                        Write-Output $_.Exception.InnerException
                }
}