Free PowerShell Scripts for Managing SQL Server

Idera has made than 20 Windows PowerShell scripts for managing SQL Server available for free download. DBAs and SQL Server administrators can use these scripts to automate many tasks, including connecting to a database, creating a database, and finding failed jobs.

You can download a .zip file that contains the 23 scripts and a Readme file from the Idera website. Here is a list of the scripts and what they do:

• Backup-SSAS: Backs up all Microsoft Analysis Server databases.
• CheckDB-MSSQL-UsingADO: Runs a DBCC against specified server instance and database.
• Connect-MSSQL-IPSQLAuth: Connects to a SQL Server machine using SQL Server authentication.
• Connect-MSSQL-IPWindowsAuth: Connects to a SQL Server machine using IP address, instance, and Windows authentication.
• CreateDB-MSSQL-UsingSMO: Creates an empty database.
• Create-MSSQLJob-UsingSMO: Creates a daily SQL job to call a PowerShell script.
• Create-MSSQL-TestDb: Loads SMO assemblies into the PowerShell process, creates an SMO Server object, creates a database object, pushes the newly-created object to the server, and verifies the creation of the new database.
• CreateTable-MSSQL-UsingSMO: Creates a table using SMO.
• Get-MSSQL-DB-UsingADO: Shows all databases for a specified server instance using ADO.
• Get-MSSQL-DB-UsingSMO: Shows all databases for a specified server instance using SMO.
• Get-MSSQL-MaxMemory: Gets the max memory property from SQL Server.
• Get-MSSQL-Port-UsingDMO: Retrieves the SQL Server port configured for use with DMO.
• Get-MSSQL-Port-UsingWMI: Retrieves SQL Server port configured for use with WMI.
• Get-MSSQL-ServerAttrib-Csv: Connects to SQL Server and outputs server attributes to a .csv file.
• Get-MSSQL-ServerAttrib-Html: Connects to SQL Server and outputs server attributes to an .html file.
• Get-MSSQL-ServerRegisterations: Exports current groups and servers.
• Get-MSSQL-Views-Csv: Connects to SQL Server and outputs selected views to a .csv file.
• Insert-MSSQL-SampleData-Csv: Inserts data from a csv file into a table.
• List-MSSQL-FailedJobs: Lists failed SQL Server jobs using SMO.
• List-MSSQL-PropertyInfo: List properties of a SQL Server instance using WMI.
• Load-MSSQL-SMO: Loads SMO assemblies.
• Start-SQLServerService: Starts SQL Server Services for the default instance.
• Stop-SQLServerService: Stops SQL Server Services for the default instance.

The accompanying Readme file notes that SQL Server 2005, PowerShell 1.0, and PowerShellPlus Professional Edition 2.0—Idera's interactive scripting environment for PowerShell—are prerequisites. However, you can use the scripts directly in PowerShell as well.

You can also download the scripts individually from PowerShell.com, which is a PowerShell community sponsored by Idera. However, Create-MSSQL-TestDb, Get-MSSQL-ServerRegisterations, Insert-MSSQL-SampleData-Csv, and Load-MSSQL-SMO aren't available through that website.

PowerShell resources

• Accessing SQL Server Data from PowerShell, Part 1
• PowerShell 101, Lesson 1: How to Get- what you need to create basic PowerShell commands
• PowerShell 101, Lesson 2: How to create pipelines and manage output
• PowerShell 101, Lesson 3: How to use PowerShell's operators and wildcards
• PowerShell 101, Lesson 4: How to properly use quotes when working with strings
• PowerShell 101, Lesson 5: How to access, create, and use variables
• PowerShell 101, Lesson 6: How to work with PowerShell's built-in drives and create new drives