Questions tagged [powershell]

Windows PowerShell™ is a task-based command-line shell and scripting language designed especially for automation of Windows tasks. Questions using this tag should restrict themselves to interactions directly with the database tier. General PowerShell scripting questions should be directed to [StackOverflow](https://stackoverflow.com/questions/tagged/powershell).

Filter by
Sorted by
Tagged with
0 votes
0 answers
17 views

Postgres - how to automatically kill a 'hung' session?

I have a powershell process that accesses a Postgres database 24*7 and runs a continuous stream of near-instantaneous transactions. Most of the individual transactions will take a fraction of a second,...
user avatar
-1 votes
0 answers
16 views

Run multiple scripts on multiple databases/servers using Try catch method with output for each individual script

Can I please request if anyone can help with my below SQL Server requirement. (TSQL or PowerShell) --# List Server\Instance names --# List Database names --# List Username and Password --# List Folder ...
user avatar
  • 1
0 votes
0 answers
16 views

SSRS Security settings using ReportService2010 - .GetPolicies InheritParent value

I need to add permissions on lots of folders, I was looking into using ReportService2010, but I have doubt regarding inheritance of security settings. In all examples I found online they are using: $...
user avatar
0 votes
1 answer
87 views

Using ODBC drivers to connect to Postgres database via Powershell

I've routinely connected to a postgres database via ODBC using a powershell script. Like this.... $DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$...
user avatar
0 votes
1 answer
32 views

Trying to insert results of Get-CimInstance to SQL table

I'm trying to get drive space for multiple servers $servers = Get-Content "\\SQL-Server1\e$\Scripts\DBInfo\DBInfo_DiskfreeComputers.txt" foreach ($Server in $Servers) { $results = Get-...
user avatar
0 votes
0 answers
22 views

Exporting multiple TSQL result sets into one spreadsheet using Powershell

I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table ...
user avatar
0 votes
1 answer
31 views

Setting up reporting and alerting in SQL Server through TSQL and Powershell

I am trying to automate monitoring on SQL Server through PowerShell and TSQL, to get alerts through mail whenever some issue is there. I have gone through many articles over the internet and tried the ...
user avatar
0 votes
1 answer
37 views

Check File System for SQL Server Access across multiple servers

This article describes the File System permissions that are needed for the SQL Server Database Engine to access any folder where database files are stored. My organization has dozens of database ...
user avatar
1 vote
2 answers
79 views

How can one authenticate with AzAD while using Read-SqlTableData to read AzSQL tables

How should one specify the User ID while creating the $sqlcc object when using Read-SqlTableData to read a table in AzSQL with an Azure account? $sqlcc = new-object ('System.Data.SqlClient....
user avatar
1 vote
1 answer
30 views

Postgresql connection timing-out too quickly

I'm running a powershell script on windows that connects to a postgres database. Under normal conditions everything works fine, but I'm trying to make the system more robust, and make everything re-...
user avatar
1 vote
1 answer
74 views

Postgres psql command intermittently 'stalls' in powershell

I'm in the process of moving a postgres database to a new server. I have number of large tables, partitioned by month, each contains several 10's of millions of rows. I have a powershell script to do ...
user avatar
0 votes
0 answers
39 views

Read .XEL file from a Azure Storage Account in Powershell

Trying to pass an Azure storage account that hold a file to this object initiation. please can anyone help read a file from a storage account into powershell. How do I mention a Storage account path ...
user avatar
  • 11
0 votes
0 answers
55 views

How do you copy the permissions of a certificate to another certificate using Powershell?

How do you copy the permissions of a certificate to another certificate using Powershell? I added a certificate through a pfx file and the new certificate doesn't have the same permissions as the old ...
user avatar
0 votes
2 answers
679 views

Calling a powershell script using xp_cmdshell with parameter in T-SQL

I have been using the following references for this Calling powershell function from T_SQL. This is on SQL server 2016 The element I need help on is the format of the call in SQL to the powershell ...
user avatar
  • 227
2 votes
1 answer
161 views

Powershell SMO changing database context from master when setting database to SINGLE_USER

I am trying to set a database to single user using SQL Server SMO and have the code below. This code is an abridged excerpt from a larger restore script $Credential = Get-Credential [System....
user avatar
  • 1,369
0 votes
0 answers
23 views

How to setup Network in Desired State Configuration for SQLSetup - Action = "InstallFailoverCluster"

I am looking to setup the network addressing correctly. Configuration SQLInstall { node localhost { SQLSetup 'InstallSQLClusterNode1-2019' { Action = 'InstallFailoverCluster' . . . ...
user avatar
1 vote
1 answer
61 views

How to specify differential backup option?

I'm implementing an automated log shipping solution, and so far I have the following: <# #Pre-req# a. The primary database needs to be in Full recovery mode #Primary Steps# 1. Backup: Generate ...
user avatar
  • 123
0 votes
1 answer
439 views

How to use PowerShell Invoke-Sqlcmd to query and table and update a PowerShell $variable?

How do I update a PowerShell variable using an Invoke-Sqlcmd statement? Eg. $PSVariable Import-Module sqlserver $QueryStatement = "SELECT $PSVariable = Max(Data) FROM MyDatabase" ...
user avatar
  • 175
0 votes
1 answer
375 views

Read and Get Data from ExcelFile using powershell

I'm starting with powershell and this time. I have to read all the values from a excel file In this case from a specific sheet. The ultimate goal of this task is to get the values from two columns and ...
user avatar
0 votes
0 answers
77 views

How to get Sql Instance names of remote server

I am trying to get all SQL instance names (default & named) when supplying a computer/server name. The main issue I have is that on an environment that uses both availability groups and FCI, I can ...
user avatar
0 votes
1 answer
125 views

Automation of MSSQL database transaction log file truncation

I am looking into setting up an automated process through PowerShell or anything for truncating SQL Server transaction log files once disk utilization reaches 90% on the SQL Server host. Does anyone ...
user avatar
  • 1
0 votes
0 answers
46 views

Using Powershell and ODBC connection to Postgresql, how can I catch the Notices from a call procedure?

I have a procedure that will raise some output. call my_procedure(param1, param2); table "temp_ts" does not exist, skipping INFO: No BR has been applied. INFO: Params are valid. Code ...
user avatar
1 vote
0 answers
44 views

Generating script of a table with more than 99 rows will append a 'GO' afterwards

I have a Powershell script that will take the schema and data on some of the tables. The relevant script being: foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) { # Scripter object ...
user avatar
0 votes
2 answers
181 views

changing SSMS settings via Powershell

in order to enable the dark theme to SSMS (not available on the picture below as you see): following this webpage: SQL SERVER Management Studio 18 - Enable Dark Theme - SQL Authority with Pinal Dave ...
user avatar
0 votes
1 answer
260 views

How to get currently used SSL certificate thumbprint from MSSQL

So, how to get currently running SSL certificate (especially thumbprint) from MSSQL using Powershell? It could include SQL commands... I know that it is in Windows Registry, but if you change it then ...
user avatar
0 votes
1 answer
1k views

SQLPS - Pass Parameter into SQL - invoke-Sqlcmd vs. invoke-DbaQuery

OK, have a little bit of a puzzle. If I use invoke-sqlcmd by itself with -Variable I can successfully pass a variable into my .sql script Now I need to do this against multiple instances, and thought ...
user avatar
0 votes
1 answer
206 views

Run T-SQL Script Using Powershell via SQL Agent Job

Tried to think of the best subject title; however having a problem getting this done. I'll give my preferred way, but open to any best method to get this done. I have a tsql script that is done, ...
user avatar
0 votes
1 answer
30 views

How can i automate DIfferential restore using dbatools Powershell

How can i automate DIfferential restore using dbatools Powershell I have configured FUllbackup migration from powershell using dbatools and using multi thread I am able to restore 4.5TB under 4 hours. ...
user avatar
0 votes
1 answer
29 views

Looking for a tool for managing complex powershell automation processes with multiple scripts [closed]

Sysadmin here, I'm wondering what tools you all use to manage large scale automation tasks with powershell. I currently have several powershell scripts that are regularly used, but do not interact or ...
user avatar
  • 93
1 vote
0 answers
19 views

Last year's totals won't group by hour

I have a PowerShell script with a query to display yesterday's totals, as well as last year's same day totals, by hour for comparison. Yesterday's totals display by hour fine, but last year's list the ...
user avatar
0 votes
1 answer
551 views

Powershell script executed by SQL Server doesn't take effect

I'm doing some testing and I want to execute a PowerShell script through xp_cmdshell. Below is the PowerShell script (c:\temp\sqltotext.ps1). echo "haha" | Out-File "c:\temp\haha.txt&...
user avatar
1 vote
1 answer
114 views

SQL Server 2019 setting Force Encryption with a script

I have to update 50+ servers to use encrypted connection strings. Is there a way to script setting Force Encryption and TrustServerCertificate (e.g Powershell, or ???) so that I don't have to open ...
user avatar
0 votes
1 answer
90 views

Powershell script while executing mysqldump script

I'm having an error 1045: Access denied for user 'mysqlsuperuser'@'test.internal.cloudapp.net' (using password: YES) when trying to connect Here is the mysqldump command on my ps1 file. $command = [...
user avatar
  • 59
1 vote
1 answer
186 views

`Invoke-SqlCmd` does not display RAISERROR messages where `sqlcmd` does

I switched from sqlcmd to Invoke-SqlCmd, and suddenly my RAISERROR messages disappeared. When I run it with sqlcmd, they appear. Is there any way to make them visible with Invoke-SqlCmd? RAISERROR (N'...
user avatar
  • 215
0 votes
2 answers
325 views

Backup to unc path in workgroup environment

I am having a problem backing up the database to the network path using dbatools.io Backup-DBAdatabase command. This is a workgroup environment. The SQL Server runs under the NT Service\MSSQLSERVER ...
user avatar
0 votes
2 answers
372 views

How to log multiple invoke-sqlcmd verbose outputs into single log file along with showing/not showing them through flag into prowershell prompt

Lets say we have multiple invoke-sqlcmd statements like below: invoke-sqlcmd -Query "PRINT 'Hello World 1'" -ServerInstance Server\SQL2017 -Database abc -Verbose invoke-sqlcmd -Query "...
user avatar
0 votes
1 answer
188 views

SQL Server 2019 System.Data.SqlClient namespace query

I am using the .NET classes in the System.Data.SqlClient namespace on a SQL 2019 Enterprise Edition and have successfully run TSQL commands via the creation of a function in PowerShell. function ...
user avatar
  • 73
-1 votes
1 answer
235 views

Schedule SQL Agent Job on RDS SQL Instance to restore latest backup weekly from S3

Scenario: I want to schedule an SQL Agent job weekly on RDS SQL instance to restore database which should access the latest backup file from S3. I'm using sp msdb.dbo.rds_restore_database to restore ...
user avatar
0 votes
1 answer
150 views

Generate script for all stored procedures in Azure SQL database

I'm trying to create a SQL script that will create all the stored procedures that were exported from another database. Basically navigating in SSMS to the database > Generate Scripts > select ...
user avatar
  • 101
2 votes
0 answers
592 views

PowerShell Script Not Working on SQL Server Job Step

I've some .CSV files generated in a shared folder \\SERVER01\DIR\Current and would like to copy to another folder in same server \\SERVER01\DIR\Archive. The script needs to copy, rename files adding ...
user avatar
  • 21
0 votes
2 answers
124 views

How to use msdb.dbo.backupset and dbatools to gather all of the historical backup size information for all instances in an environment

I have too many servers to manage to want to do this manually on each server so I'd like to somehow automate this using the dbatools module and the below query for gathering the backupset information. ...
user avatar
  • 1,051
3 votes
0 answers
401 views

After Upgrade/Migration to SQL Server 2019 Databases Periodically Gets Stuck In a Inaccessible State [closed]

I have a bit of a unique challenge, I have a series of test databases I use for CI type database testing, generally with each release I will restore (overwrite) these databases from a backup, update ...
user avatar
1 vote
0 answers
117 views

Unable to execute dbachecks command

i installed dbachecks module in a server. but when i am trying to execute get-dbccheck, it is throwing the below error Populating RepositorySourceLocation property for module Pester. VERBOSE: Loading ...
user avatar
0 votes
1 answer
346 views

Passing in multiple instances into the dbatools command GET-DBADBFILE

I am attempting to gather all of the dbfile information on all of our sql instances in one of our environments. It is a fairly large environment and would be tedious to perform one instance at a time. ...
user avatar
  • 1,051
0 votes
1 answer
131 views

Executing SQL Script through PowerShell gets inconsistent results vs in SSMS

I'm hoping somebody here can give me some advice. We use a software suite to manage and monitor PC inventory, including which executables get executed. One of the "features" (not a bug, ...
user avatar
1 vote
1 answer
161 views

Generate SQL scripts in powershell

I want to be able to generate simple repetitive scripts in SQL using Powershell as the scripting language. For example, If I want to generate a SQL GRANT command for every table in a database, and ...
user avatar
  • 4,088
1 vote
1 answer
682 views

Trouble connecting to Oracle database with sqlplus and OS authentication

I am a read-only user to an oracle database, and the DBA gave me two accounts, one with a specific user name and password, and another with OS authentication. The OS authentication is supposed to let ...
user avatar
0 votes
3 answers
283 views

To generate a script to drop or map the orphan users in a SQL Server Instance

I am very new to SQL Server. I have a script which find the orphan users in all the databases and generate the script to either drop or map the user to correct login (If available). Since i want to ...
user avatar
-1 votes
1 answer
218 views

Power shell script for failover from primary to DR

SQL server version is SQL Version 2016 Current setup is two nodes in primary having always on availability group. Adding a 3rd node which is DR(Disaster and Recover). Does anyone knows a power shell ...
user avatar
1 vote
2 answers
1k views

AlwaysOn availability group DR failover with Powershell

I am setting up AlwaysOn availability group for Disaster Recovery (DR) in the test environment. For AG1, node1 and node2 (synchronous commit/automatic failover) are in DC1; node 3 and node 4 (...
user avatar
  • 45

1
2 3 4 5
8