Use PowerShell to Backup All User Databases

re: Use PowerShell to Backup All User Databases

cmille19 — Thu, 25 Jun 2009 19:46:26 GMT

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

re: Use PowerShell to Backup All User Databases

Buck Woody — Thu, 25 Jun 2009 20:01:26 GMT

@cmille19 - Thanks for that!

re: Use PowerShell to Backup All User Databases

SQL 2k8 Data Architect — Thu, 16 Jul 2009 18:19:25 GMT

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.

re: Use PowerShell to Backup All User Databases

Woodster — Tue, 04 Aug 2009 20:56:29 GMT

Great script. Simple and effective. Thanks for sharing.