Powershell Script - Backup SQL databases to a remote drive

Question: Do you have a powershell script to backup SQL databases on a single SQL Server Instance , and direct them to a network share or a local drive? Also , I'd like an email notification with a screen output sent to my email box.

The purpose of the script is not to replace the current backup scripting but is for ad-hoc uses

Answer: There are plenty of different ways to manage the process of backing up SQL Server databases with Powershell.

In this script , the powershell cmdlet Invoke-sqlcmd is used to connect to a sql server , and execute some sql code to backup the user databases. Adjust the code according to your needs.

You'll need to configure some variables:

$target_backup_path  - use either a local drive or network share. 

$instance - an accessible SQL Server Instance , which allows access by whichever the current login is used to execute the script. 

 Part of the script includes reference to an smtp function file. To get the code read this post SQL Server – Send email using Powershell (SQL Server DBA).  Basically , you'll need to grab the function sendEmail ,place in a file called smtp.ps1 and place in an accessible path.

Alternatively , you can just lift the function code and place in this script. Generally , I like to maintain functions in separate file to allow reuse for other powershell scripts I use 

For sendEmail to work - you'll need to reference a valid smtp server and use valid email addresses. 

The email which is sent will include the the sql output messages (what you see in the “Messages” tab in SSMS) . So this script would return something like :

VERBOSE: Processed 448 pages for database 'SQL_TOOLS', file 'SQL_TOOLS' on file 1.
VERBOSE: Processed 1 pages for database 'SQL_TOOLS', file 'SQL_TOOLS_log' on file 1.
VERBOSE: BACKUP DATABASE successfully processed 449 pages in 0.084 seconds (41.684 MB/sec).
VERBOSE: Processed 392 pages for database 'Audit', file 'Audit' on file 1.
VERBOSE: Processed 1 pages for database 'Audit', file 'Audit_log' on file 1.
VERBOSE: BACKUP DATABASE successfully processed 393 pages in 0.105 seconds (29.180 MB/sec).

# #############################################################################
# SCRIPT - POWERSHELL
# NAME: SQL_Server_Backup_on_Remote_Server.ps1
# 
# AUTHOR:  Jack Vamvas - 
# DATE:  2018/08/14
# EMAIL: [email protected]
# 
# COMMENT:  This script will Log onto a  SQL Server instance and backup user databases to a destination 
#
# #############################################################################
#SQL Server Instance for backup 
$instance="MYSERVER\MYINSTANCE"
#target backup path - local or network share
$target_backup_path="\\myremoteserver\c$\tmp\"

#email variables
$emailFrom = "[email protected]"
$emailTo = "[email protected]"
$subject = "Enter subject title"
$body = ""
$smtpServer = "my_smtp_server"


#intro smtp function 
. .\modules\smtp.ps1

$QueryResult = (Invoke-Sqlcmd -ServerInstance $instance -QueryTimeout 0 -verbose -Query "
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = '$target_backup_path'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name not IN ('model','tempdb')

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  WITH COPY_ONLY

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
" | Out-Null) 4>&1
$body=$QueryResult


#Call smtp Function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer 

 Read More on using Powershell and SQL Server

Use Powershell Get-Hotfix to get all hotfixes on Windows server 

SQL Server DBA Top 10 automation tasks

Powershell script to report SQL Error Log (SQL Server DBA)