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:
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"
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.DIR "SQLSERVER:\SQL\serverName\instanceName\Databases" |
where-Object {((get-Date)-($_.LastBackupDate)).days -gt 1}
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.
Bookmarks