SQL Server maintenance via PowerShell

I was asked to help create a maintenance plan for a SQL server. I chose to implement the plan using PowerShell for the following reasons: 

• PowerShell is a procedural scripting language and the maintenance plan would have a few procedural steps and iterations through objects.
• PowerShell was also built on top of the .Net framework. This means I could make use of the SQL Shared Management Object (SMO) library within my script.
• SQL Server also has a SQL PowerShell provider, which is pre-loaded when you run SQLPS. Support also has been extended to SQL Server Agent as there is a native PowerShell, which uses SQLPS, job subsystem.
• I have been using PowerShell for a while and found it really useful; it allows you to solve problems, much more easily, than using the standard SQL tools.

A copy of my script, and related scripts, are available from my SkyDrive using the following link: OneDrive

The maintenance script, SQLServerMaintanceTask.PS1, does the following, depending of the day of execution:

• Removes the old backup files from the previous week.
• Backup the database by doing either a full backup or differential.
• Checks the fragmentation level of indexes and perform if required either the rebuild or reorganisation.
• Update any statistics which hadn't been updated.

While writing the maintenance script I also created a few reusable functions which are as follows:

• Defrag-SQLServer-Indexes: iterates through a database index objects performs the required rebuild or reorganised. (Defrag-SQLServer-Indexes.ps1)
• Update-SQLServer-DBStatistics: iterates through database statistics objects and performs an update is they are a day-old. (Update-SQLServer-DBStatistics.ps1)
• BackUp-Database / BackUp-DatabaseLog: performs a database backup / performs a database log backup. (BackUp-Database.ps1 / BackUp-DatabaseLog.ps1)
• Get-ManagedPassword / Set-ManagedPassword: functions used to create and store a password for the backup file. (ManagedPassword.ps1)

I needed to use a password to secure the backup file. Also the password needed to be stored in an encrypted manner and accessible to the PowerShell script. Some friends, from Endjin, pointed me to an article which helped me to create the functions to securely store a password for use within PowerShell: thepowershellguy.com

While working on previous projects I found I was always doing a common task / asking a common question, which was to get the file stats of a database. This task was easy to accomplish if there is only one database, but annoying if there were more than one. While working on the PowerShell SQL server maintenance script I also came up with this little function to help get the stats of all the databases on a server: Get-DBFileStats. (Get-DBFileStats.ps1)

There are some benefits using a PowerShell maintenance script:

• One it is easier to have a centralised maintenance server. As the scripts can connect to remote servers, currently only using Windows authentication, without the need to have a SQL Server Agent multi-server administration configured.
• Using a PowerShell script you're not tied into using SQL Server agent. The automation of the script can be done by using Windows Scheduler or some other scheduling component.
• PowerShell can interact with interact with the file system and SQL server within the same script.

To be to execute the script the following components would need to be installed and pre-loaded into power shell:

• Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2
• Microsoft® SQL Server® 2008 R2 Shared Management Objects.

Which is available to download from the following link: Microsoft® SQL Server® 2008 R2 Feature Pack

Also by using power shell the outputs can be stored easier to file in a number of formats: HTML,CVS or XML