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', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
$sql = $sql +"`n" +"GO" +"`n"
}
if($LogBackup -eq 1)
{
if ($db.Recovery -match "FULL")
{
$sql = $sql + "BACKUP LOG [$name] TO DISK = N'$folderName\$replaced.log.bak' WITH NOFORMAT, NOINIT, NAME = N'$name-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
$sql = $sql +"`n" +"GO"
}
}
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 = '"'+$MainFolderOnLocalhost+'\7-Zip\7z.exe a '+$folderName+'.7z '+$folderName+' -mx3 -mmt1 ; remove-item '+$folderName+' –Recurse'
}
else #just store if SQL server compression is enabled
{
$builder = '"'+$MainFolderOnLocalhost+'\7-Zip\7z.exe a '+$folderName+'.7z '+$folderName+' -mx0 -mmt1 ; remove-item '+$folderName+' -Recurse '
}
$cmd = 'powershell '+$builder+''
$ShowMe = 0
if($ShowMe -eq 0)
{
$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
$process.Create($cmd)
}
else
{
write-host $cmd
}
$cleanfolder = 1
if($cleanfolder -eq 1) #Clean folder of old backups
{
$DaysAgo = 7
$Days = -($DaysAgo)
$FileCounter = (get-childitem -path "\\$server\$MainFolderAsSeenFromRemote" *.7z | where-object {$_.LastWriteTime -lt (get-date).AddDays($Days)}).count
if ($FileCounter -gt 0)
{
$files = get-childitem -path "\\$server\$MainFolderAsSeenFromRemote" *.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."
}
}
The script does the following:
Loops through all the servers
Finds the instances
Finds all the DBs
Check recovery model
Does the backup
Checks compression of server and sets 7z accordingly
Cleans the backup folder
Well it is not perfect, but it works. And if you have already come as far as finding this blog, it should give you a good idea of what to do. The only things that need attention is: $servers $MainFolderOnLocalhost and make sure that the 7zip folder is under $MainFolderOnLocalhost, if you have it installd, just copy and paste the folder I prefer 7zip because it works and it is free. (you don’t need more arguments than that) Hope this helps someone as much as it has helped me. Adrian