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
2answers
57 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 ...
2
votes
1answer
96 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....
0
votes
0answers
21 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' . . . ...
1
vote
1answer
37 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 ...
0
votes
1answer
153 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" ...
0
votes
1answer
36 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 ...
0
votes
0answers
53 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 ...
0
votes
1answer
69 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 ...
0
votes
0answers
32 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 ...
1
vote
0answers
41 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 ...
0
votes
2answers
66 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 ...
0
votes
1answer
69 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 ...
0
votes
1answer
661 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 ...
0
votes
1answer
180 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, ...
0
votes
0answers
28 views

Powershell connecting to Azure SQL DB errors out using Active Directory Password authentication

The following powershell script connects to an Azure SQL database using Active Directory Password authentication. Add-Type -Path ('C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data....
0
votes
1answer
20 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
1answer
26 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 ...
1
vote
0answers
17 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 ...
0
votes
1answer
392 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&...
0
votes
0answers
59 views

The SQL Scripter library is running a SELECT COUNT(*), how do I stop it?

I'm using the Microsoft.SqlServer.Management.Smo.Scripter library from within a PowerShell script to pull SQL object creation scripts from a DB as part of an automated process. The user account used ...
1
vote
1answer
78 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 ...
0
votes
1answer
77 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 = [...
1
vote
1answer
121 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'...
0
votes
2answers
183 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 ...
0
votes
2answers
195 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 "...
0
votes
1answer
141 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 ...
-1
votes
1answer
165 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 ...
0
votes
1answer
105 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 ...
2
votes
0answers
393 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 ...
0
votes
2answers
77 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. ...
3
votes
0answers
327 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 ...
1
vote
0answers
94 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 ...
0
votes
1answer
263 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. ...
0
votes
1answer
101 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, ...
1
vote
1answer
110 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 ...
1
vote
1answer
518 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 ...
0
votes
3answers
174 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 ...
-1
votes
1answer
162 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 ...
1
vote
2answers
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 (...
0
votes
0answers
208 views

Using SQL Agent Job to execute a script from a UNC path

I need to execute a script located on an UNC share. I've setup my SQL job to use CMDExec and to execute the following command: PowerShell -NOPROFILE -ExecutionPolicy Bypass -File \\Server\...
1
vote
1answer
256 views

Deleting expired SQL server database backup file on data domain using PowerShell

I am backing up the database on data domain however I'll have to delete an expired backup using following command. It's an emc databoost agent app for Microsoft sql server. I am trying to convert the ...
0
votes
1answer
61 views

Determine the source of user permissions in a database

I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment. How can I track down what security ...
0
votes
0answers
360 views

Powershell script hangs with psql command, continues only after hitting enter

I have a simple powershell script that is supposed to drop an existing schema and restore it from a backup, the script is basically those lines: echo "Dropping Schema MySchema" & $PG_SQL -d "host=...
0
votes
1answer
2k views

PowerShell script to send an email with SQL results in HTML format

Does anyone have a PowerShell script to query results from a SQL Server database and send it out as an email in HTML format?
1
vote
3answers
85 views

Report of permissions in SQL server

I want to write a report that gets all the permissions for every Login in our SQL server farm. For now I can get the logins but get stuck there trying to connect the logins to roles. Haven't even ...
0
votes
0answers
344 views

Error while executing PowerShell script using SQL Server Agent Job

I am executing a PowerShell script using SQL Server Agent job, script code is given below(it is added as is in the Job step 1) : $OriginFile = "\\vmpr-file1\share3\RT-BI_Shared\ABC.xlsx" $SourceFile =...
9
votes
3answers
8k views

How can I install the SQL Server PowerShell module on an offline machine?

I have some SQL Server machines that cannot connect to any external sites. I have been using PowerShell more and more when managing databases, so I definitely need this module to be installed so ...
1
vote
1answer
258 views

how can I get only the SQL server logins, when we use Export-dbalogin

when we run the export-dbalogin, it will will generate a script(.sql) file, which we will run the in the destination server, then the logins will be migrated, but this command will give both windows ...
1
vote
1answer
199 views

How do I pass a variable into -job on Copy-DbaAgentJob?

I'm trying to query one the source instance and copy across any missing jobs into the destination instance. Everything seems to work except passing the name into -job. Can anyone suggest a fix or ...
0
votes
1answer
304 views

Restoring a managed SQL instance in Azure using PowerShell

I am trying to figure out how to restore a database from one managed SQL instance to another. I'm following the tutorials, but I keep running into inscrutable error messages. Here's my command: ...

1
2 3 4 5
7