Daily Checks on MySQL Server 2008 Servers through PowerShell

[Bud]

I am network administrator with my company and therefor need to handle the server application, and to perform application administration we have started using Powershell this is because we went through the guide which is provided by the powerShell and before that we heard, SQL Server 2008 provides some built-in support for executing T-SQL commands from within PowerShell and therefore i have decided to share my knowledge with you.


PowerShell is a relatively new interactive shell and scripting tool from Microsoft that combines the capabilities of the MS-DOS Command Prompt, batch files, Windows Scripting Host and even the popular Unix shells.

PowerShell includes an impressive array of commands (called cmdlets) and the ability to extend the tool with your own custom cmdlets. Probably the best feature of PowerShell is the pipeline, which allows you to execute a cmdlet and pipe its output to another cmdlet. Since PowerShell is built on the Microsoft.NET framework, the pipeline works with .NET objects rather than just text.

The first thing we have to do is obtain the directory of all the databases on an instance. Substituting the server name in the serverName part of this string and instance name (use default if it is the default instance) in the instanceName part of this string, type this command in the Windows PowerShell for SQL Server provider:

DIR "SQLSERVER:\SQL\serverName\instanceName\Databases"

add in a where-Object Windows PowerShell cmdlet to locate only those databases for which the backup is greater than a day:

DIR "SQLSERVER:\SQL\serverName\instanceName\Databases" |
where-Object {((get-Date)-($_.LastBackupDate)).days -gt 1}

You can see that the Where-Objectcmdlet is followed by a set of braces. This encloses what we want to do with the collection of items to the results of the dir command.

you are in the SQL Server PowerShell provider, each SMO library object class is now a directory, exactly like the databases we just used. Now we can type dir to gain access to those objects. One dir command and we can loop through all the objects it contains.

[Bud]

If you script out all the tables in a database, you could really take some time and processor cycles on your system. You can do it, of course, but we are just saying that perhaps you might want to take a microsecond and reconsider.

With all those warnings and repeating ourselves that you should be on a test system, here is a simple script to script out all the databases. Not all the objects in them, mind you, but the databases themselves:

DIR "SQLSERVER:\SQL\serverName\instanceName\Databases" |
forEach-object {$_.Script()}

The same command works for other containers such as tables and views and to restrict the tables with that useful Select-Object cmdlet, you Just need to add the Out-File cmdlet with another pipeline operator:

DIR "SQLSERVER:\SQL\serverName\instanceName\Databases" |
forEach-object {$_.Script()} |
out-File –FilePath C:\temp\Scripts.sql

Then to Compare-Object cmdlet you may make use of Get-Help Compare-Object, and to this you may also have the several ways to do this. The first way is just to use Invoke-Sqlcmd cmdlet that the SQL Server provider has built in. Here is a sample. As always, replace the serverName and instanceName for your system:

invoke-Sqlcmd -query "select @@VERSION" –ServerInstance serverName\instanceName

[Bud]

PowerShell has a cmdlet called Set-Location that navigates the file system, registry, etc. For instance to change to a particular directory in the file system you can use the command:

set-location c:\pstest

You can also use Set-Location to navigate in the registry; e.g.:

set-location hklm:\software\idera

You can display the contents of a location with this command:

get-childitem

I created a SQL Server Agent proxy for the PowerShell subsystem and named the proxy PowerShell. The proxy allows you to specify a credential to be used to run the job step. The alternative would be to run the job step using the credential of the SQL Server Agent service. Since that credential would typically be one with limited privileges, you may need to create a proxy so that your PowerShell job steps can run with the necessary privileges.