3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
anael
  • 158
  • 1
  • 8

2 Answers2

3

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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Note: running get-help invoke-sqlcmd returns help information which also states this info. The only reason I mention this--get-help is a great place to start when you have questions one specific cmdlets. – Chad Miller May 16 '11 at 16:52
  • I got the output on the console, but nothing in my log file. Another error in my script ? `invoke-sqlcmd -ServerInstance "myPC\SQLEXPRESS2K8" -Query "BACKUP DATABASE test333 TO DISK = N'c:\Backup\test333.bak' WITH FORMAT, STATS = 10" -Verbose | Out-File -filePath "c:\tmp\ps.txt"` – anael May 17 '11 at 07:14
  • 1
    See [connect item](http://connect.microsoft.com/SQLServer/feedback/details/390263/invoke-sqlcmd-should-have-ability-to-capture-verbose-output) and [this answer](http://stackoverflow.com/questions/4511498/powershell-invoke-sqlcmd-capture-verbose-output) – Martin Smith May 17 '11 at 07:24
  • great links ! Thanks and sorry for not finding that answer :p – anael May 17 '11 at 07:38
2

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
anael
  • 158
  • 1
  • 8