19

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, $...


16

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

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, ...


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: ...


10

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 = '...


9

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 ...


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....


7

Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations. I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older ...


7

Try this $db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};


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 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 ...


7

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 ...


6

You cannot loop through objects in the way you have coded. Here's the working code: Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; $ServerName = 'xyzabc123' $DatabaseName = 'test1' $TableName = 'main' $TableSchemaName = 'dbo' $Server = New-Object Microsoft.SqlServer.Management.Smo....


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

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.


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 ...


5

One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name. You could either do this by accessing the Server.ConnectionContext property: $ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;" $SqlServer = New-...


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