How to use Perl DBI for SQL Server backup

Question

I am trying to backup a Sql Server database via Perl DBI. Calling "backup database" via do() runs but usually does not produce a backup. Calling do() creates a backup when ODBC tracing is enabled. Calling prepare() and execute() fails.

I am using ActiveState Perl on Windows 7 Professional and Sql Server 2008 R2. Here is a link to download source code and various logs http://www.fileswap.com/dl/4VnYbCdk6R/ToZip.zip.html (Click on slow download)

Here is the summary of logs

BothTraces made 3 backups but program aborted
-rwx------+ 1 SYSTEM SYSTEM 160256 Jan 16 09:39 perlEasy.bak
-rwx------+ 1 SYSTEM SYSTEM 160256 Jan 16 09:39 perlHard.bak
-rwx------+ 1 SYSTEM SYSTEM 160256 Jan 16 09:38 queryOS.bak

NoTracing made 1 backup, program aborted
-rwx------+ 1 SYSTEM SYSTEM 160256 Jan 16 10:15 queryOS.bak

DbiTrace made 1 backup, program aborted
-rwx------+ 1 SYSTEM SYSTEM 160256 Jan 16 10:19 queryOS.bak

OdbcTrace made 3 backup but program aborted
-rwx------+ 1 SYSTEM SYSTEM 159744 Jan 16 10:21 perlEasy.bak
-rwx------+ 1 SYSTEM SYSTEM 160256 Jan 16 10:21 perlHard.bak
-rwx------+ 1 SYSTEM SYSTEM 160256 Jan 16 10:21 queryOS.bak

Here's my program:

#!perl -w
#try to use DBI for SQL Server backup 

#connect to database server 
   use v5.14;        #enable modern Perl 
   use DBI;          #database interface
   my $dbHandle = DBI->connect("dbi:ODBC:Driver={SQL Server};Server=DavidZ") or die;   #dbi prints a detailed error message 
   $dbHandle->{RaiseError} = 1;    #enable failure on DBI problems; obviates the need for "or die" with every DBI call
   $dbHandle->{PrintError} = 0;    #don't duplicate error messages 

#enable debugging 
   $dbHandle->trace(1); 
   $dbHandle->{odbc_trace} = 1;                                  #not helpful 
   $dbHandle->{odbc_trace_file} = 'C:\David\dump\tracer.file';   #not helpful 

#run a SQL command to verify connection, write a note to ERRORLOG  
   $dbHandle->do ('use master');
   $dbHandle->do ("raiserror ('New run of backup.pl', 0, 0) with log"); 
   say 'Verified database connection';

#backup commands 
   my $perlEasy = "backup database dz to disk='C:\\David\\dump\\perlEasy.bak'";
   my $perlHard = "backup database dz to disk='C:\\David\\dump\\perlHard.bak'";
   my $queryOS  = "backup database dz to disk='C:\\David\\dump\\queryOS.bak'";

#make a backup via sqlcmd.  this works 
   my $sysCmd = "sqlcmd -Q \"$queryOS\" "; 
   system ($sysCmd) == 0
      or die "The following system command failed:  $sysCmd \n"; 
   say 'Created backup via sqlcmd';

#try to make a backup via DBI 
   $dbHandle->do ($perlEasy);   #runs silently but does not produce a backup file 
   say 'Created backup the easy way';

#more complicated DBI method 
   my $stHandle = $dbHandle->prepare($perlHard);
   $stHandle->execute();     #statement starts a backup then fails, no furter code is executed 

   do 
   {
     #print dbi results 
     say "DBI reports $DBI::errstr"; 

     while (my @row = $stHandle->fetchrow_array())   #recommended by someone, but makes no sense for a backup 
        { say "Returned values: @row" }              #recommended by someone, but makes no sense for a backup 
   } while ($stHandle->{odbc_more_results});

   say 'Created backup the hard way'; 

#program completion
   say 'Program completed successfully';
   exit 0;

So you can't find C:\David\dump\perlEasy.bak on the local SQL Server or any other backup files? Dos the SQL Server log (accessible from management studio) say that the backup was successful? — Nick.McDermaid
I maintain DBD::ODBC. Some of that advice comes from metacpan.org/pod/release/MJEVANS/DBD-ODBC-1.45/…. Could you run the script again after first setting the env var DBI_TRACE with set DBI_TRACE=DBD=x.log and paste the end of the log somewhere. It would also be useful to enable tracing in the ODBC administrator and for us to see the end of that. — bohica

bohica bohica · Accepted Answer · 2014-01-25T10:11:02

There is nothing wrong with the Perl code you show. However, the ODBC trace file shows that DBD::ODBC made these calls just before the error:

SQLPrepare backup database dz to disk='C:\David\dump\perlHard.bak'
SQLExecute returns SQL_SUCCESS_WITH_INFO and
  Processed 208 pages for database 'dz', file 'dz_test' on file 1. (4035) 
then a few calls for various handles to SQLErrorW
SQLRowCount returns ok and -1 for row count
SQLNumResultCols returns SQL_ERROR and ]Invalid cursor state

I cannot for the life of me see how this is an invalid cursor state (look the valid state transitions for ODBC up yourself) so I'd have to say this looks like a bug in the SQL Server ODBC driver you are using. You could try getting a newer one or use the SQL Server native client driver instead (you've probably got both already).

You can ignore the errors in your sql server log as they are correct, error 1235 is ERROR_REQUEST_ABORTED which it was.

How to use Perl DBI for SQL Server backup

1 Answers