Super Awesome PS SQL Backup script

Well I think it is.


You need to backup some SQL servers, but you would like to control it yourself without using Maintenance Plans.


What do u need, well here are my assumptions:

1. You are running this on a server, or Windows 7 machine.

2. You are running under an account with backup rights and access rights  to the drive of each machine

3. There is a backup folder on each machine “C:\Backup”

4. There is a copy of a 7zip installation folder in the backup folder.. “C:\Backup\7zip”

# ﷽‎
"Set execution policy to [Unrestricted]"
Set-ExecutionPolicy Unrestricted
#note this is tested on PowerShell v2 and SSRS 2008 R2
#Compiled by Adrian Sullivan from various sources
#Date last modified: 13/01/2012

# Alias for 7-zip
if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw "$env:ProgramFiles\7-Zip\7z.exe needed"}
set-alias sz "$env:ProgramFiles\7-Zip\7z.exe"

if ( (Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null ) {Add-PsSnapin sqlserverprovidersnapin100}
if ( (Get-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyContinue) -eq $null ){Add-PsSnapin sqlservercmdletsnapin100}

#You can create your own server list
$servers = @("SP1")#,"SQL1","SQL3") #Server name only
foreach($server in $servers)
 #$instancequery = "SELECT CONVERT(sysname, SERVERPROPERTY('servername')) name;"
 #$Instances = invoke-sqlcmd -query "$instancequery" -serverinstance $server
 $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $server)
 $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )
 $Instances = $regkey.GetValueNames()
 foreach ($i in ($Instances))
 $sqlinstance = "$server\$i"
 $folderName = Get-Date -format "yyyy-MM-dd-hhmmtt"
 $TargetFolder = "\\"+$server+"\c$\Backup\" + $folderName
 [System.IO.Directory]::CreateDirectory($TargetFolder ) | out-null
 $sql = ""
 $firstquery = "select [name],databasepropertyex([name],'Recovery') as [Recovery] from master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model')"
 $dbs = invoke-sqlcmd -query "$firstquery" -serverinstance $sqlinstance
 foreach ($db in ($dbs))
 $sql = "/*$sqlins