Traditionally most People have been automating database backups in Microsoft SQL server using TransactSQL, and then using osql to execute the code through Windows Task Scheduler
prior to or along with direct therapies as a key to treating How does cialis work? which may benefit selected patients to various degrees..
. With the advent of MSSQL 2012, using Powershell to automate SQL related taks has suddenly become a lot easier.
This script selects only user databases though, ignoring system databases and leaves you with only what should be important to you.
As a part of a migration at a client, I was given the task of creating an efficient backup strategy with minimal effort. Using TransactSQL would definitely solve the task, but for future reference, I always try to use Powershell when applicable.
So how does the script work? Well, first we collect paths and dates necessary to perform the backup and name the file for the backup set. The backup file is tagged with the week number and the script is meant to create a backup set every week.
Next we create two Functions
. The first creates a full backup, the second will add incremental backups to the backup set file created by the first function. I.e., the second will be pointless if the first has not run.
Last, we choose which of the previously created functions to run. This is also where you choose when to create the full backup set. Simply change the weekday in the script. Mind your spelling though, it needs to be accurate.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
<# Reidar J. Boldevin September 2014 http://www.boldevin.com #> #Collecting paths, dates, and putting them into variables $path = "D:\MSSQL\Backup\" $databases = Get-SqlDatabase -ServerInstance localhost | where IsSystemObject -eq $false $week = Get-Date -UFormat %V $year = Get-Date -Format yyyy $date = "$week"+"_"+"$year" $weekday = (Get-Date).dayofweek #Creating a function for creating a new backup set containing a Full backup Function Full-Backup { foreach ($database in $databases) { $BackupFile = $path+$database.Name+"_$date"+".bak" Backup-SqlDatabase -ServerInstance localhost -Database $database.Name -BackupFile $BackupFile #-WhatIf } } #Creating a function for doing an incremental backup to an exisiting backup set Function Incremental-Backup { foreach ($database in $databases) { $BackupFile = $path+$database.Name+"_$date"+".bak" Backup-SqlDatabase -ServerInstance localhost -Database $database.Name -BackupFile $BackupFile -Incremental #-WhatIf } } #Deciding which backup type to do #Change name of the weekday to change which day a full backup is done if ($weekday -eq "Monday") {Full-backup} else {Incremental-Backup} |
So this is all nice and dandy. It’ll keep churning out backup sets every week, filling up your storage SAN, local hard drive, cloud storage or what ever storage you choose to add the backup set to. So what can we do to clean it up?
Well, keep in mind that all backup sets are tagged with the week number in the file name. This makes it easy to choose which backup set to restore, but it also makes it easy to choose which to delete. Simply {$CurrentWeek – 2}. Here a script that does exactly that:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
#Collect path and week number $path = "D:\MSSQL\Backup\" $week = Get-Date -UFormat %V #Select which week to delete if ($week -le 2) { $week = $week+52-2 $week = "_"+$week+"_" } elseif ($week -gt 2) { $week = $week-2 $week = "_"+$week+"_" } #Delete the backup set Get-ChildItem $path | where name -like *$week* | Remove-Item -Force |
Please feel free to leave your comments if you have and questions regarding this solution, and I’d love to hear from anyone who uses this fully or partially 🙂