CODEWITHSUNDEEP
sql-server-2008powershell
anael
anael

Reputation: 158

SQL Server 2008: how to get the "Messages" tab (in Management Studio) output with powershell

I am using invoke-sqlcmd to manage my databases, it works fine. I save the output of the "Results" tab in Management Studio to a log file.

When I do a BACKUP DATABASE, the output is done on the "Messages" tab and I would like to save it in the logfile.

Is there a way to get that output ? (or a table in SQL Server 2008 that stores the backup progression and results ?)

The SQL Server 2008 logs contain something but it is not as verbose as the "Messages" tab.

Upvotes: 3

Views: 4622

Answers (2)

anael
anael

Reputation: 158

A working example if it can help someone :

$ps = [PowerShell]::Create()
[ref]$e = New-Object System.Management.Automation.Runspaces.PSSnapInException
$ps.Runspace.RunspaceConfiguration.AddPSSnapIn( "SqlServerCmdletSnapin100", $e ) | Out-Null
$ps.AddCommand( "Invoke-Sqlcmd" ).AddParameter( "Verbose" ).AddParameter( "ServerInstance", "localhost\SQLEXPRESS2K8" ).AddParameter( "Query", "BACKUP DATABASE xxx TO DISK = N'c:\tmp\xxx.bak' WITH FORMAT, STATS = 10" )
$ps.Invoke()
$ps.Streams.Verbose | % { $_.Message} | Out-File c:\tmp\ps002.txt
cat c:\tmp\ps002.txt

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453288

Invoke-Sqlcmd does not display messages, such as the output of PRINT statements, unless you specify the Windows PowerShell -Verbose common parameter. For example:

Invoke-Sqlcmd -Query "PRINT N'abc';" -Verbose

Source

Upvotes: 3

Related Questions