我有一个通过powershell进行SQL Server数据库备份的过程，该过程在遍历数据库时会记录到单个文本文件中。当我们遇到错误时，我想将这些错误记录到表中，但是本机T-SQL会阻止在Try-Catch块中获取备份错误消息，因此我想从文本文件中提取此信息。
我只想从最近的数据库中获取消息。因此，我的问题。
给定以下文本示例：
12:31:32.310 #############
12:31:32.326 # Database1 #
12:31:32.326 #############
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5901, Level 16, State 1, Server Server1, Line 1
One or more recovery units belonging to database 'Database1' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 9002, Level 17, State 4, Server Server1, Line 1
The transaction log for database 'Database1' is full due to 'ACTIVE_TRANSACTION'.
Msg 3013, Level 16, State 1, Server Server1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 2, Server Server1, Line 4
Cannot open backup device 'D:\Backups\Database1\Database1_201408260031_backup_Native_DIFF.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server Server1, Line 4
VERIFY DATABASE is terminating abnormally.
12:31:31.435 #############
12:31:31.435 # Database2 #
12:31:31.435 #############
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
BACKUP DATABASE [Database2] TO DISK = N'D:\Backups\Database2\Database2_backup_Native_FULL.bak' WITH STATS=10,INIT,COMPRESSION;
43 percent processed.
86 percent processed.
99 percent processed.
Processed 296 pages for database 'Database2', file 'Database2' on file 1.
100 percent processed.
Processed 2 pages for database 'Database2', file 'Database2_log' on file 1.
BACKUP DATABASE successfully processed 298 pages in 0.180 seconds (12.893 MB/sec).
The backup set on file 1 is valid.
12:31:32.310 #############
12:31:32.326 # Database3 #
12:31:32.326 #############
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5901, Level 16, State 1, Server Server1, Line 1
One or more recovery units belonging to database 'Database3' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 9002, Level 17, State 4, Server Server1, Line 1
The transaction log for database 'Database3' is full due to 'ACTIVE_TRANSACTION'.
Msg 3013, Level 16, State 1, Server Server1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 2, Server Server1, Line 4
Cannot open backup device 'D:\Backups\Database3\Database3_201408260031_backup_Native_DIFF.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server Server1, Line 4
VERIFY DATABASE is terminating abnormally.
我只希望将以下输出仅用于Database3：
One or more recovery units belonging to database 'Database3' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
The transaction log for database 'Database3' is full due to 'ACTIVE_TRANSACTION'.
BACKUP DATABASE is terminating abnormally.
Cannot open backup device 'D:\Backups\Database3\Database3_201408260031_backup_Native_DIFF.bak'. Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.
到目前为止，我需要过滤掉Database3以上的所有错误。
Get-Content ("C:\SampleError.txt") | Select-String -SimpleMatch "Msg" -Context 0, 1 | %{ ($_.Context.PostContext); }
我基本上只需要在最后一个块（＃Database3＃）上过滤Get-Content，但是我很难找到正确的方法（LastIndexOf？）。由于可支持性的原因，我想避免使用正则表达式，但愿意接受建议。谢谢你的帮助！
好吧，让我们这样做。找到最后一行，12:34:56.789 #############然后找到“获取内容”文件。选择-skip，但是要减去3行。我还要获取时间和数据库名称，并创建一系列看起来很整洁的自定义对象。显然，如果您不想这样做，可以跳过第2行，而跳过ForEach在第3行。
$LinesToSkip = select-string -Path C:\temp\input.txt -Pattern "(\d{2}:\d{2}:\d{2}\.\d{3}\s+?\#+)"|select -last 1 -ExpandProperty linenumber
$Failure = GC C:\temp\input.txt | Where{$_ -Match "(\d{2}:\d{2}:\d{2}\.\d{3})\s+?\#\s+?([^#]+)\s"}|Select -Last 1 |ForEach{$Matches}
$Log = Get-Content C:\temp\input.txt | Select -skip ($LinesToSkip - 3) | Select-String -SimpleMatch "Msg" -Context 0, 1 | Select -ExpandProperty Context | Select -ExpandProperty PostContext | ForEach{[PSCustomObject][Ordered]@{'Database'=$Failure[2];'Failure Time'=$Failure[1];'Message'=$_}}
$Log|FT -AutoSize
$Log|Export-Csv c:\output.csv -NoTypeInformation
输出将进入一个CSV文件，并且在屏幕上看起来像：
Database Failure Time Message
-------- ------------ -------
Database3 12:31:32.326 One or more recovery units belonging to database 'Database3' failed to gen...
Database3 12:31:32.326 The transaction log for database 'Database3' is full due to 'ACTIVE_TRANSA...
Database3 12:31:32.326 BACKUP DATABASE is terminating abnormally.
Database3 12:31:32.326 Cannot open backup device 'D:\Backups\Database3\Database3_201408260031_bac...
Database3 12:31:32.326 VERIFY DATABASE is terminating abnormally.
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句