20

Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell The trick, as Jonathan points out, is that you need to have a listener attached to the connection manager listening for the print or error actions. $cd.Parameters.Add("@f", $bak.FullName) | Out-Null $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $...


18

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet: Set-Location c: This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file. This article contrasts how SQLPS loads in SQL ...


13

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 that points to a SQL Server Express LocalDB-specific API that allows for automatic instance start-up upon being referenced in a connection string. The whole ...


13

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, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, ...


11

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 @cpu_speed_mhz int, @cpu_speed_ghz decimal(18,2); EXEC master.sys.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = '...


10

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 want multiple result sets, but your application (in this case, PoSH) can't consume them, you'll need to log them to tables. That's where these parameters come in: ...


8

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 your script #load assemblies [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null #Need SmoExtended for backup [System....


8

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, when you end up with the database in restoring state, step back through your logs to see what went wrong. (If you want a second set of eyes from the community,...


8

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.Windows.Forms ssms.exe 'c:\temp\test.sql' -E -S localhost # wait to make sure the application is open start-sleep -Milliseconds 10000 # press F5 [System....


8

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 want for the module to be saved to). This will save the module to a directory of the same name in c:\tmp (c:\tmp\SqlServer). Then, copy that whole directory ...


7

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, activity.run_requested_date, GETDATE()) as Elapsed, case when activity.last_executed_step_id is null then 'Step 1 executing' else '...


7

A simple press on the F1 key for the help would reveal, that the execution result is stored in the sysjobstepslogs table of the msdb database.


7

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.Tables['listeningport'] IF ($tb) {$tb.Drop()} You can use $tb.Tables['tablename', 'schemaname'] to use a non-dbo schema. When I deal with things like ...


7

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 search existing firewall rules by name by using the Get-NetFirewallRule cmdlet, for example: Get-NetFirewallRule -Name "*WMI*" This uses wildcards to final all the ...


7

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 which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation. Alternatively, you can change directory ...


6

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 NoCount ON ; SET @TableTail = '</body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: solid ...


6

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 but the function of starting and stopping a service is the same no matter which method you choose. Some people have their own ways of doing things, for example ...


6

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 Invoke-SQLCmd? Invoke-SQLCmd -ServerInstance localhost -Query 'E:\PowerShell_PROD\Screenshot\ServerDetails.sql' Which are almost the same thing. Quoting from ...


6

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 as an administrator, directly from SQL Server. Creating a credential is the first step, and consists of running a T-SQL statement like this: CREATE ...


6

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 it for you. Pass the list of instances into Invoke-DbaQuery and include the -AppendServerInstance switch, and all the instances will be queried and the name of ...


6

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 there is a login with the same name, it will remap them. Remove-DbaDbOrphanUser - locates orphaned users and drops them from the database (you can do this with ...


5

I see from the comments that you've already given Compare-Object a try, but that really should be working. Below is a working example showing this: $SqlServer1Name = "SQL1" $SqlServer2Name = "SQL2" $SqlServer1 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer1Name) $SqlServer2 = New-Object Microsoft.SqlServer.Management.Smo.Server($...


5

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 SMO You can find all of the options for the ScriptingOptions class here: ScriptingOptions Class You're getting the error because of the now deprecated ...


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