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

1
vote
0answers
16 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
0answers
19 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 ...
0
votes
1answer
24 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 ...
0
votes
1answer
39 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 ...
0
votes
1answer
108 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.
0
votes
1answer
239 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
29 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
301 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 ...
0
votes
0answers
34 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" ` ...
2
votes
1answer
60 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
169 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
103 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
54 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
456 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
86 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
180 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
7k 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
18k 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
74 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 ...
3
votes
1answer
81 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 ...
7
votes
5answers
13k 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
149 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
33 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
979 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
312 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 ...
1
vote
1answer
258 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
49 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
121 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
82 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
70 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 = ...
3
votes
1answer
183 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 ...
7
votes
1answer
393 views

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 ...
2
votes
1answer
316 views

Backup SSRS Encryption Keys Using Powershell

I have made the following script from google for backing up the SSRS Encryption keys: cls $pwd = "sa@123@123" $SSRSClass = Get-Wmiobject -namespace ...
2
votes
1answer
87 views

Script file execution through Power shell

We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell. ...
0
votes
2answers
34 views

SQL Service Account creation in AD using PowerShell [closed]

I'm wondering if it's possible to create a service account remotely from the SQL box using PowerShell, and put the service accounts created in the OU (folder) they belong in AD as part of a unattended ...
1
vote
0answers
72 views

Powershell To Run SQL Case Update [closed]

This is my syntax which throws an error of incorrect syntax near abcdefg. Can someone who is more experienced in the world of powershell take a gander and let me know how to remedy my issue? ...
1
vote
1answer
333 views

Trying to use SMO Transfer Object to transfer a database from one instance to another

The schema tables are owned by dbo and another schema name. The code below is not moving over any tables. Is there something else that I need to add to make it transfer the tables? There are no stored ...
1
vote
1answer
1k views

Call Powershell script from another Powershell script

I have a problem. I develop some powershell scripts to perform actions on my Databases. In theory this PS should execute when them are called by the main powershell like the calls in .bat files. My ...
5
votes
2answers
291 views

How can I speed SQLPS connection when I don't have WMI permissions at the server

When connecting SQLPS to a SQL Server, it will try to run some WMI service status queries. In an environment where one does not have permissions for those, PowerShell issues a bunch of warnings, and ...
0
votes
0answers
42 views

Modify Script to not include *_snapshot_* databases

We use the script on the website below to script out replication. I would like to add a exclusion of databases that have a name _snapshot_ in the name of the database. ...
1
vote
1answer
324 views

Powershell Restore Azure SQL Database to local with Restore-Database

I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac ...
0
votes
1answer
175 views

Help with SQLPSX output for replication

I am using the CodePlex project SQL Server PowerShell Extensions (SQLPSX) to generate script files for replication. Everything is working as I want except for one thing. I have looked through the ...
0
votes
0answers
26 views

Error scripting data using SQLPSX Powershell?

I'm trying to script the data using some of our Lookup tables. We've used SQLPSX in the past for other scripting just fine but can't get scripting data to work. Other ScriptiongOptions work just fine. ...
4
votes
3answers
1k views

Correct way to stop SQL Server (through automation)

Question Is it safe to start & stop SQL's services using PowerShell's Start-Service and Stop-Service commands? If not, is there a recommended way to start & stop SQL's services through ...
1
vote
1answer
86 views

Run PS-Script after job is done on 3 instances - SQL Server

I have backup jobs running on a server with 3 instances. Link to script: https://ola.hallengren.com/sql-server-backup.html The solution I found for the other 2 servers with only once instance was ...
0
votes
2answers
92 views

How to connect to a named SQL Server instance without specifying its name? [closed]

Is there anyway I can connect to a SQL server that has no default instance, using just the server name? I've tried using SQL CMD, but it seems that the named instance name is required to login, ...