Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Free PowerShell Scripts for Managing SQL Server
Scripts help automate more than 20 tasks
Karen Bemowski
November 23, 2008
2 Min Read
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
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
