Some of my projects are still in SSRS 2005 and PowerShell v1.0, so this script works and is tested on this environment only, for now.
I plan to port this to SSRS2008, PowerShell v2.0 when I get the chance.
I don’t think the code will change much; although one major change would be how I’m creating the proxy right now. PowerShell v2.0 has a cmdlet called New-WebServiceProxy.
To get this to work for PS v1.0, for now, I use New-WebServiceProxy.ps1 from http://poshcode.org/538 to create the proxy.
Steps are fairly simple
1. Create the proxy
2. Create an array of policies (ie existing users/groups for a particular report or folder)
3. Create a new policy
4. Create a new role, and add it to your policy
5. “Re”-set your policies — ex $reportserverproxy.SetPolicies($itempath,$newpolicies);
To view your SQL Server (or SSRS) reports using PowerShell, you can either use
- plain browser + URL string combo
- Report Viewer
- Web Services
The script below shows the first two options.
If you are going to go with Report Viewer (and personally, that’s my preference), you will need to download the ReportViewer redistributable package
I did a presentation today for VANPASS on PowerShell and SQL Server – thank you to those who attended! It was a great lively crowd 🙂 Thanks to Idera too – for the freebies – and to Black Ninja Software for the pizza and pop!
Here are the files as promised: VANPASS – SQL Server and PowerShell – Donabel Santos
If you have the slightest interest in PowerShell, and you’re keen on learning more about it, I strongly recommend you download this book:
Mastering PowerShell (from PowerShell.com and written by Dr. Tobias Weltner)
It is an awesome, well written 567 page book that covers PowerShell through and through. Here are the chapters:
- The PowerShell Console
- Interactive PowerShell
- Arrays and Hashtables
- The PowerShell Pipeline
- Using Objects
- Finding and Avoiding Errors
- Command Discovery and Scriptblocks
- Text and Regular Expressions
- The File System
- The Registry
- Processes, Services, Event Logs
- Windows Management Instrumentation
- User Management
- Your Own Cmdlets and Extensions
I have posted previously 3 different ways of searching for SQL Server Objects (How to Search for Columns in SQL Server ).
Here’s a fourth one!
Check out how powerful and flexible PowerShell is when you need to look for a database object. In the script below, I only search databases, tables, columns, and indexes. But in reality, really, sky is the limit!
Here is a simple script to audit your SQL Server Instance Properties.
Note that the property names are not hardcoded. We query each of these properties, and use those to display the property values.
If you prefer, you can also query directly each of the properties of an instance. If this is the case, just specify your server object and then the property name. For example:
The list of properties can be found at the end of the post.
A follow-up to my previous post: How to Restore SQL Server Databases Using SMO and PowerShell
In this post I will show you how you can restore your database to :
1. the existing database (same database name)
2. a different database (different database name, different mdf and ldf)
As with the previous post, to do a SQL Server restore in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:
Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.
Other assemblies you may want to load are:
[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null