Let me start off by saying, this is not a replacement for an Enterprise Backup Solution that should be handling the backups for Production environments. But if you are running a similar setup to my current organization, backups are not completed for DEV or QA environments. This makes for an interesting problem because these are the environments are where SQL Updates are tested. If like me, you want to make your life easier when it comes time to roll back and take a pre-backup in some instances, you might want to look at using the following Script:
The Script I am referring to is: Backup-SQLDatabases.ps1
The script allows for a number of command line arguments to be supplied to specifically target required databases for backup. The only mandatory parameters are -Path to dictate where the backups will be stored and the type of backup being taken: Full, Log, or Diff.
I will go over a few different parameter combinations and what type of backups they produce.
.\Backup-SQLDatabases.ps1 -Full -Path G:\Backup -CheckSum -Verify
The command above launches a full backup of USER databases on the locally installed SQL Server with a backup checksum and then verifies each backup. The nice thing about this is that you do not have to supply a connection string to the local instance. The script will pull instance information installed on the local machine and use it for the connection to SQL Server. There is no problem if you would like to launch this script on a remote machine, just provide a Connection String for the remote instance and provide a Path that is on the network that is accessible by the SQL Server engine service and the machine launching the PowerShell.
If no databases are specified, then all user databases will be selected for backup. If you would like to include System Databases then you are able to use the param -SystemDB which will let the script know to include those in the backup. If you would like to only backup the system database you can use the param -NoUserDB which will skip all user created databases. You can use the param -Database to specify specific databases that you would like backed up, for use if you would like one or many targeted.
The PowerShell script also leverages GridView for easy selection of specific databases. Use the param -SelectDB to have a list displayed with all available databases. Highlight the databases you would like included and select and push "Ok" to backup only the selected databases.
.\Backup-SQLDatabases.ps1 -Path X:\Backups -CheckSum -Verify -SelectDB -Full
.\Backup-SQLDatabase.ps1 -Diff -Path "\\NetworkLocation\sharedfolder" -Database -SystemDB -NoUserDB -ConnectionString "Servername,InstancePort" -Retention 14
This command creates a Diff backup of only the System Databases for a remote SQL Server Instance and attempts to remove backup files older than 14 days. This will result in no database backups as DIFF is not supported for System Databases.
.\Backup-SQLDatabase.ps1 -Full -CopyOnly -Path G:\Backup -Database "foglightdb,dbadmin,master" -SystemDB -AlwaysOn -SQLCredential sysdba
This command creates a Full Copy-Only backup for only the Databases dbadmin, foglightdb and master on the local SQL Server to G:\Backup. Runs the backup using the sysdba SQL Account. -SystemDB and -AlwaysOn must be used to make sure the databases supplied are eligible for a backup as master is a SystemDB and foglightdb is an AlwaysOn database in this example. -AlwaysOn only needs to be supplied when running the backup on a secondary AlwaysOn server.
The parameters -Script and -WhatIf do not produce any backups. -Script will create a Query log file in the same directory as the backup logs with each database scripted out in TSQL so that backups can be run manually. -WhatIf just displays the results as if the commands run without any backups occurring. This can be used if you are unsure what databases will be targeted by different combinations of parameters.
There are more parameters included that can be explored by visiting the GitHub link provided. If there is any additional functionality that you would like included, feel free to shoot me an email and I will look at incorporating it.