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

Comments

html5 converter said…
Thanks for the share. Keep posting such kind of information on your blog. I bookmarked it for continuous visit. Thanks once again.
Brian said…
Nice scripts. I'm looking for a total SQL Server solution. What would you suggest to make these script act against all databases on the server? Have you implemented something like this or do you setup the script to act on a subset of the databases on the server?
Steven Wright said…
Hi Brain, I haven't implemented a complete server maintance, However you could updated the main script, SQLServerMaintanceTask.ps1, with a array of database names and iterate through the names.
Anonymous said…
anyone able to get this working with ps 3.0?
Steven Wright said…
The scripts are usable with PowerShell 3.0. Only thing would to make sure that the SQL Sever Powershell snap-in are load.
Anonymous said…
When running the Defrag-SQLServer-Indexes script I get the error stated below. I am running this in powershell 2.0 on a 2008 R2 server with sql 2008 r2 sp3 installed. I did verify that Microsoft.SqlServer.Smo is installed... Any thoughts on how to resolve?


New-Object : Cannot find type [Microsoft.SqlServer.Management.SMO.Server]: make sure the assembly containing this type is loaded.
At C:\temp\Defrag-SQLServer-Indexes.ps1:95 char:19
+ $srv = New-Object <<<< -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList $server
+ CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
+ FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand





Popular posts from this blog

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

SSIS File handle leak in For Each Loop

Cannot update a database that has been registered as a data-tier application