Backup and Restore SQL User Databases Using PowerShell

There are several ways how to backup and restore SQL databases. Over time, the way I back up and restore databases has changed.
Originally my backup database code looked like this:
The problem is that you needed to have SQL Server Management Studio (SSMS) installed for the code to run correctly. Having SSMS installed on a production server is not the best of ideas, so luckily PowerShell gives us the ability to backup all user databases very easily:
Now that we have our databases backed up, let's take a look at the old way that I use to restore databases:
Below is the newer, strictly using PowerShell way that I use to restore the just backed up databases:
Hopefully this will give you a couple of good solutions for backing up and restoring your SQL Server Databases through PowerShell.
-PC

Getting Your SharePoint Farm Information

As we prepare our upgrade strategy for SharePoint Server 2013, one of the major requirements is documentation of our existing SharePoint 2010 environment.  This is also a good script to run when first getting your hands on an unknown farm.  This should save you a bunch of documentation time.
<Lecture>
In theory, you should be reviewing the output documentation of your environment on a weekly basis.  You could even go as far as importing the results into SQL, and tracking your growth and changes of your SharePoint Farm with SSRS.
</Lecture>
This script will create a folder, go through your farm, and create the appropriate .csv file as long as you have the appropriate permissions to run the script and you have permissions to access the data.

There are several blog posts on how to take all the .csv files and merge them into one .xlsx file.  Here are a couple that I found useful.  You will notice that they call the ComObject Excel.Application, so if you do not have Excel installed or the appropriate Office System Driver Connectivity Components installed, you will get an error.

This post by Jeff Hicks was very informational about how to get everything working:

http://www.petri.co.il/export-to-excel-with-powershell.htm

This page actually has a .ps1 for you to download, but in the Q&A section, imfrancisd has a script that works very nicely as well.

Update (01/10/2012)
Added -Limit All
Update (10/30/2014)
Added a check to see if Backup location ends with "\"
Added URL to Sandbox Solution location for output
Added site structure output for entire farm
Added Disposal of objects
Update (10/21/2015)
Moved Script to Gist
Fixed error in Get Features section thanks to Matthew Bramer (@ionline247)
Cleaned up Get Site Structure section to get ALL webs within each Site Collection by Web Application.

Backing Up SQL DBs Using PowerShell

My next project requires that I create several SQL Mirrors, and instead of backing up my databases with SQL, I thought I would try it in PowerShell.  The majority of the script is from Edwin Sarmiento's blog http://www.mssqltips.com/sqlservertip/1862/backup-sql-server-databases-with-a-windows-powershell-script/  (Excellent Post)

$bkdir = "\\serverName\Shared\Temp" # Set Backup Path! (optional "C:\Temp")

if ((test-path $bkdir) -eq $false ) # Verify folder else create it...
  {
     [IO.Directory]::CreateDirectory($bkdir) 
  }

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance

$dbs = $s.Databases
foreach ($db in $dbs) 
  {
     if(($db.Name -ne "tempdb") -and ($db.Name -ne "master") -and ($db.Name -ne "model") -and ($db.Name -ne "msdb")) 
          {
               $dbname = $db.Name
               $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
               $dbBackup.Action = "Database"
               $dbBackup.Database = $dbname
               $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + ".bak", "File")
               $dbBackup.SqlBackup($s)
               write-host($db.name + " has been backed up.")
          }
  }

If you are saving to a network location, the SQL SA account and the person running the script need to have read/write permissions to the location.

Update: (03/05/2012)
Added save location verification else create folder.