Use PowerShell to Backup All User Databases

Use PowerShell to Backup All User Databases

Rate This
  • Comments 6

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)
            }
        }
 
    }
Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
  • 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

  • @cmille19 - Thanks for that!

  • 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.

  • Great script. Simple and effective. Thanks for sharing.

  • i am getting this error... plz help.

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

  • 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?

Page 1 of 1 (6 items)