19 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 ...
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,...
user avatar
  • 10.2k
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 @...
user avatar
  • 33.6k
10 votes

Running sp_AskBrent with @ExpertMode = 1 in PowerShell

Brent here. Correct, @ExpertMode = 1 turns on multiple result sets - diagnostics, wait stats, file stats, and perfmon counters. If you only want one result set, don't turn on @ExpertMode. If you ...
user avatar
  • 41.4k
10 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 ...
user avatar
  • 1,579
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, ...
user avatar
  • 41.4k
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 ...
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....
user avatar
  • 15.4k
7 votes
Accepted

Job showing the running sign but not currently running

Use script instead to check status of currently running job. SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF(MINUTE, ...
user avatar
  • 3,933
7 votes
Accepted

SQL Server job - powershell Where does "log to table" save logs

The F1 key for help docs reveals that the execution result is stored in the sysjobstepslogs table of the msdb database.
user avatar
  • 211
7 votes
Accepted

Dropping a table with powershell

A cleaner way to do this would be to get the table object from the database object directly, and then drop it if it returns non-null. This will ONLY run if the table exists. #drop the Table $tb = $db....
user avatar
  • 17.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 ...
user avatar
  • 27.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 ...
user avatar
  • 9,307
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 ...
user avatar
  • 1,579
6 votes
Accepted

How Do I Use PowerShell to Get a SQL Server Backup Status?

Code gets backup history for all databases on a server and generates HTML report Step 1: DECLARE @Body VARCHAR(MAX), @TableHead VARCHAR(MAX), @TableTail VARCHAR(MAX) SET ...
user avatar
  • 3,933
6 votes
Accepted

PSQL not executing SQL command - Powershell

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\'; $env:PGPASSWORD = 'mypwd'; .\psql --% -U postgres -w myDB .\psql --% -c "copy {'SELECT * FROM myTable';} TO 'C:\Users\e\Desktop\test1.csv' CSV ...
user avatar
  • 52.2k
6 votes
Accepted

Correct way to stop SQL Server (through automation)

You are safe either which way you go. SERVICES.MSC and POWERSHELL command lines are EQUAL functions to MSSQL Configuration manager... Configuration manager is also used to control instances, etc etc ...
user avatar
  • 1,012
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 ...
user avatar
  • 68.9k
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 ...
user avatar
  • 67.2k
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 ...
user avatar
  • 1,579
5 votes

What is the most effective way to discover all running instances of SQL Server using PowerShell?

The only way that I know of discovering instances across an environment without knowing all of the possible owning servers and their particular names, would be to make a call to System.Data.Sql....
user avatar
5 votes

SQL Server 2012 Powershell script to generate database with schema and data

I wanted to use Powershell to script a small database so that I can track changes in git. I found the same script you did to use as a starting point: Automated Script Generation with Powershell and ...
user avatar
5 votes
Accepted

Passing current server name from SQL Server Agent job to PowerShell

Replace A-SVR with SRVR. A-SVR is for alerts. SRVR will contain server name and instance name. Refer to document on MSDN.
user avatar
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 ...
user avatar
  • 2,369

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