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
38 views

Job showing the running sign but not currently running

I have a script to get the currently running jobs: -- get the running jobs --marcelo miorelli -- 10-dec-2013 SELECT sj.name ,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds FROM ...
2
votes
2answers
64 views

Run a PowerShell script on a different server - from inside a SQL Server job

I have a procedure to backup SSAS Databases. That is working as a charm. Now my server is filling up with SSAS backups and I would like to delete the backup files that are older than 2 days. to ...
0
votes
0answers
10 views

Domain Inventory Powershell Script [migrated]

I'm working on a code for my companies office to help maintain better inventory of our computers and their capabilities/hardware. This is what I have written, Basically use Wmget to pull info off the ...
1
vote
1answer
49 views

Script Table Definition in Powershell

I'm writing a powershell to create a duplicate table(in another schema) for the purposes of partition switching*. I'm having problems adding the columns and the clustered column store index. Here's ...
2
votes
1answer
155 views

How Do I Use PowerShell to Get a SQL Server Backup Status?

I need a power-shell script to obtain the following information Name of the database , LastBackupDate of the database and this information should be sent via an email converting the results to HTML ...
0
votes
0answers
33 views

SQLCMD.exe in SQL Server 2012 does not give real-time output logging [closed]

I'm running a long-running stored procedure using sqlcmd.exe for automation purposes. However, I observed that in SQL Server 2012, sqlcmd.exe does not produce real-time logging. It displays only when ...
2
votes
3answers
157 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
177 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
68 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
2answers
89 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
56 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
131 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
139 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
53 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
34 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
156 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
864 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
34 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
280 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
237 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
67 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
200 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
85 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
953 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
159 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
2k 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
440 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
1k 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 ...
1
vote
5answers
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
1k 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
334 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
640 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
258 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
610 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
1answer
84 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
122 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
282 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
1k 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
790 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
3k 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
328 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
339 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
618 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
574 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
1k 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 ...