Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Backup Sql databse

by dmsparts (Sexton)
on Feb 02, 2012 at 10:27 UTC ( #951405=perlquestion: print w/ replies, xml ) Need Help??
dmsparts has asked for the wisdom of the Perl Monks concerning the following question:

Hi

I am trying to write a script that will trigger a backup of an SQL database from a remote copmuter. The script i have below seems to work, i dont get any errors. however, the backup file appears in the specified directory then dissapears after about 4 or 5 seconds. i need to be able to call this script from a browser so anyone can do the backup. the file would then be accesible on a network share to be copied to a USB drive.

does anyone have any ideas why it would do this??

#!/usr/bin/perl use CGI::Carp qw(fatalsToBrowser); use DBI; print "Content-type: text/html\n\n"; my $dbs = "DBI:ODBC:DRIVER={SQL Server};SERVER={dmssql01};DATABASE={sa +ge200_dmstestconversion}"; my ($username, $password) = ('user', 'passwd'); my $dbh = DBI->connect($dbs, $username, $password) or die "$DBI::errst +r\n"; my $sth = $dbh->prepare( 'backup database sage200 to DISK = \'d:\sage\ +sage200data\sagebackup2.bak\' ;') or die "Couldn't prepare statement: + " . $dbh->errstr; $sth->execute() or die "execute failed: " . $sth->errstr(); $sth->finish(); $dbh->disconnect(); print "Backup Completed";

Thanks for your help

Comment on Backup Sql databse
Download Code
Re: Backup Sql databse
by Corion (Pope) on Feb 02, 2012 at 10:31 UTC

    I don't know why your script "fails" or why the file gets removed (maybe by the database server). Consider looking at the webserver error logs or the database server error logs.

    In the long run, you don't want to run your backup(s) with the hope that the user keeps their browser window open and that the browser does not time out. See Watching Long Processes Through CGI for a technique to show a "please wait" or a "backup progress" page while the backup gets generated.

      Indeed - backups are the kind of thing cron jobs (or on Windows, Scheduled Tasks) were invented for.

      dmsparts appears to be using Microsoft SQL Server. There are facilities for scheduling regular backups of a database built right into the SQL Server management console.

        While such facilities exist I think it's clear that OP wants to do this dynamically (not as a scheduled task) and remotely. The TSQL method seems like a reasonable solution to this.

        If you are using SQL Server Express I believe Maintenance Plans are not available.

Re: Backup Sql databse
by mje (Curate) on Feb 02, 2012 at 11:47 UTC
      Hi, i think that this is the problem. however i can't work out how to use the suggestion on my script, i have done this,
      #!/usr/bin/perl use CGI::Carp qw(fatalsToBrowser); use DBI; print "Content-type: text/html\n\n"; my $dbs = "DBI:ODBC:DRIVER={SQL Server};SERVER={dmssql01};DATABASE={sa +ge200_dmstestconversion}"; my ($username, $password) = ('user', 'passwd'); my $dbh = DBI->connect($dbs, $username, $password) or die "$DBI::errst +r\n"; my $sth = $dbh->prepare( 'backup database Sage200_DMSTestConversion to + DISK = \'d:\sage\sage200data\sagebackup2.bak\' ;') or die "Couldn't +prepare statement: " . $dbh->errstr; $sth->execute() or die "execute failed: " . $sth->errstr(); do { while (my @row = $sth->fetchrow_array()) { print "backing up,<br>"; print @row; } } while ($sth->{odbc_more_results}); my @error = $sth->err; print @error; $sth->finish(); $dbh->disconnect(); print "Backup Completed";
      This still gives me the same output to screen and doesn't backup. Thanks

        Take this out of CGI and just run it from the command line until you get it working. The backup command outputs print statements which are usually received via an error handler. Look at the script http://cpansearch.perl.org/src/MJEVANS/DBD-ODBC-1.34_2/examples/backup_restore.pl which I know works - you might want to comment out the restore first though. The main point is that any procedure you call has to run to completion and if nocount is off and the proc issues output the client end needs to read the output or the proc does not complete.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://951405]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2014-10-31 12:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (217 votes), past polls