title: SQLite Backup Strategy
body: <p>I'm trying to backup my sqlite database from a cronjob that runs in 5 minute intervals. The database is "live", so there are running queries at the time I want to perform the backup.</p>
  
  <p>I want to be sure, that the database is in good shape when I backup it so that I can rely on the backup. </p>
  
  <p><strong>My current strategy (in pseudocode):</strong></p>
  
  <pre><code>function backup()
  {
      #try to acquire the lock for 2 seconds, then check the database integrity
      sqlite3 mydb.sqlite '.timeout 2000' 'PRAGMA integrity_check;'
      if (integrity is ok and database was not locked)
      {
          #perform the backup to backup.sqlite
          sqlite3 mydb.sqlite '.timeout 2000' '.backup backup.sqlite'
  
          if (backup could be performed)
          {
              #Check the consistency of the backup database
              sqlite3 backup.sqlite 'PRAGMA integrity_check;'
              if (ok)
              {
                  return true;
              }
          }
      }
  
      return false;
  }
  </code></pre>
  
  <p>Now, there are some problems with my strategy:</p>
  
  <ul>
  <li>If the live database is locked, I run into problems because I cannot perform the backup then. Maybe a transaction could help there?</li>
  <li>If something goes wrong between the <code>PRAGMA integrity_check;</code> and the backup, I'm f*cked.</li>
  </ul>
  
  <p>Any ideas? And by the way, what is the difference between the <code>sqlite3 .backup</code> and a good old <code>cp mydb.sqlite mybackup.sqlite</code> ?</p>
  
  <p><strong>[edit]</strong> I'm running nodejs on an embedded system, so if someone suggests the <a href="http://www.sqlite.org/c3ref/backup_finish.html" rel="nofollow">sqlite online backup api</a> with some ruby wrapper - no chance ;(</p>