Solved

How to use Perl DBI for SQL Server backup

Posted on 2014-01-10
2
886 Views
Last Modified: 2014-01-24
I am trying to backup a Sql Server database via Perl DBI.  Calling "backup database" via do() runs but does not produce a backup.  Calling prepare() and execute() fails.

I am using ActiveState Perl on Windows 7 Professional.  

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 
   $dbHandle->do ('use master');

#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"; 

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

#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});

Open in new window


And here's stderr

   DBI::db=HASH(0x2915c88) trace level set to 0x0/1 (DBI @ 0x0/0) in DBI 1.622-ithread (pid 5768)
$h->{'odbc_trace'}=1 ignored for invalid driver-specific attribute
      info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (SQL-01000)' (err#0)
   <- STORE('odbc_trace', 1)= ( '' ) [1 items] at backup.pl line 13
$h->{'odbc_trace_file'}='C:\David\dump\tracer.file' ignored for invalid driver-specific attribute
      info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (SQL-01000)' (err#0)
   <- STORE('odbc_trace_file', 'C:\David\dump\tracer.file')= ( '' ) [1 items] at backup.pl line 14
   !! info: '' CLEARED by call to do method
   !! info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'. (SQL-01000)' (err#0)
   <- do('use master')= ( -1 ) [1 items] at backup.pl line 17
   !! info: '' CLEARED by call to do method
   !! info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Processed 208 pages for database 'dz', file 'dz_test' on file 1. (SQL-01000)' (err#0)
   <- do('backup database dz to disk='C:\David\dump\perlEasy.bak'')= ( -1 ) [1 items] at backup.pl line 30
   !! info: '' CLEARED by call to prepare method
   <- prepare('backup database dz to disk='C:\David\dump\perlHard.bak'')= ( DBI::st=HASH(0x29160c0) ) [1 items] at backup.pl line 33
   !! ERROR: 1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Processed 208 pages for database 'dz', file 'dz_test' on file 1. (SQL-01000) [state was 01000 now 24000]
[Microsoft][ODBC SQL Server Driver]Invalid cursor state (SQL-24000)' (err#1)
   <- execute= ( undef ) [1 items] at backup.pl line 34
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Processed 208 pages for database 'dz', file 'dz_test' on file 1. (SQL-01000) [state was 01000 now 24000]
[Microsoft][ODBC SQL Server Driver]Invalid cursor state (SQL-24000) at C:\David\dump\backup.pl line 34.
      ERROR: 1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Processed 208 pages for database 'dz', file 'dz_test' on file 1. (SQL-01000) [state was 01000 now 24000]
[Microsoft][ODBC SQL Server Driver]Invalid cursor state (SQL-24000)' (err#1)
   <- DESTROY(DBI::st=HASH(0x2916078))= ( undef ) [1 items] at backup.pl line 34
      ERROR: 1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Processed 208 pages for database 'dz', file 'dz_test' on file 1. (SQL-01000) [state was 01000 now 24000]
[Microsoft][ODBC SQL Server Driver]Invalid cursor state (SQL-24000)' (err#0)
   <- DESTROY(DBI::db=HASH(0x2915c88))= ( undef ) [1 items] at backup.pl line 34

Open in new window

0
Comment
Question by:CFS_developer
2 Comments
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 39771801
It depends on what's implemented in the DBD::ODBC module and the ODBC driver you are using.  I suspect "backup database" is not implemented so you'll have to find another way to do a backup.
0
 

Author Comment

by:CFS_developer
ID: 39807711
This is definitely a difficult question (which is why I asked it).  It's my guess that there is either a bug in DBD::ODBC or SQL Server or ...   I managed to locate the developer who maintains DBD::ODBC and ask for his input.  Hopefully he will find a solution.  See http://stackoverflow.com/questions/21053671/how-to-use-perl-dbi-for-sql-server-backup
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question