Interactive Powershell Script to Backup A SQL Database

I created a small interactive script/tool in Powershell that leverages SQLPS, takes user input, and then creates a SQL database backup either to the default location in SQL or a location specified by the user. For this script to work the user running it must have the ability to backup a SQL Database or run it as an account that does.

SQLPS is required for this script to work. If you need instructions on how to install SQLPS, they can be found here.

The tool prompts for input because I work with many different clients with different server names and locations to backup to. You could fully automate this if you’d like.

When creating this script, the goal was to be able to create backups for my use without having to launch SSMS and click through the menus.

By default, the script creates a “copy only” backup, with compression, and a checksum to verify the backup. Options can be modified to fit your needs. More information on available flags can be found on Microsoft’s site under “Backup-SqlDatabase”, which I’ve linked.

The script will also display the machine name, Windows Version, available free space of attached disks, SQL Server status, and the default location SQL is backing up to. For a more in-depth explanation of how the listing hardware portion of the script works, please check out my earlier post, found here.

Without further ado, here’s the script:

Clear-Host
Start-Transcript -Path ".\SQLDBBackup.log" -Force -ErrorAction SilentlyContinue

function GetResources{ 
    $computername = $env:COMPUTERNAME
    $ProductName = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion' -Name ProductName).ProductName
    $disks = get-wmiobject -class "Win32_LogicalDisk" -Filter "DriveType = '3'" | Select-Object -Property DeviceID, @{L="Size (GB)";E={"{0:N2}" -f ($_.Size/1GB)}}, @{L="Free Space (GB)";E={"{0:N2}" -f ($_.FreeSpace/1GB)}}
    Write-Host -ForeGroundColor green -BackgroundColor black "Name:" "$computername"
    Write-Host -ForeGroundColor green -BackgroundColor black "OS:" $ProductName
    Write-Host -ForeGroundColor green -BackgroundColor black "Disks:"
    $disks | Format-Table | Out-String | Write-Host -ForeGroundColor green -BackgroundColor black
}
GetResources

function SQLBackupTask
{
	Import-Module SQLPS
	# prompts for the name of the SQL Server and test connection
	$SQLServerInstance = read-host -prompt "What is the name of the SQL Server instance?"
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
	$sql = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLServerInstance
	if ($sql.Status -eq $null)
	{
		Write-Host -ForeGroundColor Red -BackgroundColor Yellow "Unable to connect to $SQLServerInstance, please check connection."
		Exit
	}
	Else
	{
		Write-Host -ForeGroundColor Green "SQL Connection" $sql.Status
		#Find SQL Server Default Backup Location
		$SQLBackupDirectory = $sql.Settings.BackupDirectory
		# prompts for the name of the 3E Database to backup
		$SQLDBToBackup = read-host -prompt "What is the name of the SQL Database you'd like to backup?"
		# prompts for the storage location of the backup, which can be a network drive
		$SQLBackupTaskLocation = read-host -prompt "Input destination location or leave blank for default SQL Location ($SQLBackupDirectory)"
		if ([string]::IsNullOrEmpty($SQLBackupTaskLocation))
		{
			try
				{
					# the SQL Powershell backup command to actually make the backup.
					$DBBackupDate = Get-Date -format yyyy-MM-ddTHH-mm
					Backup-SqlDatabase -ServerInstance "$SQLServerInstance" -Database "$SQLDBToBackup" -BackupFile "$SQLBackupDirectory\$SQLDBToBackup-$DBBackupDate.bak" -CompressionOption On -CopyOnly -Checksum
					Write-Host -ForegroundColor Green "$SQLDBToBackup saved to $SQLBackupDirectory."

				}
				catch
				{
					Write-Host -ForeGroundColor Red -BackgroundColor Yellow "Unable to Backup SQL Database, check database connection."
					Exit
				}
		}
		Else
		{
			if (Test-Path -path $SQLBackupTaskLocation)
			{
				try
				{
					# the SQL Powershell backup command to actually make the backup.
					$DBBackupDate = Get-Date -format yyyy-MM-ddTHH-mm
					Backup-SqlDatabase -ServerInstance "$SQLServerInstance" -Database "$SQLDBToBackup" -BackupFile "$SQLBackupTaskLocation\$SQLDBToBackup-$DBBackupDate.bak" -CompressionOption On -CopyOnly -Checksum
					Write-Host -ForegroundColor Green "$SQLDBToBackup saved to $SQLBackupTaskLocation."
				}
				catch
				{
					Write-Host -ForeGroundColor Red -BackgroundColor Yellow "Unable to Backup SQL Database, check database connection."
					Exit
				}
			}
			Else
			{
				Write-Host -ForeGroundColor Red -BackgroundColor Yellow "Unable to Backup SQL Database, path entered not valid."
				Exit
			}
		}
	}
}
SQLBackupTask

Stop-Transcript -ErrorAction SilentlyContinue

Right now, the script will exit if it is unable to connect to the specified SQL Server. A future version may have the script prompt for the server name to be input again, but for now, this works!