22 votes
Accepted

Connect to LocalDB via SQLCMD in PowerShell

The casing of the instance name does not matter. You need to try: sqlcmd -S "(localdb)\MSSQLLocalDB" -d testdb01 The "localdb" part needs to be enclosed in parenthesis as that is a special syntax ...
Solomon Rutzky's user avatar
13 votes
Accepted

Are cursors/While loops the only way to make administrative changes to multiple databases?

As far as loops go for this type of thing, don't worry about it. Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster. For admin stuff,...
wBob's user avatar
  • 10.4k
12 votes
Accepted

how to get the CPU speed in SQL Server?

You could use xp_cmdshell, or you could use xp_regread. Since xp_regread is 'safer' (I mean, you don't have to change any configuration options to use it), you could do something like this: DECLARE @...
Erik Darling's user avatar
  • 40.5k
12 votes
Accepted

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

PowerShell has a built-in mechanism for this which should be easier than the previous answer. From an Internet-connected computer, run Save-Module sqlserver -path c:\tmp (substitute whatever path you ...
alroc's user avatar
  • 1,674
9 votes

Identify root cause for why database is stuck in RESTORING state

Occam's razor suggests starting with the obvious: If your script sometimes leaves a database in restoring state, then debug the script. Start by logging what you're doing in a table or a file. Then, ...
Brent Ozar's user avatar
  • 42.9k
8 votes
Accepted

Missing assembly when running PowerShell script using SMO

Using some information found in SQL Server PowerShell : How to Restore SQL Server Databases Using SMO and PowerShell, I was able to get past that error. Try adding the following to the very top of ...
Scott Hodgin - Retired's user avatar
8 votes
Accepted

How to execute SQL Server query in SSMS using PowerShell

You probably shouldn't do this, but it's possible to simulate user activity by using SendKeys. Sends keystrokes to the active application. This does what you want: add-type -AssemblyName System....
Tom V's user avatar
  • 15.7k
7 votes
Accepted

how to Enable these Windows Firewall rules using powershell?

You would use the Enable-NetFirewallRule cmdlet: Enable-NetFirewallRule -Name "WMI-WINMGMT-In-TCP", "WMI-RPCSS-In-TCP" In order to find the relevant rule names (in order to enable it), you can ...
Josh Darnell's user avatar
  • 29.2k
7 votes
Accepted

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

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS ...
HandyD's user avatar
  • 9,932
7 votes

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

The easiest way to do this (read: least amount of additional code you need to write) is to use Invoke-DbaQuery from the dbatools PowerShell module. Remove the foreach loop - this function will handle ...
alroc's user avatar
  • 1,674
6 votes

How to execute SQL Server query in SSMS using PowerShell

I can't see why you'd use SSMS for unattended script execution. There is no added value at all. You would use sqlcmd? sqlcmd -Q 'E:\PowerShell_PROD\Screenshot\ServerDetails.sql' -E -S localhost or ...
gbn's user avatar
  • 69.8k
6 votes

Run Powershell Script From Agent As Administrator

Create a PowerShell proxy in SQL Server agent that uses a privileged Windows account via a SQL Server credential. CREATE CREDENTIAL docs. How to create a proxy That will allow you to run PowerShell ...
Hannah Vernon's user avatar
  • 70k
6 votes

To generate a script to drop or map the orphan users in a SQL Server Instance

The dbatools module has already solved this for you. Get-DbaDbOrphanUser - reports all users in one or more databases with no corresponding login Repair-DbaDbOrphanUser - finds orphan users and if ...
alroc's user avatar
  • 1,674
5 votes
Accepted

How to collect NTFS file properties and insert into SQL Server table

SSIS is well equipped to handle CSV files and load them into SQL Server. You can have a very simple package using the Flat File Source. The dialogue and setup is a familiar windows "wizard" like ...
Dave's user avatar
  • 2,389
5 votes

Are cursors/While loops the only way to make administrative changes to multiple databases?

Here's one method that doesn't require a looping construct: DECLARE @sql nvarchar(MAX) = N''; SELECT @sql += N'ALTER DATABASE ' + QUOTENAME(name) + N' SET RECOVERY FULL; ' FROM sys....
Dan Guzman's user avatar
  • 28.1k
5 votes
Accepted

Get-MSSQLLinkPasswords Powershell script doesn't seem to work under SQL Server 2016

I've tested dbatools' Copy-SqlLinkedServer from SQL Server 2016 to SQL Server 2016 and SQL Server 2014 to SQL Server 2016. All linked servers and their respective logins were successfully migrated. ...
Chrissy LeMaire's user avatar
5 votes

Retrieving SQL Server InfoMessages in PowerShell 5

I finally realized the problem was variable scope. The way around it is to define it as global $global:message = "" and use it inside the event handler with the same reference: $handler = [System....
Leandro Chao's user avatar
5 votes

T-SQL or PowerShell to move bak files older than 2 days to another location

This Powershell example will find all .bak files older than two days in the c:\backup folder and move them to \shared\archive shared drive. Get-ChildItem c:\backup\*.bak |where {$_.LastWritetime -lt (...
Bob Klimes's user avatar
  • 3,214
5 votes
Accepted

Determine the PackageFormatVersion for multiple .DTSX packages files in a folder

Retrieve dtsx info programmatically You can read my detailed answer on StackoverFlow: Automate Version number Retrieval from Dtsx files Demo App I Created A Demo Application to achieve this ...
Hadi's user avatar
  • 1,222
5 votes
Accepted

Using SMO from PowerShell, is there a way to state that a jobstep output file should be appended and not overwritten?

I think it is more along below lines $jobStep.JobStepFlags = "AppendToLogFile" #call the alter to modify $jobStep.Alter() #display it $jobStep.JobStepFlags
Kin Shah's user avatar
  • 62.2k
5 votes
Accepted

SQL Agent PowerShell Syntax Error

If you are running this as a SQL Server Agent job step you will need to use the Operating system (CmdExec) step type, not a PowerShell type. Within the CmdExec it would be best to create a file on ...
Nic's user avatar
  • 4,003
5 votes

Check progress of database restore running asynchronously

I don't see anything obvious about why this command won't be showing up like any other restore command, unless it is busy doing something other than RESTORE DATABASE. I suggest identifying the ...
Aaron Bertrand's user avatar
5 votes
Accepted

Get-ClusterParameter Not Returning Expected Results

The command you are running is returning all of the parameters for an availability group resource. There are not many, as most of the config is stored in SQL Server and not as cluster resource ...
Tony Hinkle's user avatar
  • 7,939
5 votes
Accepted

Invoke-SqlCmd - does casting as integer prevent injection

You can, but there's problems with doing that. Primarily, it's still not a great idea because the pattern isn't extendable. It only works for certain data types. As soon as you have a string ...
Bacon Bits's user avatar
  • 1,885
5 votes
Accepted

Invoke SQLCMD doesn't work when used with xp_cmdshell

There are several problems here: The main issue is that the CMD shell executes each line as it comes in via a return; it does not wait for an "end of command" indicator such as ;, nor does it attempt ...
Solomon Rutzky's user avatar
5 votes

DBATools offline install - export-dbainstance was not found

You need to import the module using import-module dbatools See Properly importing the module Specifying the path to the .psd1 file hope that helps you ! Also, what Scott commented is documented ...
Kin Shah's user avatar
  • 62.2k

Only top scored, non community-wiki answers of a minimum length are eligible