Backup SQL Databases
PowerShell script to backup all SQL databases on the local machine.
Dev Insight: This script was written to provide SQL backups to clients using SQL Express with a backup solution that doesn't natively support SQL. It is designed to be used on a schedule in tandem with a file/image based backup solution.
Prerequisites:
For this script to run successfully, the user running the script will need to have permission to perform SQL database backups on all databases, including system databases. It is recommended to run the script as
SYSTEM and set permissions on each database using the process below.Granting backup database permissions to
SYSTEM:- 1.
- 2.Navigate to Server\Instance > Security > Logins
- 3.Right-click
NT AUTHORITY\SYSTEMand choose Properties - 4.Under Server Roles, make sure
publicis checked - 5.Under User Mapping, check each database and select the following database role memberships for each:
- 1.
public - 2.
db_backupoperator - 3.
db_denydatareader(optional) - 4.
db_denydatawriter(optional)
After database permissions are set, you can deploy the script via your RMM.
Warning: Running this script will overwrite existing SQL database backups saved to the default backup location with the name
databasename.bak.Note: Unless changed manually, the default SQL backup location is
C:\Program Files\Microsoft SQL Server\MSSQL.[INSTANCENAME]\MSSQL\Backup Checking for necessary PowerShell modules...
Necessary modules installed.
Databases in localhost\SQLEXPRESS:
Name Status Size Owner
---- ------ ---- -----
master Normal 6.00 MB sa
model Normal 16.00 MB sa
msdb Normal 149.56 MB sa
Performing backup of [master]...
Performing backup of [model]...
Performing backup of [msdb]...
Backup jobs complete.
Checking for necessary PowerShell modules...
Necessary modules installed.
Backup history for localhost\SQLEXPRESS (past month):
DatabaseName BackupSetType BackupStartDate BackupFinishDate Compressed
BackupSize
------------ ------------- --------------- ---------------- ----------
master Database 2/1/2022 12:15:26 PM 2/1/2022 12:15:26 PM 3825664
master Database 2/1/2022 1:11:16 PM 2/1/2022 1:11:16 PM 3821568
master Database 2/1/2022 1:51:43 PM 2/1/2022 1:51:43 PM 3821568
master Database 2/1/2022 2:07:29 PM 2/1/2022 2:07:29 PM 3821568
model Database 2/1/2022 12:15:26 PM 2/1/2022 12:15:26 PM 2578432
model Database 2/1/2022 1:11:16 PM 2/1/2022 1:11:16 PM 2574336
model Database 2/1/2022 1:51:43 PM 2/1/2022 1:51:43 PM 2574336
model Database 2/1/2022 2:07:29 PM 2/1/2022 2:07:29 PM 2574336
msdb Database 2/1/2022 12:15:27 PM 2/1/2022 12:15:29 PM 126077952
msdb Database 2/1/2022 1:11:16 PM 2/1/2022 1:11:19 PM 126073856
msdb Database 2/1/2022 1:51:43 PM 2/1/2022 1:51:48 PM 126073856
msdb Database 2/1/2022 2:07:29 PM 2/1/2022 2:07:32 PM 126073856
Backups were not performed.
Want to learn more about how this script works? Included below are official documentation links for all cmdlets used in this script, as well as other useful documentation where applicable.
Cmdlet Documentation:
Last modified 1yr ago