My PowerShell command to backup SQL server

Below is my PowerShell code to backup SQL servers. This will create a folder in the destination with the ServerName, then a subfolder with the date, and then a subfolder with the hour. You can backup a single database or all of them. You must have the PowerShell SQL snap ins installed:

Add-PSSnapin -name SqlServerProviderSnapin110 -ErrorAction SilentlyContinue
Add-PSSnapin -name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue

Maybe this code will help someone:

Function JBMURPHY-SQL-BackupDB() {

if ($All){
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases")
elseif($DatabaseName -eq $NULL){
write-host "You must use the -DatabaseName parameter"
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases WHERE name = '$DatabaseName'")

foreach ($db in $DatabaseName){
    $folderDate=$(get-date -uformat "%Y-%m-%d")
    $folderHour=$(get-date -uformat "%H")
    new-item "$Destination\$ServerName\$folderDate\$folderHour" -type directory -force
    $sqlcmd="BACKUP DATABASE [$dbName] TO DISK = N'$Destination\$ServerName\$folderDate\$folderHour\$dbName.bak' WITH NOFORMAT, NOINIT,  NAME = N'$($dbName) FullBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
    Write-Host $sqlcmd
    invoke-sqlcmd -query "$sqlcmd" -Server $ServerName -QueryTimeout 1200


Comments are closed.