Skip to main content

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
1 vote
1 answer
549 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 ...
Kim's user avatar
  • 11
1 vote
0 answers
232 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 ...
Paul_LayLow's user avatar
0 votes
2 answers
721 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 ...
Marcello Miorelli's user avatar
0 votes
1 answer
3k 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 ...
Vangass's user avatar
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 ...
SQLSeeker's user avatar
0 votes
1 answer
333 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, ...
SQLSeeker's user avatar
0 votes
1 answer
220 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. ...
raja mangubat's user avatar
0 votes
1 answer
47 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 ...
Austin's user avatar
  • 93
1 vote
0 answers
30 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 ...
Jared Severson's user avatar
0 votes
1 answer
2k 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&...
Just a learner's user avatar
1 vote
1 answer
489 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 ...
John Deupree's user avatar
0 votes
1 answer
339 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 = [...
JRA's user avatar
  • 149
1 vote
1 answer
563 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'...
cilerler's user avatar
  • 225
1 vote
2 answers
1k 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 ...
user1716729's user avatar
0 votes
2 answers
2k 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 "...
Murali Dhar Darshan's user avatar
0 votes
1 answer
388 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 ...
zapcon's user avatar
  • 73
-1 votes
1 answer
653 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 ...
Vikas Kumar's user avatar
0 votes
1 answer
693 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 ...
Dieter's user avatar
  • 101
2 votes
0 answers
1k 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 ...
AdemirP's user avatar
  • 21
1 vote
2 answers
366 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. ...
Geoff Dawdy's user avatar
  • 1,123
3 votes
0 answers
801 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 ...
David Rogers's user avatar
1 vote
0 answers
246 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 ...
Saravana Kumar's user avatar
0 votes
1 answer
992 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. ...
Geoff Dawdy's user avatar
  • 1,123
0 votes
1 answer
352 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, ...
jparnell8839's user avatar
1 vote
1 answer
338 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 ...
Walter Mitty's user avatar
  • 4,346
1 vote
1 answer
2k 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 ...
datadawg2000's user avatar
0 votes
3 answers
1k 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 ...
Saravana Kumar's user avatar
-1 votes
1 answer
697 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 ...
C1ark05's user avatar
1 vote
2 answers
3k 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 (...
AMMH's user avatar
  • 45
1 vote
1 answer
739 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 ...
SqlDBA's user avatar
  • 117
0 votes
1 answer
248 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 ...
Philippe's user avatar
  • 507
0 votes
0 answers
1k 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=...
Damir Porobic's user avatar
1 vote
1 answer
5k 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?
Zammy89's user avatar
  • 13
1 vote
3 answers
963 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 ...
Peter Schuetze's user avatar
0 votes
0 answers
829 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 =...
Rachna Verma's user avatar
9 votes
3 answers
23k 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 ...
Marcello Miorelli's user avatar
1 vote
1 answer
855 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 ...
Harsh Sanghai's user avatar
1 vote
1 answer
553 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 ...
TuckRollworthy's user avatar
0 votes
1 answer
719 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: ...
eckza's user avatar
  • 101
1 vote
1 answer
3k views

What permissions does the SQL Agent's user need to execute Powershell scripts?

I'm trying to create a job with a Powershell step. To recreate this: create new job, give it a name add a new job step, name it, type powershell, run as the SQL Agent, script: $test="test" set the ...
end-user's user avatar
  • 211
0 votes
2 answers
804 views

Powershell dbatools combining result of multiple Get-dbaxxx commands to generate a report

I'm new to powershell and dbatools. It is a great tool but when I try to combine the result of few different commands into an array to be exported to csv, it is not providing the intended result. It ...
Ali's user avatar
  • 305
1 vote
1 answer
843 views

SQL Server Encrypted Connections - Configuration Manager does not see Certificate

My goal is to implement encrypted connections on Test SQL Server instance First, tried to create self-signed certificate in the Certificates snap in (certlm.msc), Personal > All Tasks > Request New ...
Aleksey Vitsko's user avatar
5 votes
2 answers
9k views

Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file

Rather than issuing a query per instance via SQL Server Management Studio, we are trying to create a powershell script to query multiple instances by going through a loop referencing a text file where ...
Dennis M.'s user avatar
1 vote
1 answer
4k views

how to list the SSRS permissions in each folder in the reporting services server?

I have the following powershell script that downloads all the RDL from a reporting services server. I am not the author to whom I am very thankful. It has been very useful for downloading the RDLs. ...
Marcello Miorelli's user avatar
-2 votes
1 answer
3k views

PowerShell script to output SQL query results [closed]

I have the SQL cmdlets loaded in PowerShell, need a script to execute a SQL statements and save results in pipe delimited file
user201420's user avatar
3 votes
1 answer
8k views

where can I find the DNS name associated to the listener of an Availability Group?

I have an availability group that has a listener. The listener is called SQLPROD as you can see on the picture below. It uses port 1433 When I run the following script: ALTER AVAILABILITY GROUP [...
Marcello Miorelli's user avatar
0 votes
1 answer
790 views

How to capture data from a PowerShell script that runs as part of a SQL Agent Job?

I have a PowerShell script called from an Agent job which opens an Excel file, converts it to .CSV and saves the .CSV file in a particular location, ready for me to import it with BULK INSERT. What I ...
Geoff Griswald's user avatar
0 votes
2 answers
521 views

How to automate creating credentials in a secure way

We have created an automated process to stand up a new SQL Server using TFS and powershell. As part of a new server build, we deploy a database and a set of jobs for dba use. As part of the install ...
DForck42's user avatar
  • 3,048
2 votes
2 answers
2k views

PowerShell command 'New-Item' fails when invoked through SQL Server Agent job

I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing. The problem is the ...
S.D.'s user avatar
  • 746
0 votes
1 answer
633 views

How to find port using Get-DbaTcpPort

How to find whether the port used by SQL Server is static or dynamic using Get-DbaTcpPort dbatools command? Below command doesn't show whether it's static/dynamic: Get-DbaTcpPort -sqlinstance TEST ...
SQLPRODDBA's user avatar
  • 1,908

1
2
3 4 5
8