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).
390 questions
1
vote
1
answer
41
views
How to run sp_blitz against all sql servers and store the output in a single table?
You can run sp_blitz (First Responder Kit by Brent Ozar) on a sql server to output to a table. But it will store the output only on that server. I am using Dbatools.io PowerShell commands currently. ...
- 357
0
votes
0
answers
49
views
SqlBulkCopy - How to force it to use a renamed bcp file
I'm trying to read data from XEL files and input to a SQL Server table.
$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.InputXELData"
$...
- 442
2
votes
2
answers
252
views
Is it possible to store SQL path instead of uploading entire BLOB data?
I've a c# site which is uploading images in SQL database and now that SQL table size is around 300GB. Is it possible to store the path of those images instead of storing entire blob data while ...
- 21
0
votes
1
answer
52
views
unable to update service account password for windows service on remote database server
I am trying to update service account password for windows service(example: SQL service) on remote server but keeps getting following error. I am using PowerShell version 7.0 which supports -...
- 119
2
votes
4
answers
368
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 ...
- 801
0
votes
1
answer
162
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
103
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.6k
2
votes
1
answer
502
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.6k
0
votes
1
answer
151
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
54
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
52
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
372
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 ...
- 335
0
votes
0
answers
182
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 ...
- 377
0
votes
2
answers
141
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 ...
- 335
0
votes
0
answers
43
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
39
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
437
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
1
vote
1
answer
270
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
273
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
689
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
489
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
394
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 ...
- 223
0
votes
1
answer
48
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
764
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
342
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:...
- 584
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
296
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,217
0
votes
0
answers
85
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,239
0
votes
1
answer
334
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 ...
- 662
0
votes
1
answer
462
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
203
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
2k
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,219
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
772
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
171
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.
...
- 11
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,629
1
vote
2
answers
693
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
159
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 ...
- 733
1
vote
0
answers
315
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
471
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
284
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
5k
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,217
0
votes
1
answer
279
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-...
- 447
0
votes
1
answer
389
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
187
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
383
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
381
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
2k
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,217
1
vote
1
answer
431
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,217