0
votes
1answer
40 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 ...
2
votes
1answer
83 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 ...
2
votes
0answers
125 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
128 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
53 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
105 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
57 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
1answer
89 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 ...
3
votes
2answers
955 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
238 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
483 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
3answers
1k 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 ...
1
vote
1answer
230 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
473 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 = "\\\" + ...
2
votes
1answer
229 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
564 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
1k 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 ...
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
243 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
285 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
522 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
935 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
452 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
1answer
835 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, ...
5
votes
1answer
265 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 instace of SQL Server. I found a script on ...
19
votes
3answers
5k 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 ...
3
votes
2answers
4k views

How to monitor SQL Server data and log file growth?

I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
7
votes
2answers
2k views

Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?

I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks. So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities. ...
0
votes
1answer
880 views

attach database - verify - detach from a script or a batch file

for a hundred good reasons .... and I promise that at least a few are good ... I find myself with almost 200 SQL server databases (mdf,ldf files) generated by different versions of sql server, most ...
2
votes
4answers
322 views

What “tool” to use for monitoring instances and where to store this data?

I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server ...
1
vote
1answer
596 views

powershell to connect to SQL and display a friendly message when it cannot connect

We check our SQL servers using powershell. The script goes through all servers in a text file and sends the output to a text file when it can connect. But when it cannot connect, it displays error in ...
1
vote
2answers
10k 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 ...
6
votes
2answers
1k views

powershell to get sql server memory counters and show value

I am writing a powershell script to capture the following SQL server counters: SQL Server: Memory Manager: Total Server Memory (KB) SQL Server: Memory Manager: Target Server Memory (KB) My ...
8
votes
2answers
869 views

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes ...
4
votes
3answers
1k views

Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM) ...