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).
376
questions
1
vote
1
answer
39
views
Download certificate from Key Vault w/ private key for use in SQL Server (TDE) via powershell
I need to download an x509 Certificate from Azure Key Vault and import it in SQL Server to use for TDE.
Note: I'm not referring to EKM providers/ASYMMETRIC KEYs, where SQL accesses key vault to pull ...
- 2,073
0
votes
0
answers
88
views
Use PowerShell script to backup SQL Server 2019 Express database to a network destination
I've got a SQL database server (SQL Server 2019 Express) and a application server connected by a simple switch. Now I'd like to back up a SQL database to the application server.
Since its an SQL ...
- 101
0
votes
0
answers
117
views
Error with dbatools
Trying to export SQL Objects via DBAtools in Powershell.
Get-DbaAgentJob -SqlInstance 'TestServer01' -Job 'mydatabase-daily-backup' | Export-DbaScript -FilePath C:\temp\mydatabase-daily-backup.sql
...
- 1
0
votes
0
answers
29
views
"consise" SQL table scripting via Powershell SQLServer module
When I script a database using Visual Studio, I get a more "consise" script like the one below, where constraints are included in the create table statement. I'm wondering if there is an ...
- 101
1
vote
1
answer
213
views
Call SQL Stored Procedure from Powershell to insert XML into a table
I have a working stored procedure:
USE [DB_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[INSERT_XML]
-- Add the parameters for the stored procedure here
@...
- 113
0
votes
1
answer
92
views
How can I get the SQL Server instance names running based on a hostname (COMPUTERNAME)?
I have only a hostname (what Microsoft calls a COMPUTERNAME). How can I get the names of SQL Server instances running on the host?
It appears from other answers that I could look through the services ...
- 221
0
votes
1
answer
27
views
Run Policy Based Management On Central Management Server with Code
I have a jump\terminal server to administer 100 SQL Servers and want to evaluate my locally created policies across all the servers by using the Central Management Server but WITHOUT:
creating the ...
- 1,528
1
vote
1
answer
255
views
Powershell script SQL Server AvailabilityGroups
I am really new to Powershell and hopefully someone can help me with this error. I have been given the below script that is supposed to take a list of SQL servers from a text file and lists out the ...
0
votes
1
answer
188
views
Why does `Get-ChildItem` require a call to `invoke-sqlcmd` to see `SQL SERVER:\SQL\...`?
PowerShell has a cool module for interacting with SQL Server. One of the features is the ability to navigate database objects like file system objects. For example:
get-childitem -Path "SQLSERVER:...
- 495
0
votes
0
answers
210
views
Using ReportingServicesTools to change the connection string of a PowerBi report
We're migrating to a new SQL Server instance and I'm trying to change the connection strings in our PowerBI reports via PowerShell and ReportingServicesTools.
Basically, all I need to do is to change ...
- 43
0
votes
1
answer
859
views
Can we change the parameter of Find-DbaAgentJob?
Can we change the parameter of Find-DbaAgentJob?
Hi there,
I got "[Find-DbaAgentJob] Failure | Error connecting to [xxx.yyy.zzz]: The certificate chain was issued by an authori
ty that is not ...
- 429
0
votes
0
answers
37
views
Checking Scale Out Deployment of SSRS using PowerShell
I logged on the SSRS server and from the SSRS Configuration Manager, I can check if it's a scale out deployment. But is there a way to check it using PowerShell? I want to automate this process and ...
- 1,157
1
vote
0
answers
179
views
How to direct psql errors to powershell log file?
I'm running a powershell script on a windows system. The script includes psql commands to run SQL on a postgres database.
Most stuff gets correctly recorded in the powershell log file, EXCEPT for psql ...
- 1,125
0
votes
0
answers
60
views
Rollback a write-dbadatatable with the truncate flag?
I'm using the dbatools for our SQL Server 2019 server. When I try to import an Excel sheet, I would like to truncate the table before importing the new rows. This is possible if I add the -Truncate ...
- 2,173
0
votes
1
answer
188
views
How to check AutoProtect setting for Azure Backup using PowerShell?
We have about 30 SQL instances being backed up by Azure Backup. I have the needed PowerShell commands to set the AutoProtect policy, but there doesn't seem to be a command to get the AutoProtect ...
- 628
0
votes
1
answer
317
views
Unable to connect to MariaDB using Invoke-SQLcmd
I have tried this a ton of different ways and I can't figure out why the syntax isn't working :
I have mariaDB running on localhost 127.0.0.1:3306 and I'm able to connect successfully in MySQL ...
1
vote
1
answer
1k
views
Can't Connect to WMI Provider on New SQL 2022 Install
I have a fresh installation of Microsoft SQL Server 2022 on Windows Server 2022 Standard. I'm attempting to enable Always On Availability Groups with the Enable-SqlAlwaysOn PowerShell commandlet but ...
- 111
0
votes
1
answer
125
views
Reading from .sql file located in Visual Studio repository
A user.sql file which contains
USE [database]
GO
-- Legacy users cleanup
DROP USER IF EXISTS [dw];
etc...
is located at the repository: https://company.visualstudio.com/Database/_git/Azure?path=etc...
...
- 361
0
votes
1
answer
709
views
How to enable these SQL Server PowerShell commands?
On this Microsoft documentation page, it lists a lot of SQL Server PowerShell commands. But how can I use it? Should I install anything? I just installed SQL Server 2016 (without SSMS) and it seems ...
- 1,157
1
vote
3
answers
796
views
How can I export all of the Tables from a given Database in SQL Server to tab separated files?
I tried using SSMS but it only lets me export one file at a time and I'd prefer not to create an SSIS package. Is it possible to do this in PowerShell?
I need the headers of each table in the file as ...
- 37
0
votes
1
answer
574
views
Invoke-AzVMRunCommand on Azure VM using domain user
I am working on ARM template to setup SQL server VM on Azure with Always on High availibility setup using Win server 2019 and SQL Server 2019 Enterprise image. I am trying to automate everything using ...
Nirav B
-1
votes
1
answer
73
views
SQL Server long term backup policy
I wish to implement a long term backup policy in SQL Server on VM, much like Retention policies in azure sql database.
I have full backup once a week, diff once a day and log backup every 15 minutes.
...
- 1
1
vote
1
answer
672
views
DBATools :: how to query Azure SQL Databases with MFA
I'm following the official DBATools guide regarding how to query Azure SQL Databases.
This code is working:
$azureCredential = Get-Credential -Message 'Azure Credential'
$azureAccount = Connect-...
- 1,484
0
votes
2
answers
357
views
dbatools import csv help!
Is there a way I can import csv files with the proper data types into MSSQL table using PS dba tools?
I'm using this below command but all I get is a table and all the columns are nvarchar(max):
...
- 90
0
votes
0
answers
116
views
How do I retain command history for psql running within PowerShell?
I'm running the following version of psql
psql (12.11)
on Windows Powershell running on Windows 10. Within the same shell session, when I log in and out of psql, it doesn't retain the previous ...
- 643
1
vote
0
answers
280
views
Powershell Script for Copying backup files and capture results in output file with specific details
I have the below PowerShell script which is working for me to capture the results for one set of backup file but my requirement is to copy 10 or more backups files in parallel to a shared location and ...
1
vote
0
answers
268
views
Enable Shared Memory Protocol (SQL Server PowerShell)
I`m trying to modify and use powershell script from learn.microsoft.com in order to enable SQL Shared Memory protocol on a remote server. But my issue is instead of connecting to provided ...
0
votes
1
answer
189
views
Powershell SQL Remove DB access from list
Hi hope someone can help i have a list of users and instances they have access to in a text file like below.
SQL-UAT-DB domain\testuser
SQL-Prod-DB domain\Joe.bloggs
SQL-Train-DB domain\...
- 1
1
vote
1
answer
3k
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=$...
- 1,125
0
votes
1
answer
203
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-...
- 427
0
votes
1
answer
299
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 ...
- 115
0
votes
1
answer
109
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 ...
0
votes
1
answer
326
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 ...
- 103
1
vote
2
answers
299
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....
- 115
1
vote
1
answer
1k
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-...
- 1,125
1
vote
1
answer
296
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 ...
- 1,125
0
votes
2
answers
7k
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 ...
- 249
3
votes
1
answer
472
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....
- 1,908
1
vote
1
answer
170
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 ...
- 133
0
votes
1
answer
3k
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"
...
- 175
0
votes
1
answer
3k
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 ...
- 13
0
votes
0
answers
441
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 ...
- 203
1
vote
1
answer
445
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 ...
- 11
1
vote
0
answers
218
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
...
- 11
0
votes
2
answers
589
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
...
- 15.8k
0
votes
1
answer
2k
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 ...
- 3
0
votes
1
answer
3k
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 ...
- 21
0
votes
1
answer
295
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, ...
- 21
0
votes
1
answer
142
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.
...
0
votes
1
answer
44
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 ...
- 93