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
0answers
14 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: ...
5
votes
2answers
241 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
23 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
24 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
40 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 ...
1
vote
1answer
31 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
2answers
104 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 ...
0
votes
1answer
25 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
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
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 ...
3
votes
3answers
87 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 ...
1
vote
1answer
77 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
86 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 ...
6
votes
2answers
152 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 ...
2
votes
1answer
39 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" ` ...
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 ...
4
votes
1answer
50 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)") ...
-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 = ...
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 ...
3
votes
1answer
188 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
407 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
89 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? ...
6
votes
2answers
315 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 ...
1
vote
1answer
335 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 ...
0
votes
0answers
43 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. ...
0
votes
0answers
27 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
87 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
94 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, ...
1
vote
1answer
383 views

Cannot run Sql Server Agent jobs with PowerShell script

The problem: I have a customer that cannot run Sql Server Agent jobs with PowerShell script. It fails with this message: Message Executed as user: xxNC\SQLService. Unhandled Exception: ...
1
vote
1answer
54 views

Failed scheduled job [closed]

I have created 3 jobs in my MS SQL Server The job returns an error on 2. Remove top 4 lines Message Executed as user: myDomain\administrator. The process could not be created for step 2 of ...
2
votes
1answer
173 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
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
2answers
89 views

SSPI error and DotNetMethod exception while connecting to the SQL DB using powershell script

I wrote the following script with the original connection names and credentials. $SQLServer = "servername" $SQLDBName = "dbname" $SqlQuery = "select * from tbl_Employee " $SqlConnection = ...
1
vote
1answer
215 views

What can I use for the PowerShell notin operator in v2.0?

I developed a PowerShell script in v4.0 and then learned that the -notin operator isn't supported on v2.0 (which is the version that I need to run it in). What is the best alternative to the -notin ...
0
votes
0answers
51 views

SMO Not Scripting DB Objects

I have a PowerShell function included in the allusersallhost profile. It is basically scripting linked servers. When I execute the function from a PowerShell session, it scripts the linked server ...
5
votes
1answer
145 views

Passing current server name from SQL Server Agent job to PowerShell

I have a SQL Server Agent job that calls a PowerShell script in one step. And I want to pass the SQL server name and the current job-id to my script. With the job-id I have no problems. But with the ...
1
vote
0answers
353 views

how to pass the “file path” as a parameter in powershell?

I have recently added a SQL Server JOB on SERVER-A that goes to SERVER-B and backups all the SSAS databases. This seems to be fine. However when I want to delete backups older than 3 days, using ...
2
votes
2answers
868 views

PSQL not executing SQL command - Powershell

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the ...
0
votes
2answers
127 views

PowerShell script to access Oracle system views [closed]

I am using PowerShell script to access oracle 11g's system views as SELECT COUNT (*) AS COUNT FROM v$session but I keep getting "ORA-00942: table or view does not exist" I have created a user with ...
2
votes
1answer
163 views

Powershell - SQL Server - connectionstring in loop for multiple Instances

Thisis part of a bigger script which finds DatabaseFiles on a SQL Server machine (multiple instances). Following should just return all files for 3 instances. The server is called V3000801 and ...
1
vote
1answer
107 views

SQL Server AS: Set ImpersonationMode via PS

Try to create a database in SQL Server Analysis Server (SQLAS, SSAS) version 2012 SP2 if that matters and set Impersonation Mode via Powershell. (try to avoid XMLA script). Here are my attempts. Does ...
1
vote
1answer
336 views

Powershell function to drop table(s) from SQL Server database [closed]

### List of tables that require full sanitizing $requiredTables = 'table1','table2','table3' foreach ($table in $requiredTables){ function Delete-FromTable{ $SqlConnection = New-Object ...
3
votes
1answer
268 views

Backup-SqlDatabase: Cannot bind parameter 'InputObject' Error Being Thrown

I have a PowerShell script that is used in a fairly complex automated process. One thing it does is creates a database backup, and for that we are using the Backup-SqlDatabase cmdlet. I am also using ...
0
votes
3answers
458 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 ...
2
votes
1answer
65 views

Can't create an Availability Group with Powershell when instance uses non-standard port

I am trying to create an Always On Availability Group with PowerShell, something I have done many times before but am hitting an issue now that I am using non-standard ports for the SQL Instance I ...