The Enterprise Backup Batch

If a piece of software is described in any way, shape or form as being "enterprise", it's a safe bet that you don't actually want to use it. As a general rule, "enterprise" software packages mix the Inner-Platform Effect with trying to be all things to all customers, with thousands upon thousands of lines of legacy code that can't be touched because at least one customer depends on those quirks. There doesn't tend to be much competition in the "enterprise" space, so none of the vendors actually put any thought into making their products good. That's what salesbeasts and lawyers are for.

Kristoph M supports a deployment of Initech's data warehouse system. Since this system is a mix of stored procedures and SSIS packages, Kristoph can actually read a good portion of the code which makes the product work. They just choose not to. And that's usually a good choice.

But one day, while debugging, Kristoph decided that they needed a simple answer to a simple question: "For a SQLAgent Job, how do you create a backup of the database with the day appended to the filename?"

SQLAgent is SQL Server's scheduling system, used for triggering tasks. SSIS is SQL Server's "drag and drop" dataflow tool, designed to let users draw data pipelines to handle extract-transform-load tasks.

In this case, the SQLAgent job's first step was to launch an SSIS package. Already, we're in questionable territory. SSIS is, as stated, an ETL tool. Yes, you can use it to extract data, it's not really meant as a replacement for an actual database backup.

The good news is that this SSIS package doesn't actually do anything to backup the database. Instead, it contains a single task, and it isn't a data flow task, it's a "Visual Basic Script Task". Yes, SSIS lets you run a stripped down Visual Basic dialect in its context. What does this task do?

 Public Sub Main() ' ' Add your code here ' Dim sToday As Date = Now Dim sDay As String = sToday.Day.ToString If CInt(sDay) < 10 Then sDay = "0" & sDay Dim sMonth As String = MonthName(Month(sToday), True) Dim sYear As String = Year(sToday).ToString Dim sPara1 As String = sDay '& sMonth & sYear Dim sPath As String = "D:\Initech\DailyProcess\" Using fso As StreamWriter = New StreamWriter(sPath & "runBackupBatch.bat") fso.WriteLine(sPath & "DailyExtractBackup.bat " & sPara1) fso.Close() End Using Dts.TaskResult = ScriptResults.Success End Sub

This figures out the current day, and then writes out a runBackupBatch.bat file with contents like this:

D:\Initech\DailyProcess\DailyExtractBackup.bat 02

Once that step is completed, the SQLAgent job continues, and runs the runBackupBatch.bat, which in turn runs DailyExtractBackup.bat, which does this:

D:\Initech\DailyProcess\DailyExtractBackup.bat@echo off@echo Dumping DailyExtract database...osql -E -Slocalhost -oD:\Initech\DailyProcess\DailyExtractDump.log -Q"backup database DailyExtract to DISK='D:\Initech\MSSQL\Backup\DailyExtractDump%1.bak' with INIT"if errorlevel 1 goto dumperrorREM Check for SQL Errorsfindstr "Msg" D:\Initech\DailyProcess\DailyExtractDump.logif not errorlevel 1 goto dumperror:OK@echo All Done!!exit 0:dumperror@echo Error dumping database.exit 1

The osql call is about the first reasonable step in this process. That actually does the backup using SQL server's backup tools. Then again, the mechanism to see if there were any errors in the logfile is troubling. findstr sets the errorlevel to 1 if Msg is not found in the log file. So, if Msg is not not found in the logfile, we'll go to dumperror.

After reading through this process, Kristoph decide it was best to take a step outside, get some air, and stop thinking about the other horrible things that might be lurking in Initech's data warehouse product.

[Advertisement] Forget logs. Next time you're struggling to replicate error, crash and performance issues in your apps - Think Raygun! Installs in minutes. Learn more.