Hot answers tagged powershell
6
Aaron Bertrand wrote a good post on it that is pretty detailed...How I use PowerShell to collect Performance Counter data.
Then Laerte Junior has an excelent walk through on how he finds the counters he wants in a Simple-Talk article: Gathering Perfmon Data with Powershell. This might be where you want to start. It has some cmdlets that he uses to capture ...
6
I don't know that anyone has written something along the lines of Ola's script within PowerShell. I know that PowerShell scripts for SQL Server are slowly growing over at the TechnNet Script Center. Then MSSQLTips.com also has started publishing tips working with SQL Server and PowerShell.
Then you also have SQL Server PowerShell Extenstions project over on ...
5
You cannot loop through objects in the way you have coded. Here's the working code:
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
$ServerName = 'xyzabc123'
$DatabaseName = 'test1'
$TableName = 'main'
$TableSchemaName = 'dbo'
$Server = New-Object ...
3
If you want to use PowerShell you can easily create task scheduler jobs in Windows to run the PowerShell scripts which would dump the output into a table or log file.
If you want to use SQL you can easily create SQL Server Agent jobs to pull the data from the DMVs or DBCC and dump them in a table.
It is difficult to understand exactly what you would want ...
3
I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine.
#SQL 2008 R2
#add-type -AssemblyName ...
3
Figured it out!
The key here was piping it out to an array successfully. I've had a lot of drawbacks here but you've got to work thru them.
$pids = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\id process" -and $_.path -like $filter} | select cookedvalue | ForEach ...
3
Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations.
I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older ...
3
If you look in SQL Server BOL, SQL Server Agent provides a set of "tokens" that it will substitute into both the job step command text and the output file (the later will prevent the GUI "view" button from working). These tokens seem to work for any type of step except T-SQL.
http://msdn.microsoft.com/en-us/library/ms175575.aspx
So, if you have a SQL 2008 ...
3
The lengths are found at <Column>.Properties['Length'].Value, so you can select it like:
#Get column names
$colNames = dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' |
Where-Object {$_.DisplayName -match "dbo.MYTABLE"} |
ForEach-Object {$_.Columns} |
Select-Object Name, DataType, `
...
2
I have been thinking about a similar solution in our own environment. My ideas have been gearing towards the last two options (ssis & powershell). By having a single instance of your admin database you will be able to generate one report for all servers and do comparisons among your different instances.
The reason I have been leaning towards ssis or ...
2
You need to try/catch the exception.
$OutputFile="C:\output.txt"
$HostName="MyServer"
try
{
$con="server=$HostName;database=master;Integrated security=sspi"
$da=New-Object System.Data.SqlClient.SqlDataAdapter ("
declare @ServerRestartDate datetime
set @ServerRestartDate = (select crdate from master..sysdatabases where ...
2
Sad to say I have not done much with PowerShell scripts being called inside SQL Server. Nor am I at a computer that I could play with it right now.
I believe though instead of using the PowerShell type step that if you used CmdExec and just call your script as you would from a command line "powershell 'MyScript.ps1'" you could then pass a parameter that has ...
2
Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little. Add this after the line setting $size = $_.Size
$sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)"
$sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate ...
2
This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs.
# Get database files from where ever they are
$databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf"
$sqlCmds = @()
# Parse db names from file ...
2
You will have to use tokens in your job steps to get your own job id. Details here: Using Tokens in Job Steps.
At the end of the article there's one example with jobid:
SELECT * FROM msdb.dbo.sysjobs
WHERE @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;
2
Your first problem is that Putty/PSCP want to store the host key for each user for security reasons (stop Fred from storing a fake host key that can be used to con George into trusting a fake server). The -batch option won't override that as it's seen as a flaw in the security process.
So it's fine when you run it interactively, as you can accept the key ...
1
Arg, found the problem. My username is ops$account and PowerShell treats $ as a start of variable token. Because $account was null the username I was passing was just "ops". I fixed the issue by escaping the $ with the backtick, i.e.
C:\oracle\client\BIN\sqlplus.exe ops`$account@server
1
$ServerName = "[local\s12]";
Remove the square brackets.
Apart from that, the PowerShell code looks fine (and works on my test machine - the code I used is below, copied from my SQL Server test machine SERVER1).
PS C:\Users\Administrator.ADVENTURE-WORKS> [void][reflection.aSSEMBLY]::LoadWithPartialName("Microsoft.SQLServer.Smo" )
PS ...
1
I ran into the same problem, looks like its an SMO bug (1st link) (however I have not tried the CredSSP stuff from the second link below)
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/265ee700-182d-4a99-96f8-cada117fd3d3
http://www.ravichaganti.com/blog/?p=1230
Please let me know if that works..
1
I'm going to outline a T-SQL way that uses most of what you already have.
Create a table to hold your publication information (for the solution that I have written, it's just the name of the publication and an identity column)
Create a table to hold your subscriber information (in mine, I have name of subscriber, subscriber database, identity column, and a ...
1
I tend to lean toward using SQLPS if I can. It is simpler and if I use it in scripts is much easier to read and less typing than trying to use SMO. SMO does it have its place in that it has a good bit of power, but can be confusing at times to use if you are unfamiliar with it.
I think as SQL Server versions are released the SQLPS will be improved upon. ...
1
For SQL server 2008 you can use the new Data Collector feature. More on this at this link.
You may need the Disk usage collection set. Nice graphical reports can be created if you use the feature that is already built in and free.
To see how to create reports on Disk usage collection set, check out this link.
You asked and Microsoft provided (joke).
1
What data are you wanting to collect? If your are just looking for free space, backups checks etc.. and have an instance of SQL Server 2008 handy you could use Central Management Server and Policy Based Management and PowerShell. I did a talk about this at SQL PASS Member Summit and SQL Rally this year. You can grab my resources here
Personally, I also ...
1
In the following part of the INSERT query:
('"+ $Name + "', " + $FreeSpace + ", " + $Size + ")"
If either $FreeSpace or $Size -eq $null, then it won't properly complete the query string. Either use command parameters just as you would in .NET (best method) or check for $null before insert.
Only top voted, non community-wiki answers of a minimum length are eligible
