Powershell and SQL .. give you better backups

So lately our Backup Admin has been on a mental sabbatical.. which means our DPM backups of SQL has not been working lately. This has caused some issues, especially the 5-8GB daily log growth on one of our SQL servers. Which is okay if the things get backed up and truncated and not okay if they don’t. Of course this is the real world, so when I woke up to a SQL nightmare early Sunday morning I decided to make a little Powershell script to make my life easier.

# ﷽‎
"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
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Xml.XmlDocument");
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO");
$snapins = ("sqlserverprovidersnapin100","sqlservercmdletsnapin100")
foreach($snapin in $snapins)
 {
 if ( (Get-PSSnapin -Name $snapin -ErrorAction SilentlyContinue) -eq $null )
 {
 Add-PsSnapin $snapin
 }
 }
#You can create your own server list
$servers = @("SQL002") #Sould be valid SQL instance to bind on, at least 1 per server
foreach($server in $servers)
 {
 #Find Registry entries for SQL instances on server
 $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $server)
 $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )
 $Instances = $regkey.GetValueNames()
 #Loop through instances on server
 foreach ($i in ($Instances))
 {
 if($i -eq "MSSQLSERVER")
 {
 $sqlinstance = "$server"
 }
 if($i -ne "MSSQLSERVER")
 {
 $sqlinstance = "$server\$i"
 }
 $sqlinstance
 $folderName = Get-Date -format "yyyy-MM-dd-hhmmtt"
 $MainFolderOnLocalhost = "F:\logs"
 $MainFolderAsSeenFromRemote = $MainFolderOnLocalhost.Replace(":", "$")
 $TargetFolder = "\\"+$server+"\"+$MainFolderAsSeenFromRemote+"\" + $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
 $folderName = "$MainFolderOnLocalhost\$folderName"
 foreach ($db in ($dbs))
 {
 $db.name
 $sql = "/*"+$sqlinstance+" : "+$db.name+" : "+$db.Recovery+"*/" +"`n"
 $name = $db.name
 $replaced = $name.Replace(" ", "_")
 $LogBackup = 1
 $DBBackup = 0
 if($DBBackup -eq 1)
 {
 $sql = $sql + "BACKUP DATABASE [$name] TO DISK = N'$folderName\$replaced.db.bak' WITH NOFORMAT, NOINIT, NAME = N'$name-Full Database Backup',