Powershell script to backup all user databases on current SQL server
This script will backup all user databases on the current SQL server. The Input region accepts values for 2 variables:
$Destination ==>This is the folder where backup files will be stored
$Retension ==> Number of days to keep backup files. Backup files older than this will be deleted. If you don’t want any files deleted, make this number high like 99999
The script creates a folder for each database under the $Destination folder. Backup file name contains the database name and date/time of backup. For example, for database ‘INM’, backup file would be ‘H:\SQL-Backup\INM\INM-20160605_081237AM.bak’
Script returns an object collection. Each object has the following properties:
- ServerName
- DatabaseName
- RecoveryModel
- BackupDateTime(yyyyMMdd_hhmmsstt)
- BackupDuration(hh:mm:ss)
- BackupFileSize(MB)
- BackupFileSize
Sample script output:
Also see script to list SQL Recovery Model of all databases on current server, script to modify and list SQL Recovery Model of all databases, and this related post.
November 2014 update rollup for Windows RT/8/2012 breaks VSS backup – hot fix
November 2014 update rollup for Windows RT, Windows 8, and Windows Server 2012 appears to have some issues that cause VSS based backup to break.
Error messages have been ovserved like:
EventId 12366 – VSS – An unhandled exception was encountered while processing a VSS writer event callback method. The VSS writer infrastructure is in an unstable state.
and
EventId 2034 – MSExchangeRepl – The Microsoft Exchange Replication service VSS Writer when processing the backup completion event.
Microsoft has acknowledged the issue and released hot fix KB2996928.
Sam's Corner 