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 = "/*$sqlinstance_$ : $db.Recovery*/"
 $name = $
 $replaced = $name.Replace(" ", "_")
 $sql = $sql + "
 BACKUP DATABASE [$name] TO DISK = N'C:\Backup\$folderName\$replaced.db.bak' WITH NOFORMAT, NOINIT, NAME = N'$name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
 if ($db.Recovery -match "FULL")
 $sql = $sql + "
 BACKUP LOG [$name] TO DISK = N'C:\Backup\$folderName\$replaced.log.bak' WITH NOFORMAT, NOINIT, NAME = N'$name-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
 invoke-sqlcmd -query "$sql" -serverinstance $sqlinstance -QueryTimeout 1200
 #$sql >> outputme.txt
 $Compression = invoke-sqlcmd -query "SELECT name, value from master.sys.configurations WHERE name like 'backup compression default'" -serverinstance $sqlinstance
 if ($Compression.value -eq 0) #Only compress if SQL server compression is disabled
 $builder = '"C:\Backup\7-Zip\7z.exe a C:\Backup\'+$folderName+'.7z C:\Backup\'+$folderName+' -mx3 -mmt1 ; remove-item C:\Backup\'+$folderName+' -Recurse"'
 else #just store if SQL server compression is enabled
 $builder = '"C:\Backup\7-Zip\7z.exe a C:\Backup\'+$folderName+'.7z C:\Backup\'+$folderName+' -mx0 -mmt1 ; remove-item C:\Backup\'+$folderName+' -Recurse"'
 $cmd = 'powershell '+$builder+''
 $process = [WMIClass]"\\$server\ROOT\cimv2:Win32_Process"
 $process.psbase.Scope.Options.Impersonation = [System.Management.ImpersonationLevel]::Impersonate
 $process.psbase.Scope.Options.Authentication = [System.Management.AuthenticationLevel]::PacketPrivacy
 #Clean folder of old backups
 $DaysAgo = 7
 $Days = -($DaysAgo)
 $FileCounter = (get-childitem -path "\\$server\c$\Backup" *.7z | where-object {$_.LastWriteTime -lt (get-date).AddDays($Days)}).count
 if ($FileCounter -gt 0)
 $files = get-childitem -path "\\$server\c$\Backup" *.7z | where-object {$_.LastWriteTime -lt (get-date).AddDays($Days)}
 foreach ($File in $Files)
 Remove-Item $File.FullName -recurse
 write-host "Thank you and have a nice day mkay."

What this should do is.

* Loop through all servers in the list

* >Lookup each SQL instance on that machine

* > > Loop through all databases on that instance

* > > > Do a backup

* > > > Compress the backup

* > > > Delete old backups

* > > Next DB

* > Next Instance

* Next Server


Let me know if it doesn’t work as intended, but please note that:

There are no warranties, guarantees, pinkie promises, claims or anything suchlike for this script and it is used at your own risk, blah blah, amen

Note to the wise, look for all the invoke-sqlcmd sections and # them out, then pipe everything to a text file for perusal.

Also you will note the 7z option to limit cpu threads to 1 –mmt1, more reading here

Edit: Added-query “-QueryTimeout 1200” to  invoke-sqlcmd, script times out backing up larger DBs

My attempt to intercept the 7z process and change priority to low failed miserably, so I am sticking with limiting the threads to 1.

Added logic to test for SQL server backup compression

Fixed error with server name, now using Registry entries to find the SQL instances, so looping through servers works fine now.


0 views0 comments