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.

June 5, 2016 | Categories: Backup and Data Protection, Powershell, SQL | Tags: backup, Powershell, Script to backup all user databases, SQL | Leave a comment