How to capture SQL 2008 sqlcmd.exe output to a log file in PowerShell 2.0
-
I'm writing a PowerShell 2.0 script that calls SQL Server 2008 sqlcmd.exe, and I am looking for a way to capture output from sqlcmd.exe to a log file. I've tried conventional redirection (">", "2>&1", etc.), "| Output-File", Start-Transcript, etc. Ideally, I would like to emulate the way you can redirect the output from osql.exe in a cmd.exe script to a text file with ">". Since we are building new hardware for our SQL 2008 environments, I would like to avoid using older cmd.exe and osql.exe technology. At this point, I don't see value in re-writing our SQL 2005 T-SQL (.sql) scripts so that they can run under SQLPS.
So far, Start-Transcript has come the closest to our needs, but it does not capture T-SQL print statements or SQL Backup progress messages that display on the PowerShell console when sqlcmd.exe executes a T-SQL script. I've tried the "-r1" switch, but it gets ignored in the following example from my script:
sqlcmd.exe -S $DatabaseConnection_STR -E -w600 -b -d dba -r1 -i $TempInputFile_NMEAny ideas?
-
Why not just use the output parameter for sqlcmd itself?
sqlcmd.exe -S $DatabaseConnection_STR -E -w600 -b -d dba -r1 -i $TempInputFile_NME -o $OutputFile_Name
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I dont Know if I understand , But If you want to capture the output from sqlcmd to txt file, you can use the -o from sqlcmd..but in powershell you can try :
$a = "sqlcmd.exe -s MyServer -d master -Q ""SELECT TOP 10 name from spt_values"""
Invoke-expression $a | Out-File c:\temp\testsqlcmd.txt
or using -o from sqlcmd
$Server = "Myserver"
$Database = "MAster"
$Query = """SELECT TOP 10 name from spt_values"""
$OutputFile = "C:\temp\testesqlcmd.txt"
$a = "sqlcmd.exe -s $Server -d $database -Q $query -o $OutputFile "
Invoke-expression $a
But I firmly believe you can use Invoke-Sqlcmd
$Query = "SELECT TOP 10 name from spt_values"
invoke-sqlcmd -ServerInstance $Server -Database $Database -Query $Query | Out-File c:\temp\testesqlcmd.txt
I dont know, I think I do not understant your problem
-
Jeffrey Williams-493691 (5/1/2010)
Why not just use the output parameter for sqlcmd itself?sqlcmd.exe -S $DatabaseConnection_STR -E -w600 -b -d dba -r1 -i $TempInputFile_NME -o $OutputFile_Name
In addition to performing database backups, the script does some "housekeeping" tasks related to the backups. If possible, I would like to have a single log to reference if anything fails and I need to debug what happened.
I had already thought about using -o and "cat"ing the output in my script, but that messes up the output if you run the script manually, which I would be doing if I needed to debug it at some point in the future.
-
Laerte Poltronieri Junior-367636 (5/1/2010)
Hi,I really do not understand your question, can you explain better ?
Here is the long version.
- All of our SQL Server backups go to disk and subsequently get picked up to tape by NetBackup
- Our tape backup team has a long (multi-year) history of backing up open files and calling the backups "good," despite a multitude of re-education attempts by the DBAs to help them understand that a NetBackup backup of "in use" .mdf, .ndf, and .ldf files will be corrupt when anyone tries to restore that backup; in like fashion, a NetBackup of an "in use" SQL Server database backup files (.bak, .trn) will likely be corrupt as well
- In an attempt to stop the NetBackup insanity, we are implementing a directory rotation scheme that writes the "live" SQL backups to "DirA"; when finished, the script renames DirA to DirA.01, etc.; even if NetBackup backs up DirA while it is open, DirA should be renamed to DirA.01 (and be "closed") by the time NetBackup comes along the next night; so, at that point, we should have a good copy of what used to be in DirA on tape; each evening, DirA.01 gets renamed to DirA.02 and so on--eventually, we should have several nights of "closed" backups on tape, in case we need them
A stripped down version of my script does something like this:
mkdir DirAsqlcmd.exe -S ... -E -Q "backup database [dba] to disk = N'D:\DirA\dba_2010-05-02_002436.bak'" ...rmdir DirA.03 -recursemv DirA.02 DirA.01mv DirA.01 DirA.02mv DirA DirA.01mkdir DirAThis script will eventually be scheduled with Task Scheduler, and I would like to be able to capture all of the activity (PowerShell and sqlcmd.exe) in a single log file so that I can use the log file as a road map to debug whatever went wrong in a prior run.
-
I did not think much on how to join the output of sqlcmd with powershell, but there is a possible solution made in powershell for everything (not test with the script you send its ok, just rewrite in powershell)
$Server = $env:computername
$Database = "master"
$LogFile = "c:\temp\testsqlcmd.txt"
$error.Clear()
try
{
New-Item -ItemType directory -Path c:\dirA -Force -ErrorAction Stop
"New Path c:\DirA created with success" | Out-File $LogFile -Append
$Query = "BACKUP DATABASE [master] TO DISK = N'C:\temp\master.bak' WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
invoke-sqlcmd -ServerInstance $Server -Database $Database -Query $query -ErrorAction Stop
"Database Master backuped with success " | Out-File $LogFile -Append
Remove-Item -Path c:\dirA -ErrorAction Stop
"Path c:\dirA removed with success" | Out-File $LogFile -Append
Move-Item -Path c:\dirA.02 -Destination c:\dirA.01 -ErrorAction Stop
"c:\dirA.02 moved to c:\dirA.01 with success " | Out-File $LogFile -Append
Move-Item -Path c:\dirA.01 -Destination c:\dirA.02 -ErrorAction Stop
"c:\dirA.01 moved to c:\dirA.02 with success " | Out-File $LogFile -Append
Move-Item -Path c:\dirA -Destination c:\dirA.01 -ErrorAction Stop
"c:\dirA moved to c:\dirA.01 with success " | Out-File $LogFile -Append
New-Item -ItemType directory -Path c:\dirA -Force -ErrorAction Stop
"New Path c:\DirA created with success" | Out-File $LogFile -Append
} catch {
$Error | Out-File $LogFile -Append
}
The -erroraction in each cmdlet to stop send script execution to catch block and log the error.
Hope can help
-
Laerte Poltronieri Junior-367636 (5/2/2010)
I did not think much on how to join the output of sqlcmd with powershell, but there is a possible solution made in powershell for everything (not test with the script you send its ok, just rewrite in powershell)...Hmmm.... This looks very interesting. I plan to check it out later today. Thanks for the code!
-
Laerte Poltronieri Junior-367636 (5/2/2010)
I did not think much on how to join the output of sqlcmd with powershell, but there is a possible solution made in powershell for everything (not test with the script you send its ok, just rewrite in powershell)...I think this is going to work. Thanks for your help! Here is the syntax I am using for a quick and dirty test:
start-transcript temp.txtwrite-host "test"$Server = "server_name\instance_name"$Database = "db_name"$LogFile = "c:\temp\testsqlcmd.txt"$error.Clear()$Query = "select * from junk"invoke-sqlcmd -ServerInstance $Server -Database $Database -Query $query -ErrorAction Stopstop-transcriptIn this example, temp.txt contains the output from "write-host" and the output from invoke=sqlcmd:
cat temp.txt**********************Windows PowerShell Transcript StartStart time: 20100502155439Username : MOUNTAIN\DBAJPS1Machine : MOUNTAIN (Microsoft Windows NT 6.0.6002 Service Pack 2)**********************Transcript started, output file is temp.txttestcola----MondayTuesdayWednesdayThursdayFridaySaturday**********************Windows PowerShell Transcript EndEnd time: 20100502155439********************** -
I resolved a similar issue while building an infrastructure script to process T-SQL batch files. I encountered many issues trying to programmatically capture the error message from sqlcmd.exe. Attempted to use the invoke-sqlcmd cmdlet in SQLPS because the error code is easy to capture programmatically. I abandoned invoke-sqlcmd, however, due to the limitations with capturing results and messages from the T-SQL queries.
My Requirements
=============
Requirement #1 - Trap the error returned from the sqlcmd call in Powershell.
Requirement #2 - Write any error messages to a designated log file.
Requirement #3 - Save all results and processing (PRINT) messages returned from each T-SQL command executed through sqlcmd.
My Solution
=============
This implementation wraps the sqlcmd call in the INVOKE-COMMAND cmdlet. The following Powershell command invokes the sqlcmd.exe utility and executes two T-SQL statements. The 1st succeeds and the 2nd fails during the same session. The whole operation fails but only after query #1 returns data - and I want to capture this data!
Requirement #1 is addressed by setting the -r0 and -b sqlcmd parameters. They redirect error messages to the standard output stream and set the ERRORLEVEL variable, respectively. Parameter -r0 causes Powershell to save the error message in the $Error[0].Exception object. Parameter -b sets the $LASTEXITCODE environment variable.
Requirement #2 is addressed using the 2> redirection operator from Windows. The redirection operator is located inside of the -scriptblock parameter as part of the sqlcmd. This effectively pushes the error stream to the ScriptError.txt file.
Requirement #3 is addressed by piping all standard output the ScriptOutput.txt using the out-file cmdlet. The -e sqlcmd parameter echoes the T-SQL statement to the standard output stream
======================================
Powershell Command
======================================
INVOKE-COMMAND -scriptblock {sqlcmd -S .\SQL2008R2 -E -Q 'SELECT [name] FROM master.sys.databases;SELECT TOP 10 * FROM sys.columnses;' -r0 -b -e 2> "C:\ScriptError.txt"} | out-file "C:\ScriptOutput.txt"
======================================
Powershell Results
======================================
$LASTEXITCODE = 1
$ERROR[0].Exception.Message = Invalid object name 'sys.columnses'.
===================
ScriptError.txt
===================
SQLCMD.EXE : Msg 208, Level 16, State 1, Server CONNEMARA\SQL2008R2, Line 1
At line:1 char:36
+ INVOKE-COMMAND -scriptblock {sqlcmd <<<< -S .\SQL2008R2 -E -Q 'SELECT [name] FROM master.sys.databases;SELECT TOP 10 * FROM sys.columnses;' -r0 -b -e 2> "C:\Users\Maste
rChief\Documents\vbb5spc.err"} | out-file "C:\Users\MasterChief\Documents\vbb5spc.txt"
+ CategoryInfo : NotSpecified: (Msg 208, Level ...L2008R2, Line 1:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Invalid object name 'sys.columnses'.
===================
ScriptOutput.txt
===================
SELECT [name] FROM master.sys.databases;SELECT TOP 10 * FROM sys.columnses;
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
ReportServer$SQL2008R2
ReportServer$SQL2008R2TempDB
SchemaTest
(7 rows affected)
I hope this helps!
-
Steve, that is fantastic! Thank you very much. I wasted hours looking for ways to run a folder full of SQL scripts with GO commands in them via PowerShell. Invoke-sqlcmd makes detecting errors a serious pain, and the POSH community written version of Invoke-sqlcmd2 does not parse the GO keyword as a batch terminator, and returns "Incorrect syntax near 'GO'" errors.
I was looking at writing code to read each SQL script line by line, and execute the buffer of commands each time it reached a GO, then skip to the next line when I stumbled across this post, and your fabulous answer.
I hope I've embedded enough keywords in this post to make it easy for others attempting the same kind of thing to find!
I ended up omitting the nifty 2> error output redirect (and the -r0 parameter), since it created a file even when there were no errors. Instead, I just piped the $Error[0].Exception.Message to Out-File to write the SQL error to an error file, and I placed that inside an if($LastExitCode -eq 1) {}, so I only see error files when there are errors.
Works a charm!
--David
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply