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).
387
questions
2
votes
4
answers
238
views
How does SMO make it easy to grab all code on a Microsoft SQL Server?
There has recently been a lot of talk about putting database code in source control. I have read widely on this topic and there appears to be an unstated assumption that using SMO through PowerShell ...
- 661
0
votes
1
answer
107
views
Execute Python with Powershell in SQL Server 2019
I have been using SQL Server 2016's SQL Server Agent with bottom setting and script for last 3+ years. It was/is working fine.
But, now, I am migrating everything to SQL Server 2019, but the same ...
- 251
0
votes
1
answer
61
views
sql server client network utility - how to list the aliases?
there is something called sql server client network utility
it lives on
C:\Windows\System32 for 64 bits.
as you can see I have many aliases there, server, instance and ip address and port used for ...
- 16.4k
2
votes
1
answer
143
views
how to set in-transit encryption sql server?
today I had to check if in-transit Encryption was up and running in a particular server (sql server) and if not then set it up.
the way it is done is:
In SQL Server Configuration Manager, expand SQL ...
- 16.4k
0
votes
1
answer
95
views
SQL Agent Job failing after uninstall of SQLPS and install of the new module
Uninstalled the old PS module and replaced it with the new SQLServer but now third step of system job syspolicy_purge_history, which is a Powershell step, fails with the following message:
The error ...
- 1,550
0
votes
1
answer
44
views
Invoke-PolicyEvaluation with the new SqlServer module generates security error
Started getting error messages after uninstall of PS and install of the new module SqlServer when evaluating policies on remote servers using Registered Servers and Invoke-PolicyEvaluation cmdlet. It ...
- 1,550
0
votes
0
answers
39
views
Write-DbaDbTableData - "Exception occurred while trying to enumerate the collection" error
I have the following code that I use to retrieve some TempDB space details on an AG:
$DataDriveTable = Invoke-DbaQuery -SqlInstance $SQLInstance -Database TempDB -Query $SelectDriveQuery -...
1
vote
1
answer
275
views
Insert values in PowerShell Variable into Oracle Database Table
I have created a script in PowerShell (utilising the PowerShell dbaTools) which collect information about SQ Server Estate in our environment. I need to insert the result from the PowerShell directly ...
- 325
0
votes
0
answers
143
views
Uninstalling a SQL Server instance using dbatools
I don't know if this is the right forum for this question, please let me know if it isn't and kindly point me in the right direction. I have just started learning powershell and dbatools.
I am trying ...
- 373
0
votes
2
answers
119
views
Error when scripting SQL View dbo.spt_values
I am using a Powershell script to generate scripts of my SQL database objects (Tables, Views, StoredProcedures etc.). It was working fine in testing and still works on 2 out of the 3 servers being ...
- 173
0
votes
0
answers
35
views
Problems with minutes in timestamps when inserting data into InfluxDB via PowerShell
I am working with InfluxDB and trying to insert time series data via PowerShell. My goal is to insert data points with specific timestamps containing hours, minutes and seconds into InfluxDB. I am ...
- 101
-1
votes
1
answer
36
views
Backup and Restore Disaster Recovery
I am using PowerShell to write a script for DR, assuming that we only have backup files of all the databases. If i have a latest sql server version and i want to restore some database from backup ...
0
votes
1
answer
341
views
How to export as a pipe-delimited file using PowerShell?
This code creates a comma-separated file, I need to convert that to the pipe-delimited format. How to do that?
$DataSet.Tables[0] | Export-csv "G:\Text.txt" -NoTypeInformation
- 1
0
votes
0
answers
45
views
Unix Powershell script stops after running SQLPlus command within the script
We are trying to create a Unix Powershell (not shell script) script which first runs a SQL to update a couple of tables, psprcsque and psprcsrqst. The function which runs the SQL update gets variables ...
- 1
1
vote
1
answer
213
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,163
0
votes
0
answers
239
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
1
answer
550
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
1
vote
1
answer
467
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
327
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 ...
- 231
0
votes
1
answer
46
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,550
1
vote
1
answer
655
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
308
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:...
- 529
0
votes
1
answer
1k
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 ...
- 439
1
vote
0
answers
271
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,187
0
votes
0
answers
81
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,219
0
votes
1
answer
293
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 ...
- 658
0
votes
1
answer
440
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
2k
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
198
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...
...
- 371
0
votes
1
answer
1k
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,189
1
vote
3
answers
2k
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
732
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
148
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
2
votes
1
answer
1k
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,619
1
vote
2
answers
610
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):
...
- 100
0
votes
0
answers
147
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 ...
- 723
1
vote
0
answers
311
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
415
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
277
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
4k
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,187
0
votes
1
answer
255
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-...
- 437
0
votes
1
answer
371
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
170
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
379
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
368
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,187
1
vote
1
answer
407
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,187
0
votes
2
answers
10k
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
593
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....
- 2,033
1
vote
1
answer
192
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