Use Powershell To Backup SQL Database Remote

This is a Powershell script I’ve used for a few years that creates a dated folder and places a backup for a single database in it. This version uses the “Invoke-Sqlcmd” and you may need to install the module before using this script.

Install-Module sqlserver
Get-Command -ModuleName sqlserver

You only need to change the variables at the top of the script to execute it. Be sure to include the trailing slash for the backup folder path. There should be no problem using a shared or network folder or a mount point but be sure that you don’t end up with paths like:

\\path\name\\too\may\\slashes\\

Also, do not add square brackets to the database name as the brackets are added as a default in the final SQL statement.

The date used for the folder name and file naming will be current but this can be changed by to something static or add/subtract days by changing the Get-Date command.

$path = "D:\DB_Backups\"
$sqlinstance = "Server\SQLinstance"
$database = "My DB Name"

## define the dated folder path 
$date = Get-Date -Format 'MM-dd-yyyy'
$fullpath = $path+$date

## check if the folder already exists and create it
if (-not ( Test-Path $fullpath)) { 
    New-Item -Path $path -Name $date -ItemType "directory"
}

$fullpath = $fullpath + "\"  ## add trailing slash
$time = Get-Date -Format 'HHmmss'  ## add time for the file name
$datetime = $date + "_" + $time

$backupstmt = "
BACKUP DATABASE [" + $database + "]  
TO  DISK = N'" + $fullpath + $database + "_" + $datetime + ".bak'
WITH NOFORMAT, NOINIT,  
NAME = N'" + $database + "-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10"

## Execute the statement
Invoke-Sqlcmd -Query $backupstmt -ServerInstance $sqlinstance

Backup A Single Database

Here is another script for backing up a single database using the powershell command “Backup-Sqldatabase” instead of straight SQL. There is little difference between this and the previous but I prefer the SQL statement because I can change the backup command easily.

$path = "D:\DB_Backups\"  ## need trailing slash
$sqlinstance = "Server\SQLinstance"
$database = "My DB_Name"


## define the dated folder path 
$date = Get-Date -Format 'MM-dd-yyyy'
$fullpath = $path + "\" + $date + "\"

## check if the folder already exists and create it
if (-not ( Test-Path $fullpath)) { 
    New-Item -Path $path -Name $date -ItemType "directory"
}

$datetime = Get-Date -Format 'MM-dd-yyyy_HHmmss'  #change date to include the time for filename
$fullpath += $database + "_" + $datetime + ".bak"
Backup-SqlDatabase -ServerInstance $sqlinstance -Database $database -BackupFile $fullpath

Backup All Databases

In this final script we are backing up all of the databases on the instance and getting the list of databases from the master.sys.databases table. This script uses both commands, Invoke-sqlcmd and Backup-SqlDatabase to get the list and do the work. You can also modify the first script to do the same thing using only SQL statements.

$path = "D:\DB_Backups\"  ## need trailing slash
$sqlinstance = "Server\SQLinstance"

## define the dated folder path 
$date = Get-Date -Format 'MM-dd-yyyy'
$fullpath = $path + "\" + $date + "\"

## check if the folder already exists and create it
if (-not ( Test-Path $fullpath)) { 
    New-Item -Path $path -Name $date -ItemType "directory"
}

$datetime = Get-Date -Format 'MM-dd-yyyy_HHmmss'  ##date for filename 

## get the list of DB's
$alldb = Invoke-Sqlcmd -Query "SELECT name FROM master.sys.databases" -ServerInstance $sqlinstance

## run a backup for each DB
Foreach($db in $alldb) {  
   
    # $db.name
    $backuppath = $fullpath + $db.name + "_" + $datetime + ".bak"
    Backup-SqlDatabase -ServerInstance $sqlinstance -Database $db.name -BackupFile $backuppath

}