Powershell script for remote facet collection?

Question

Powershell script for remote facet collection?

finno

SSChasing Mays

Points: 608
More actions
March 20, 2012 at 4:31 pm

#270399

Hi,
Can anyone please assist me in the best way to create and run a powershell script from my machine across multiple servers (with multiple instances installed) to obtain facet information on the backup directory? There has been some changes to some backup paths (without updating the facets also) so the facet information needs validating across the environment..
any advice is greatly appreciated 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

marno (3/20/2012)
Hi,
Can anyone please assist me in the best way to create and run a powershell script from my machine across multiple servers (with multiple instances installed) to obtain facet information on the backup directory? There has been some changes to some backup paths (without updating the facets also) so the facet information needs validating across the environment..
any advice is greatly appreciated 🙂

What do you mean by "facet information needs validating". Are you asking about a Policy Management Facet, or are you using the term facet generically?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

finno SSChasing Mays Points: 608 More actions · Answer 2

Hi, thanks for the reply and sorry for not being very clear ;o)

What i need to validate is the BackupDirectory setting on the server facets (right click > view facets > server facet\backupdirectory) for all servers, i wish to run a script from my machine to obtain the values for each if possible..

Due to some storage problems there has been changes to the maintenance plans (in regards to changes of paths) but this change has not been reflected in the facets manually by those who made the ammendments so want to ensure everything is aligned..

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

OK, our individual use of terminology can be such a funny thing sometimes, I'm with you now, thanks for the clarification.

Is this going to be an ongoing need? If not, then using Policy Management with PowerShell may be overkill. If you would like to proceed down that path then this article has helped me a lot. SQL Server Policy-Based Management > Running Against SQL Server 2005 and SQL Server 2000 Ignore the fact that it has SQL 2005 and SQL 2000 in the title, the meat of the article demonstrates how to run one set of policies against multiple servers using PowerShell. You can adapt their technique for your own use if you decide to have the same policy parked on all instances.

If you just want to get a quick look at the backup directory on all your instances you can do this through SSMS. Do you have a Central Management Server setup? If not, just setup all your instances in your Registered Servers window in SSMS and then open a single query window that connects to all instances (select New Query when Folder containing all server has focus). In your multi-query window this script will be useful to show you the backup directory from all instances in one resultset.

DECLARE @MSSQLRegRoot NVARCHAR(150)= N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer',

@BackupDir VARCHAR(100);

EXEC master.sys.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

@MSSQLRegRoot,

N'BackupDirectory',

@BackupDir OUTPUT;

SELECT @BackupDir AS [Backup Directory];

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

finno SSChasing Mays Points: 608 More actions · Answer 4

Excellent,thanks a lot!!

🙂

I have all my instances registered so this should do the trick,is more of a one off exercise so this is spot on,was trying to kill 2 birds with one stone and get into powershell also (which i will take a look at your link)..

Cheers

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Good deal, happy to assist 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bruce 1565 SSCrazy Points: 2574 More actions · Answer 6

If you're still interested in a Powershell method try this.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop

foreach ($server in $(Get-Content -Path serverList.txt)) {

$srvObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server

"${server}: $($srvObject.BackupDirectory)" | Write-Output

}

The serverList.txt file would just be a list of servers, one per line.

Tested against SQL 2000 through 2008 R2.

finno SSChasing Mays Points: 608 More actions · Answer 7

most definitely!!!

thanks to both you guys for helping me out with both methods:-)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

bruce 1565 (3/22/2012)
If you're still interested in a Powershell method try this.
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
foreach ($server in $(Get-Content -Path serverList.txt)) {
$srvObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server
"${server}: $($srvObject.BackupDirectory)" | Write-Output
}
The serverList.txt file would just be a list of servers, one per line.
Tested against SQL 2000 through 2008 R2.

Very nice solution! I completely forgot about that SMO property.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato