Use PowerShell to Backup All User Databases


This script will back up all user databases – you need to change the BWOODY1\SQL2K8 part to your server\instance name, and of course, you should only run this on a test system until you completely understand it.

Unfortunately, the SQL Server PowerShell Provider doesn’t make this very easy – and there may be a better way to do this once I research it more.

Also – this script was adapted from one given to me earlier, and unfortunately I don’t recall the source. If this is partly your work, please add a comment here for attribution:

 

# Performs a Full backup followed by a transaction log backup on all user databases
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'
$bkdir = $s.Settings.BackupDirectory
$dbs = $s.Databases
$dbs | foreach-object {
    $db = $_
    
    if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) {
        $dbname = $db.Name
        $dt = get-date -format yyyyMMddHHmmss
        $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
        $dbbk.Action = 'Database'
        $dbbk.BackupSetDescription = "Full backup of " + $dbname
        $dbbk.BackupSetName = $dbname + " Backup"
        $dbbk.Database = $dbname
        $dbbk.MediaDescription = "Disk"
        $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
        $dbbk.SqlBackup($s)
        if ($db.DatabaseOptions.RecoveryModel -ne 'Simple') {
            $dt = get-date -format yyyyMMddHHmmss
            $dbtrn = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
            $dbtrn.Action = 'Log'
            $dbtrn.BackupSetDescription = "Trans Log backup of " + $dbname
            $dbtrn.BackupSetName = $dbname + " Backup"
            $dbtrn.Database = $dbname
            $dbtrn.MediaDescription = "Disk"
            $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", 'File')
            $dbtrn.SqlBackup($s)
            }
        }
 
    }

Comments (6)

  1. cmille19 says:

    You need to add loading the SMOExtended assembly to your script to support SMO 10.0. The backup class was moved from the SMO assembly in 9.0 to the SMOExtended assembly in 10.0:

    [reflection.assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’) > $null

  2. BuckWoody says:

    @cmille19 – Thanks for that!

  3. Aaron Nelson says:

    Buck,

    I kept on having problems with the script trying to backup my dbs that are in simple recovery mode.  I tried several different things and finally found this to work:

    On the If loop for the transaction logs I had to swap out the "-ne" for "-notmatch"

    Before:

    if ($db.DatabaseOptions.RecoveryModel -ne ‘Simple’) {

    After:

    if ($db.DatabaseOptions.RecoveryModel -notmatch "Simple") {

    Now it skips the dbs in simple mode every time.  I don’t know if anyone else was having this problem but my setup is SQL 2008 Dev. SP1 running on Windows 7 RC (7100).

    Hope this helps if anyone else was stuck like me.

  4. Woodster says:

    Great script. Simple and effective. Thanks for sharing.

  5. xibic says:

    i am getting this error… plz help.

    >>Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'localhost'. "

  6. Nick says:

    With regard to the notion of powershell running in a seperate memory space, have you seen the above be more performant than a standard backup job in terms of IO consumed, memory and CPU etc?