Powershell Full Database and Transaction Log Backup Script (works with SQLExpress)

Loosely based off of other examples I found, this script will perform full database backups of all databases or tran log backups of all full/bulk-logged databases.  In addition, it will utilize the default backup path (if one is not given), and delete old backup files (that conform to the file naming convention) that are older than a given number of retention hours (default to 48 hours):


Full backup to a given path, retaining only 26 hour old backups on disk

. .\backup.ps1 SQLINSTANCE\Name full 26 "C:\some\path"

Log backup (to the default instance configured backup path), retaining default 48 hours of logs on disk

. .\backup.ps1 SQLINSTANCE\Name log

Assumes that the given windows user has necessary permissions to perform backups.  Compression is not specified, so will assume instance default config

Create local admin user with password set to never expire

The following script will create a local computer users in windows, set it’s password, mark the password to not expire and optionally set the description:

Check Mirror and Witness Status Before Rebooting

The following script, meant to run as part of a pre-check for automated reboots, will check that all mirrored databases are in a Synchronized status and the witness is in a Connected status.  If the above conditions are true, the script returns a 0 exit code, otherwise, it returns < 0.

See http://msdn.microsoft.com/en-us/library/ms189902.aspx for more on mirroring quorum.

Add User/Group to Local Administrators Group

The follow will prompt to add a given domain user/group to local admins on multiple boxes