Welcome to MSDN Blogs Sign in | Join | Help

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)
            }
        }
 
    }
Published Thursday, June 25, 2009 8:16 AM by Buck Woody

Comments

Thursday, June 25, 2009 12:46 PM by cmille19

# re: Use PowerShell to Backup All User Databases

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

Thursday, June 25, 2009 1:01 PM by Buck Woody

# re: Use PowerShell to Backup All User Databases

@cmille19 - Thanks for that!

Thursday, July 16, 2009 11:19 AM by SQL 2k8 Data Architect

# re: Use PowerShell to Backup All User Databases

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.

Tuesday, August 04, 2009 1:56 PM by Woodster

# re: Use PowerShell to Backup All User Databases

Great script. Simple and effective. Thanks for sharing.

Anonymous comments are disabled
 
Page view tracker