August 11, 2010 at 6:43 pm
I would really appreciate any help I can get with this. The situation I'm facing is this:
The customer has decided to perform all database backups outside of SQL. They use a series of batch files and along with those they produce a corresponding text file with some verbiage stating the success of failure of the backup. The text files themselves are named 'server+db.prn' the context of the file simply states how much data was backed up and weather or not it was successful; there is no mention of the server name in the context of the file. They then send these text files over to a central repository. My dilema is to produce a report that will concatenate all of these text files and display Server Name, Database Name and either Success or Failure and then email this to a distribution group. I have figured out how to concatenate the context of all the files into one. What I do not know how to do is pull the server and database names from each text file name and then merge them into an excel file and I do not know how to select a word like 'success' or 'failed' from the context of a text file and specify just that one word bit of information in a spread sheet. I have thought about importing the context of each separate flat file into a database table and attempt to write some T-SQL statement out to do this but I'm still not sure how to take and put the name of the text file into a table. Maybe I'm way off base here but I am new to all of this. Please help if you can. Perhaps there is a better way I am not thinking of. I would appreciate any assistance I can get. So thank you in advance.
August 12, 2010 at 1:37 pm
Can you post an example of what these files look like?
Also, why in the world would they insist on doing batch files for the database backups instead of something that SQL Server natively does? It seems like re-inventing the wheel for no apparent reason.
August 12, 2010 at 2:44 pm
Thanks to answer your question as to why they would do this with batch files; they never had a SQL DBA and had their SE's build the servers and somewhere down the line one of them created this set of batch files and it became a standard. Now they hired us to come in and to be quite honest I was a Information Security guy they brought in to implement OIM but several years ago before SQL 2005 was around I had a little bit of experience with SQL 2000 and SQL 7 (and even 6.5) so they dubbed me SQL DBA and I suppose some people know how that goes. Anyway they are used to doing the backups this way and refuse to listen to any alternatives. Thier shop/equipment their call and I have to adapt. Now you've heard my story of woah 🙁 sniff sniff huh. Please don't play the violin for me (hehe) Anyway here is a sample of the file context and the name of the file is again in the format of server name_Database name.prn again thank you so much.
1> 2> Processed 752 pages for database 'CMS', file 'CMS_Data' on file 1.
Processed 1 pages for database 'CMS', file 'CMS_Log' on file 1.
BACKUP DATABASE successfully processed 753 pages in 0.913 seconds
(6.748 MB/sec).
August 16, 2010 at 2:47 pm
Maybe a different direction from where you were but since you're on 2008 I'd take advantage of the availability of Powershell.
$today = get-date -format yyyyMMdd
$concatenatedFile = "conCatFiles_$today.prn"
$csvOut = "backupReport_$today.csv"
foreach ($file in get-childitem -filter "*.prn") {
($server, $dbName) = [regex]::split($file.Name, "_")
$dbName = [regex]::replace($dbName, "(.+\w)\.prn", '$1')
# the next three lines are optional if you're running from the command prompt
# and want to see where you are in the file list
$file.Name
"server`t" + $server
"db`t" + $dbName
$content = get-content -path $file.Name
if ($content | %{[regex]::matches($_, " successfully processed ") } ) {
write-host "Server: $server`tDatabase: $dbName Backup successful"
"$server`t$dbName`tSuccess" | out-file -append -filepath $csvOut
}
Else { write-host "Server: $server`tDatabase: $dbName Backup Failure"
"$server`t$dbName`tFailure" | out-file -append -filepath $csvOut
}
"$file.Name `n" + $content + "`n`n" | out-file -append -filepath $concatenatedFile
}
Instead of csv Powershell can write directly to an Excel doc but that's more than I had time for today.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply