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

2
votes
3answers
100 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 ...
3
votes
2answers
125 views

How does the PowerShell SQLPS SQLSERVER provider detect central management servers in SQLRegistration\Central Management Server Group

I'm experiencing odd behavior with the PowerShell SQLSERVER provider and the SQLRegistration\Central Management Server Group directory. Below are the behaviors. I would like to know how the SQLSERVER ...
3
votes
0answers
54 views

PowerShell Move-Item command made SQL and Windows entirely unresponsive

A LUN expansion completed overnight on our 15k FibreChannel VMAX SAN from 4 to 13 terabytes. This morning I attempted to move a 4.5 terabyte file from a temporary SATA LUN to the newly expanded LUN ...
2
votes
1answer
55 views

Detached database file permissions

I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by ...
2
votes
1answer
51 views

SQL Server 2008 R2 - remove line below heading (which has ----,----) - dont have SSIS

I have found this line of code online sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv" This is used in a SQL job, to export to a CSV file. ...
2
votes
1answer
82 views

Iterating over the output of a SQL query with PowerShell

I have about 300 databases, (most) with a table, [File], and a column [Location] that holds a UNC path to a file. While migrating SANs, some needed files may not have been copied. This issue ...
3
votes
2answers
87 views

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 ...
0
votes
1answer
50 views

Why do my backup policies fail when a database's guest user is disabled?

I'm attempting to implement Policy Based Management on a SQL Server 2012 instance also acting as a Central Management Server using the Enterprise Policy Management Framework. The job running the ...
1
vote
1answer
27 views

PowerShell 'get-ClusterQuorum' output is not showing 'QuorumType' column

PowerShell cmdlet 'get-ClusterQuorum' output is not showing "QuorumType" column. Has anybody seen this before? Thanks
2
votes
1answer
122 views

SQL Server xp_cmdshell: account's access changed - how to avoid restarting the service?

A T-SQL script is running an xp_cmdshell call to stop a local service through Powershell's Stop-Service, say, like this: exec xp_cmdshell 'powershell Stop-Service -InputObject (get-Service -Include ...
3
votes
1answer
729 views

How to get server cpu, memory, network usage from SQL Server

For reporting purposes, I want to get SQL Server box CPU, memory, network (not SQL Server process alone) and show it in report. Do you have any idea how to pull this? My initial thoughts are run wmi ...
0
votes
1answer
31 views

Remove Cluster Nodes after Cluster Object was deleted

While building new 2012 servers to move to we had some change in requirements. We are using Availability Groups on the new servers. Here's where we started. Servers: Serv1a Serv1b Serv2a Serv2b ...
2
votes
0answers
251 views

Powershell Get-Counter not returning values for SQL counters

I am attempting to use PowerShell to get some SQL counters but it doesn't seem to return the SQL specific counters anymore. I am not sure what could have changed on my server that would have caused ...
3
votes
1answer
219 views

Why can't I script out CLR assemblies with SMO in Powershell?

I am creating a Powershell script to read all objects in a source database, script them and then re-create them in an empty destination DB (may be across different SQL Server versions). For the most ...
0
votes
0answers
64 views

Enterprise Policy Management Framework Issue

I am attempting to setup and configure EPM to work in our environment. I have all the components in place and when I run the powershell it completes without reporting any errors. However, the only ...
0
votes
0answers
172 views

Powershell Error Handling W/ SQL Restore Jobs

I would like to rebuild many of our SQL scripts into Powershell to further integrate my department with the Windows Operations team and to open up a lot of other options. Questions: 1-preference ...
0
votes
1answer
80 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 ...
2
votes
2answers
861 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. ...
2
votes
1answer
131 views

Comparing Agent Jobs on Two Servers

I'm currently building a process in Powershell where we can copy SQL Server Agent jobs from one server to another server just by entering the server names. The script uses the SMO library to obtain ...
0
votes
0answers
1k views

SQl Server 2012 Powershell script to generate database with schema and data

Using Sql Server 2012, could somebody be so kind and show me a simple powershell 3 script that generates my entire database (schema and data)? I tried this one: $Filepath='d:\b\t' # local directory ...
3
votes
2answers
1k views

Using SQL Server SMO with a Port number

In SSMS if you are connecting to SQL Server instance set to specific port other than 1433 you simply put ",port number" after the instance name. In the same regard if I am using ...
2
votes
1answer
389 views

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

Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)? [duplicate]

I am having dozens of MSSQL Servers 2012 R2 on Windows Server 2008 R2 with similar setup, configuration (in virtual machines) in production. Neither has SSIS installed. I have to similarly maintain ...
0
votes
4answers
2k views

How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?

Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof) I need to delete (.bak files older, than, say, 7 days in remote ...
5
votes
1answer
948 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 ...
2
votes
1answer
1k views

SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

We've recently upgraded to SQL Server 2012 (from SQL Server 2008 R2) on Windows Server 2012 (from Windows Server 2008 R2), and a SQL Server Job Agent job that was previously running without error is ...
2
votes
1answer
316 views

Sqlcmd command strips $ out of query variable

What I am trying to do Restore a database from backup using sqlcmd. Sqlcmd is being called by PowerShell. What is the issue The server folder which contains the database backups has a $ inside of ...
2
votes
1answer
563 views

Need Help Executing a Remote Task from SQL Server Agent

There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if ...
1
vote
1answer
254 views

Error creating XTP Table in SQL Server 2014 CTP1 with Powershell

Having a couple of hours to play I created a SQL Server 2014 box on Azure and followed This SQLServerCentral Post to create a File Group and table with T-SQL. All good. I then decided to take a look ...
2
votes
1answer
579 views

Reading SQL Performance Counters using PowerShell script

I'm trying create a PS script to read some performance counter about my SQL for my Level 1 partners but I'm having a problem with the instance name. I'm using this code: $computer = "\\\" + ...
1
vote
0answers
69 views

Powershell and SQL Script Execution in selected schemas

Any one please help me on this one also..I have got many schemas under one database. And in that database I have some SQL Scripts to be executed. My issue is that I should be able to execute those sql ...
2
votes
1answer
112 views

PowerShell to get database schemas

I am able to connect to a remote SQL Server with the instance name using PowerShell. I am using an XML file (something like a configuration file) which includes all my database details. PowerShell ...
2
votes
1answer
270 views

SQL Server connectivity and script execution using PowerShell

I am designing a PowerShell script that can execute some sql scripts in a folder. Actually this must be done remotely. I am able to connect to the particular server remotely using: $SqlConnection = ...
0
votes
1answer
934 views

PowerShell based SQL script execution [closed]

I am new to powershell. Even I dont think I know the basics..But now I am assigned a work to do the execution of sql scripts residing in a remote server. 1) we have to connect to that particular ...
2
votes
1answer
2k views

SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

SQL Server 2008R2 PowerShell 2.1 I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I ...
0
votes
1answer
740 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: ...
1
vote
1answer
2k 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 ...
2
votes
2answers
2k 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 ...
1
vote
0answers
316 views

Powershell to dump database schema to text file

I want to be able to dump a database schema 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 ...
5
votes
1answer
321 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
609 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
1answer
1k 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 ...
6
votes
1answer
546 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 ...
4
votes
2answers
966 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 ...
4
votes
1answer
930 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
1answer
680 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
1answer
591 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 ...
5
votes
1answer
286 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 instance of SQL Server. I found a script ...
19
votes
3answers
6k 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 ...
4
votes
2answers
6k 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 ...