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.
2
votes
2answers
57 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
63 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
76 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
234 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
84 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
175 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
1answer
444 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 ...
3
votes
1answer
236 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 ...
1
vote
1answer
298 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
182 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
258 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
176 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
293 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
62 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 ...
1
vote
0answers
86 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
194 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
184 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
686 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
335 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
1k 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
1k 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
184 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
235 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
425 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 ...
2
votes
1answer
616 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
352 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
726 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 ...
3
votes
1answer
668 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
407 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
443 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
248 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 ...
17
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 ...
1
vote
2answers
3k 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 ...
3
votes
2answers
3k 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 ...
6
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
707 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
291 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
480 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
8k 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 ...
7
votes
2answers
765 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
996 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)
...
