Windows PowerShell™ is a task-based command-line shell and scripting language designed especially for automation of Windows tasks. It has become the defacto CLI for Microsoft products since the its release, and can fully leverage .NET frameworks.
15
votes
3answers
3k views
Which PowerShell technique should I use to talk to SQL Server?
I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that ...
6
votes
2answers
1k views
Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?
I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks.
So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities.
...
6
votes
2answers
883 views
powershell to get sql server memory counters and show value
I am writing a powershell script to capture the following SQL server counters:
SQL Server: Memory Manager: Total Server Memory (KB)
SQL Server: Memory Manager: Target Server Memory (KB)
My ...
6
votes
1answer
249 views
Executing Powershell SMO against a cluster with elevated privileges to update service accounts
I'm working on a Powershell script to update service accounts on named SQL instances in a failover cluster. I'm trying to use the SMO and WMI to update this so that I can update the service account ...
5
votes
1answer
201 views
SQL Server SMO Method “Discover()”
I think I just found the coolest thing every with SQL Server SMO and PowerShell. I was asked to get a script together to find the owner of every object in an instace of SQL Server. I found a script on ...
5
votes
1answer
134 views
Why am I missing some output?
Archiving to avoid ID Exhaustion
We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in ...
5
votes
2answers
637 views
SQL Agent powershell context reference
At my new job, we have multiple named instances on each servers.
Server1\Dev
Server1\DevIntegrated
Server1\QA
I have a SQL PowerShell script in the works that calls out to the OS, invokes ...
5
votes
2answers
310 views
SQL Server SMO and PowerShell formatting
I have probably been looking at this for too long to figure this out...
What is the easiest way I could get this exported to a CSV format?
Would it be better to dump this to a table in a database ...
3
votes
2answers
2k views
How to monitor SQL Server data and log file growth?
I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
3
votes
3answers
821 views
Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM)
...
3
votes
2answers
476 views
Powershell script executing within SQL Server Agent
I am trying to use Powershell within a SQL Server Agent job to download a zip file. The script uses PuTTY (PSCP.exe) to download a zip file from a SFTP site.
The issue I am having is that when the ...
3
votes
1answer
329 views
How to script out push subscription creation at the subscriber?
I'm trying to set up a push subscription to a SQL Server publication from the subscriber.
I could set up the subscription at the publisher using the Replication Wizard in Management Studio. However, ...
2
votes
4answers
242 views
What “tool” to use for monitoring instances and where to store this data?
I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server ...
2
votes
1answer
338 views
SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints
I have a .ps1 PowerShell script that runs on a Central Management Server. The script goes out to all databases in our environment, and scripts out all of of objects. For tables scripts, the output ...
2
votes
1answer
261 views
How to change SQL Server Service Account using PS
SQL Server runs under AD Account SQLAccount1. How to make it run under another account, SQLAccount2? How to do it un Powershell?
1
vote
2answers
7k views
How to get table columns with datatypes (and lengths) using PowerShell?
I'm trying to get column names with data types (and lengths) for SQL Server instance using PowerShell. I got this far:
#Load PSSnapin
Add-PSSnapin *SQL*
#Get column names
$colNames = dir ...
1
vote
1answer
252 views
Powershell Format Table Combine Values
I have an awesome script that gives vital data for running worker processes. What I'm attempting to do is to get these three values into a table so that they can be displayed all pretty-like. I ...
1
vote
2answers
2k views
Get Job id OR Job name from within executing job
I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
1
vote
1answer
406 views
powershell to connect to SQL and display a friendly message when it cannot connect
We check our SQL servers using powershell. The script goes through all servers in a text file and sends the output to a text file when it can connect. But when it cannot connect, it displays error in ...
1
vote
1answer
261 views
How to connect Powershell to the SQL Server named instance?
When I run
# Load the SMO assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$ServerName = "[local\s12]";
#Get a server object which corresponds to the ...
1
vote
0answers
86 views
SQL Server: Powershell to dump database as SQL Beginner Issues
I want to be able to dump a database to a sql text file using Powershell. I want this because our DBAs want to be able to do a diff on the file that is committed to SVN. They won't execute it if they ...
0
votes
1answer
578 views
attach database - verify - detach from a script or a batch file
for a hundred good reasons .... and I promise that at least a few are good ... I find myself with almost 200 SQL server databases (mdf,ldf files) generated by different versions of sql server, most ...
0
votes
1answer
94 views
SQLPlus: Cmd vs PowerShell
From cmd.exe, when I run:
C:\oracle\client\BIN\sqlplus.exe username@server
then type my password, I get in. However, if I run the same command from PowerShell and type the same password I get:
...
0
votes
1answer
545 views
Restore SQL Server database using Windows Powershell 3.0
I'm trying to restore a SQL Server database with a PowerShell script, but I'm having problems.
Here is the error I'm getting:
Exception calling "SqlRestore" with "1" argument(s): "Restore failed
...
