Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [powershell]

Windows PowerShell™ is a task-based command-line shell and scripting language designed especially for automation of Windows tasks. Questions using this tag should restrict themselves to interactions directly with the database tier. General PowerShell scripting questions should be directed to StackOverflow.

Resources
TechNet Windows PowerShell
TechNet Scripting Center: Scripting with Windows PowerShell
TechNet Wiki: Windows PowerShell Survival Guide

With the release of SQL Server 2008, SQL Server PowerShell was introduced. It is a powerful tool that can be used to automate many repetitive tasks. You will also see some folks that use it for deployment and to standardize tasks/objects across many servers.

PowerShell is also able to use .NET frameworks, which is especially handy for SQL Server when using the SQL Management Objects API. Some starter links on SMO are here:

Basic interface
More in-depth look at SMO objects
MSDN guide to overall SMO Object Model

SQL Server PowerShell offers some information regarding use of PowerShell within the context of SQL Server.

Posting Questions
As always you can read through the FAQ to get general ideas of how to ask your question. Any question you do post regarding PowerShell on this site should involve SQL Server in some manner. This can include working with SQLPS or SQL Server Management Objects (SMO).

388 questions
30
votes
3 answers

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 PowerShell can actually talk to SQL server. Not sure if…
BradC
  • 10,073
  • 9
  • 51
  • 89
18
votes
1 answer

Connect to LocalDB via SQLCMD in PowerShell

I'm trying to access the "localdb\MSSQLLocalDB" server on my computer through PowerShell with the SQLCMD utility. I'm using PowerShell v5, .NET v5.0, and the server name is (localdb)\MSSQLLocalDB when I connect to it in Microsoft SQL Server…
jmk22
  • 305
  • 1
  • 3
  • 6
16
votes
5 answers

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 that time, etc. The info is then exposed via SSRS…
CleanFill
  • 531
  • 1
  • 3
  • 9
15
votes
5 answers

What is the most effective way to discover all running instances of SQL Server using PowerShell?

I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell methods of finding these instances, but neither seem…
Elsimer
  • 365
  • 1
  • 4
  • 13
14
votes
2 answers

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. e.g. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes Foo.exe but needs to pass a command line parameter…
billinkc
  • 16,143
  • 4
  • 54
  • 89
13
votes
2 answers

SQL Agent - PowerShell step "syntax error"

I have the following code setup in SQL Agent Job step that is not running. The message received is simply: Unable to start execution of step 1 (reason: line(46): Syntax error). The step failed. The duration of the job is: 00:00:00 Line 46 of this…
user507
12
votes
1 answer

In Powershell Retrieving Printed Message From SSMS

Our DBA team doesn't verify the backups using the below TSQL (which can easily be done post-backup and takes almost no time, so I don't get why not): RESTORE VERIFYONLY FROM DISK = 'D:\Backups\LOCATION' They've had issues in the past, so even…
Question3CPO
  • 561
  • 1
  • 8
  • 13
11
votes
1 answer

Identify root cause for why database is stuck in RESTORING state

I know there are questions which solve the issue of a database being stuck in RESTORING state and have used those solutions to manually bring the database back online, but my scenario is somewhat different. I have an automated restore using…
Radu Gheorghiu
  • 983
  • 2
  • 13
  • 27
11
votes
1 answer

Is it normal for Install-Module SqlServer to clobber existing modules?

I have a windows 10 box with SSMS 17. I had SSMS 2016 installed, but it is now uninstalled. When I go to install the SqlServer module I get this warning: C:\WINDOWS\system32> install-module sqlserver -Scope AllUsers Untrusted repository You are…
Justin Dearing
  • 2,717
  • 6
  • 36
  • 52
9
votes
2 answers

PowerShell Script To Identify Databases

I have enough PowerShell skills to get off the ground, but have none with SQL server. Can someone please point me in the right direction of how to use PowerShell to get information on remote SQL servers to identify what databases are running on…
The Woo
  • 191
  • 1
  • 1
  • 4
9
votes
2 answers

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 machine has 3 instances of SQL servers, so I want this…
Manjot
  • 1,213
  • 2
  • 21
  • 29
9
votes
2 answers

Error running Powershell Script in SQL Server Agent

I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name of the folder that the application is in (the…
Greg
  • 3,292
  • 5
  • 33
  • 57
9
votes
3 answers

How can I install the SQL Server PowerShell module on an offline machine?

I have some SQL Server machines that cannot connect to any external sites. I have been using PowerShell more and more when managing databases, so I definitely need this module to be installed so that I can use my PowerShell routines. It has been…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
9
votes
2 answers

Enable SQL server agent service by T-SQL script or PowerShell

Can we enable/disable SQL server agent service by T-SQL script or PowerShell/cmd?
Puskar
  • 191
  • 3
  • 4
  • 15
9
votes
2 answers

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. What I'd like to know is if there is an official…
ivanmp
  • 2,323
  • 3
  • 24
  • 36
1
2 3
…
25 26