php backup sqlite3 db issue

Question

I'm attempting to backup a few sqlite3 databases via a small php script. This script successfully outputs all the files of the correct size into the proper location. The problem arises (And I've only just realized this today when trying to migrate my website to a new location) that these backups do not work.

When I go to import these backups using phpLiteAdmin, I get an error near "SQLite": syntax error

If I open this file in notepad++, all the data is there. The file starts with SQLite format 3 which I believe is causing the issue. But removing this on a copy of this backup does not fix the issue.

I have tried many different ways to backup the databases, all using PHP's exec() command to execute sqlite3 code.

Here is my current backup script. This basically loops through all the databases and backs them up into their own folder with a timestamp in a backups folder. I am using sqlite3's .backup which outputs the file, currently with a .sql extension. I have also tried .schema and .dump but neither of these output anything.

I am at a complete loss here, I'm lucky I still have the original databases in-tact, but I need to be able to automatically back up these databases to be restored at a later date if needed.

    <?
$today = date("m-d-Y");
$time = date("g:iA");

backup("db");
backup("development");
backup("inventory");
backup("permissions");
backup("reports");
backup("rollcall");
backup("upc");

$files = getDirContents('backups');
$start = "/home5/aspiretw/public_html/myticketroll/otherdb/modelfinder/pages/db/";

foreach($files as $value) {
    $value = substr($value, strlen($start));
    @$act = explode("_", $value);
    @$act = explode("/", $act[0]);

    if(strtotime("$act[2] 17:14:40") > strtotime('-7 days')) {
        unlink($value);
    }
}

function backup($db) {

    global $today, $time;

    $dir = "backups/$db/";
    if (!is_dir($dir)) {
        mkdir($dir, 0777, true);
    }

    $path = $dir.$today."_".$db."_".$time.".sql";

    exec("sqlite3 '$db' '.backup '".$path."''");
}

function getDirContents($dir, &$results = array()){
    $files = scandir($dir);

    foreach($files as $key => $value){
        $path = realpath($dir.DIRECTORY_SEPARATOR.$value);
        if(!is_dir($path)) {
            $results[] = $path;
        } else if($value != "." && $value != "..") {
            getDirContents($path, $results);
            if(substr($path, -4) == ".bak") {
                $results[] = $path;
            }
        }
    }

    return $results;
}
?>

CL. · Accepted Answer · 2017-06-05 18:28:05Z

2

The .backup command simply copies the database file (while correctly handling transaction isolation).

To restore it, simply copy it back.

answered Jun 5, 2017 at 18:28

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php backup sqlite3 db issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related