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.

learn more… | top users | synonyms

0
votes
1answer
13 views

Connecting Remote Shares via Powershell

I had a big problem which I just happened to solve, but it's a permissions issue I don't quite understand. Unfortunately I'm in a tight bind and no amount of googling in the past week has really ...
0
votes
1answer
17 views

In SQL Server and using only client side encryption requests, can I use a different certificate for each client?

Based upon the UI dialogue box in SQL Server Configuration Manager, if I right click 'Protocols for server_name', it appears that I can only select one certificate to use. My experience so far tells ...
0
votes
1answer
326 views

delete WSFC 2012 cluster role/group fails

PS C:\Windows\system32> get-cluster Name ---- sql2012poc_cl1 PS C:\Windows\system32> get-clustergroup Name OwnerNode State ---...
1
vote
1answer
25 views

AOAGs State Report

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null $query = "select node_name, group_name, role_desc,operational_state_desc, synchronization_health_desc from ...
0
votes
1answer
23 views

Trying to get password has for a login through PowerShell

I've trying to get the password hash for a SQL authentication login via powershell. The code I'm executing is something like this Invoke-Sqlcmd -Query "SELECT LOGINPROPERTY('$LoginName', '...
4
votes
2answers
3k views

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. ...
3
votes
1answer
73 views

Changing SQL account password in specific database

I am trying to change the password for userId in a specific database called LionsDB. If I am changing the password manually, I would login into SQL Management Studio using that user account and update ...
0
votes
0answers
14 views

Windows Powershell - multiline indirect for psql connection

I have a script that goes like psql << EOF sql 1 sql 2 EOF I want to do the same in Windows powershell. I learned about the script block, but tried passing it in Invoke-command, but hitting a ...
2
votes
1answer
35 views

PowerShell Pipeline Binding examples on SQL server?

I am learning PowerShell, my training machine (VM) is a Windows Server 2012 R2, with SQL 2014 and PS 4.0. I am try get a handle on the two Pipeline Binding methods ByValue & ByPropertyName. I am ...
0
votes
1answer
27 views

Powershell: Does System.Data.SqlClient.SqlDataAdapter usange in powershell need some ADO.Net installation?

In PowerShell, goal is to query some data from Sql Server and put that into a preformatted Excel file. Towards that, the following PowerShell script snippet, throws an error saying "assemblies are ...
1
vote
0answers
45 views

SQL Agent job step fails after upgrading to Powershell 5

I've not seen this issue discussed on the web, and have searched far and wide. I have a brand new HP DL380 running SQL Server 2008R2 standard. It's not yet in production, and I'm testing SQL Agent ...
0
votes
0answers
24 views

How to return a user defined error code through SQL based on the error

I would like to return a user defined error code in my SQL Server stored procedures based on the outcome. For example, if no error takes place, RETURN 0. If a validation error takes place, return 20....
1
vote
1answer
33 views

How to find the 'Instance ID' for PowerShell Invoke-TroubleshootingPack?

I was doing a PowerShell training, that said use the PowerShell help to run a "Networking troubleshooting pack" I found, ran and read Get-Help Get-TroubleshootingPack -full Get-Help Invoke-...
0
votes
1answer
180 views

SA login history in sql

Is there any PowerShell script to get the SA log in history? I need to do an audit on SA login.
4
votes
1answer
271 views

How do I enable both PowerShell Remoting and SPN for SQL Server Reporting?

I'm struggling with remoting servers with SQL Server Reporting Services running. My Reporting Services are running with individual domain-accounts, and I have set up SPNs for them (HTTP/<Machine>...
2
votes
1answer
50 views

How can I detect the use of SQL Server replication on a server (all versions) preferably using PS or CMD

I am trying to find a way to detect if a customer has SQL Server replication on. This helps decide whether they can use Express (as they frequently request) and this is one of the blocking factors. ...
0
votes
0answers
30 views

Error connecting from other SQL instance using powershell script

I have a job to run a powershell script that will rebuid fragmented index through source SQL instance to target SQL instance. The poweshell script didn't run due to the following error: Sqlcmd: ...
2
votes
1answer
62 views

How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

I can make a certificate in the windows certificate store for the master column key easily enough with: $cert = New-SelfSignedCertificate ` -Subject "Always Encrypted Sample Cert" ` -...
0
votes
1answer
69 views

Powershell invoke sqlcmd

How is it possible to execute a query as a applicationintent=readonly using powershell? I have an cluster alwayson environment. I want to call a procedure with invoke sqlcmd to the readonly database ...
6
votes
2answers
319 views

Are cursors/While loops the only way to make administrative changes to multiple databases?

I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in ...
1
vote
0answers
66 views

Powershell Invoke-Sqlcmd Implicit Transaction

Does Powershell, through invoke-sqlcmd or through running a query directly through a cmdlet, wrap a query in an implicit transaction? And if so, is there a way to work around this should my query ...
0
votes
1answer
28 views

Create a file with variable name in power shell Power shell

I am trying to execute the below power shell script to script out the db in sql server. [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $s = new-object ('...
1
vote
1answer
51 views

How is powershell commonly used in SQL server? [closed]

I am wondering how is powershell used in SQL Server? I heard people saying that powershell makes a DBA's life easier. But I dont know how is powershell commonly used in SQL server. Can anyone provide ...
8
votes
2answers
3k views

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 ...
1
vote
1answer
34 views

Modifying the Contents of SQL Server Agent Alerts

As is generally recommended to do, I have Alerts configured for errors of severity 19 and greater that occur on my SQL Servers. However, as part of an email notification standardization process, I'm ...
2
votes
1answer
370 views

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?

I have a procedure to backup SSAS Databases that is working successfully. I also delete the SSAS backup files that are older than 2 days. What I am looking forward to implement is: 1) How to verify ...
2
votes
1answer
96 views

powershell problem when running on a remote server - access is denied

as a follow up from this question below: Run a PowerShell script on a different server - from inside a SQL Server job there you can see that for me tu run a powershell script from inside a sql ...
2
votes
1answer
221 views

SQL Agent doesn't have perms to run a PowerShell script

I recently upgraded SQL Server from Windows 2008 R2 & SQL Server 2008 R2 to Windows 2012 R2 & SQL Server 2014 SP1. One of the sql agent jobs fails to run. The job runs a PowerShell script to ...
2
votes
2answers
172 views

Using Powershell Variables in a File-Stored Query with SQL Server

I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell ...
7
votes
2answers
2k views

Execution-Policy Error running Powershell Script in SQL Server Agent

Running a powershell script from SQL Server Agent in 2014 using my AD account via a credential. I am getting the following error. A job step received an error at line 1 in a PowerShell script. The ...
1
vote
1answer
75 views

TSQL Script Execution Through Power shell, Special Characters Rendering Problem

We have a simple T-SQL script file (.sql), which we execute through Powershell. Everything works fine, except the script portion, where we are using some special characters as string (example: 'ô' ). ...
0
votes
3answers
559 views

Executing a script via Powershell possibility

Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it ...
3
votes
3answers
115 views

Improve Restore Speed

I have a ~3GB backup file from our QA database, and a daily diff (~500MB). For data complexity reasons, some of our developers like to use regular backups from this database, and so we have a process ...
0
votes
0answers
6 views

Need help on permission on AdvanceSharing

i have create a remove share using powershell and i want to enable everyone to have a "change" permission on the share i have create. I have used the below script to do so. But it is creating ...
2
votes
1answer
199 views

Set method to change StartMode property using SqlServer.Management.Smo.Wmi.Service

I'm trying to change SQL server Configuration manager settings using Microsoft.SqlServer.Management.Smo.Wmi.Service. Microsoft provided with a list of methods & properties here. I used the ...
4
votes
4answers
8k 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 ...
8
votes
4answers
21k 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
169 views

unable to run step 3 of syspolicy_purge_history job on SQL Server 2012

After installing an instance of SQL Server 2012 (SP3) on a server that had previously had SQL Server 2008R2 on it, I began encountering the following error while attempting to run step 3 of ...
7
votes
5answers
15k views

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 ...
6
votes
2answers
196 views

Can't get databases or its objects with powershell

I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My ...
1
vote
2answers
49 views

How do I view operators in a Notify Operator task in a Maintenance Plan?

In the past, we have added each DBA to a Notify Operator task on failure in Maintenance Plans ranging from scheduled backups to indexing and occasionally one-off operations. One of our DBAs recently ...
6
votes
2answers
1k 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 ...
6
votes
2answers
473 views

Running sp_AskBrent with @ExpertMode = 1 in PowerShell

I am trying to run sp_AskBrent from PowerShell using Invoke-SQLCmd and capture its output in a variable: $query = "EXEC SAIDBA.monitoring.sp_AskBrent @Seconds=10;" $check = Invoke-Sqlcmd -...
2
votes
3answers
18k 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 'SQLSERVER:\...
1
vote
1answer
294 views

SqlPS and PowerShell with Import-Module SQLPS different behaviors

I'm trying to write an automated installation script in powershell for SQL Server Express 2014. I'm stuck with a stupid issue when trying to enable the TCP protocol in the SQL instance. What I'm ...
4
votes
1answer
59 views

How long does it take for powershell to restore a SQL Backup?

I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name: $srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)") $...
6
votes
2answers
168 views

How to collect NTFS file properties and insert into SQL Server table

I have to provide reports on file system usage. I'm collecting statistics on file server usage down to individual file level so we can see who is using what files/folders, how much storage they're ...
-2
votes
1answer
116 views

Script to check CPU, memory, server type

Can someone please help me with the script to get CPU, total physical memory, server type(i.e. physical/VM), maxdop and max server memory details? I need it to run for 50 servers. All the servers are ...
4
votes
1answer
107 views

SQL Server Job Powershell is failing

I have a SQL Server 2014 instance job that runs nightly that moves backups from the local drive to a network drive. I wrote it using PowerShell. $mypath = $('F:\Backup\CRMDB01$CRMDB012014\Prod\Full\'...
3
votes
1answer
333 views

Using a Different Windows Account for Invoke-Sqlcmd

I'm attempting to make a Powershell script that needs to interact with SQL Server, so part of the script will involve invoke-sqlcmd. However, the script can't be scheduled as a job in SQL Server and ...