CFS_developer
asked on
How to use Perl DBI for SQL Server backup
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:
And here's stderr
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});
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER