diff options
Diffstat (limited to 'www/backup.html')
| -rw-r--r-- | www/backup.html | 491 |
1 files changed, 491 insertions, 0 deletions
diff --git a/www/backup.html b/www/backup.html new file mode 100644 index 0000000..1d917f5 --- /dev/null +++ b/www/backup.html @@ -0,0 +1,491 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>SQLite Backup API</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> + + + +<style> + /* Formatting for the blocks containing the example code */ + pre a:visited, pre a:link { text-decoration: none ; color: #40534b } + pre { + background: #F3F3F3; + float: right; + padding: 1ex 2ex; + margin-left: 1em; + border: solid black 1px; + } + h1,h2 { clear: both } +</style> + +<div class=fancy> +<h1>Using the SQLite Online Backup API</h1> + +<p> + Historically, backups (copies) of SQLite databases have been created + using the following method: + +<ol> + <li> Establish a shared lock on the database file using the SQLite API (i.e. + the shell tool). + <li> Copy the database file using an external tool (for example the unix 'cp' + utility or the DOS 'copy' command). + <li> Relinquish the shared lock on the database file obtained in step 1. +</ol> + +<p> + This procedure works well in many scenarios and is usually very + fast. However, this technique has the following shortcomings: + +<ul> + <li> Any database clients wishing to write to the database file while a + backup is being created must wait until the shared lock is + relinquished. + + <li> It cannot be used to copy data to or from in-memory databases. + + <li> If a power failure or operating system failure occurs while copying + the database file the backup database may be corrupted following + system recovery. +</ul> + +<p> + The <a href="c3ref/backup_finish.html#sqlite3backupinit">Online Backup API</a> was created to + address these concerns. The online backup API allows the contents of + one database to be copied into another database file, replacing any + original contents of the target database. The copy operation may be + done incrementally, in which case the source database does not need + to be locked for the duration of the copy, only for the brief periods + of time when it is actually being read from. This allows other database + users to continue without excessive delays while a backup of an online + database is made. +<p> + The effect of completing the backup call sequence is to make the + destination a bit-wise identical copy of the source database as it + was when the copying commenced. (The destination becomes a "snapshot.") + +<p> + The online backup API is <a href="c3ref/backup_finish.html#sqlite3backupinit">documented here</a>. + The remainder of this page contains two C language examples illustrating + common uses of the API and discussions thereof. Reading these examples + is no substitute for reading the API documentation! + +<p> + Update: The <a href="lang_vacuum.html#vacuuminto">VACUUM INTO</a> command introduced in + SQLite version 3.27.0 (2019-02-07) can serve as an + alternative to the backup API. + +<h2>Example 1: Loading and Saving In-Memory Databases</h2> + +<pre> +<span style="color:blue;font-style:italic">/*</span> +<span style="color:blue;font-style:italic">** This function is used to load the contents of a database file on disk </span> +<span style="color:blue;font-style:italic">** into the "main" database of open database connection pInMemory, or</span> +<span style="color:blue;font-style:italic">** to save the current contents of the database opened by pInMemory into</span> +<span style="color:blue;font-style:italic">** a database file on disk. pInMemory is probably an in-memory database, </span> +<span style="color:blue;font-style:italic">** but this function will also work fine if it is not.</span> +<span style="color:blue;font-style:italic">**</span> +<span style="color:blue;font-style:italic">** Parameter zFilename points to a nul-terminated string containing the</span> +<span style="color:blue;font-style:italic">** name of the database file on disk to load from or save to. If parameter</span> +<span style="color:blue;font-style:italic">** isSave is non-zero, then the contents of the file zFilename are </span> +<span style="color:blue;font-style:italic">** overwritten with the contents of the database opened by pInMemory. If</span> +<span style="color:blue;font-style:italic">** parameter isSave is zero, then the contents of the database opened by</span> +<span style="color:blue;font-style:italic">** pInMemory are replaced by data loaded from the file zFilename.</span> +<span style="color:blue;font-style:italic">**</span> +<span style="color:blue;font-style:italic">** If the operation is successful, SQLITE_OK is returned. Otherwise, if</span> +<span style="color:blue;font-style:italic">** an error occurs, an SQLite error code is returned.</span> +<span style="color:blue;font-style:italic">*/</span> +int loadOrSaveDb(<a href="c3ref/sqlite3.html">sqlite3</a> *pInMemory, const char *zFilename, int isSave){ + int rc; <span style="color:blue;font-style:italic">/* Function return code */</span> + <a href="c3ref/sqlite3.html">sqlite3</a> *pFile; <span style="color:blue;font-style:italic">/* Database connection opened on zFilename */</span> + <a href="c3ref/backup.html">sqlite3_backup</a> *pBackup; <span style="color:blue;font-style:italic">/* Backup object used to copy data */</span> + <a href="c3ref/sqlite3.html">sqlite3</a> *pTo; <span style="color:blue;font-style:italic">/* Database to copy to (pFile or pInMemory) */</span> + <a href="c3ref/sqlite3.html">sqlite3</a> *pFrom; <span style="color:blue;font-style:italic">/* Database to copy from (pFile or pInMemory) */</span> + + <span style="color:blue;font-style:italic">/* Open the database file identified by zFilename. Exit early if this fails</span> + <span style="color:blue;font-style:italic">** for any reason. */</span> + rc = <a href="c3ref/open.html">sqlite3_open</a>(zFilename, &pFile); + if( rc==SQLITE_OK ){ + + <span style="color:blue;font-style:italic">/* If this is a 'load' operation (isSave==0), then data is copied</span> + <span style="color:blue;font-style:italic">** from the database file just opened to database pInMemory. </span> + <span style="color:blue;font-style:italic">** Otherwise, if this is a 'save' operation (isSave==1), then data</span> + <span style="color:blue;font-style:italic">** is copied from pInMemory to pFile. Set the variables pFrom and</span> + <span style="color:blue;font-style:italic">** pTo accordingly. */</span> + pFrom = (isSave ? pInMemory : pFile); + pTo = (isSave ? pFile : pInMemory); + + <span style="color:blue;font-style:italic">/* Set up the backup procedure to copy from the "main" database of </span> + <span style="color:blue;font-style:italic">** connection pFile to the main database of connection pInMemory.</span> + <span style="color:blue;font-style:italic">** If something goes wrong, pBackup will be set to NULL and an error</span> + <span style="color:blue;font-style:italic">** code and message left in connection pTo.</span> + <span style="color:blue;font-style:italic">**</span> + <span style="color:blue;font-style:italic">** If the backup object is successfully created, call backup_step()</span> + <span style="color:blue;font-style:italic">** to copy data from pFile to pInMemory. Then call backup_finish()</span> + <span style="color:blue;font-style:italic">** to release resources associated with the pBackup object. If an</span> + <span style="color:blue;font-style:italic">** error occurred, then an error code and message will be left in</span> + <span style="color:blue;font-style:italic">** connection pTo. If no error occurred, then the error code belonging</span> + <span style="color:blue;font-style:italic">** to pTo is set to SQLITE_OK.</span> + <span style="color:blue;font-style:italic">*/</span> + pBackup = <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init</a>(pTo, "main", pFrom, "main"); + if( pBackup ){ + (void)<a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step</a>(pBackup, -1); + (void)<a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish</a>(pBackup); + } + rc = <a href="c3ref/errcode.html">sqlite3_errcode</a>(pTo); + } + + <span style="color:blue;font-style:italic">/* Close the database connection opened on database file zFilename</span> + <span style="color:blue;font-style:italic">** and return the result of this function. */</span> + (void)<a href="c3ref/close.html">sqlite3_close</a>(pFile); + return rc; +} +</pre> + + +<p> + The C function to the right demonstrates one of the simplest, + and most common, uses of the backup API: loading and saving the contents + of an in-memory database to a file on disk. The backup API is used as + follows in this example: + + <ol> + <li>Function <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a> is called to create an <a href="c3ref/backup.html">sqlite3_backup</a> + object to copy data between the two databases (either from a file and + into the in-memory database, or vice-versa). + <li>Function <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> is called with a parameter of + <tt>-1</tt> to copy the entire source database to the destination. + <li>Function <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> is called to clean up resources + allocated by <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a>. + </ol> + +<p><b>Error handling</b> + +<p> + If an error occurs in any of the three main backup API routines + then the <a href="rescode.html">error code</a> and <a href="c3ref/errcode.html">message</a> are attached to + the destination <a href="c3ref/sqlite3.html">database connection</a>. + Additionally, if + <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> encounters an error, then the <a href="rescode.html">error code</a> is returned + by both the <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> call itself, and by the subsequent call + to <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a>. So a call to <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> + does not overwrite an <a href="rescode.html">error code</a> stored in the destination + <a href="c3ref/sqlite3.html">database connection</a> by <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a>. This feature + is used in the example code to reduce amount of error handling required. + The return values of the <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> and <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> + calls are ignored and the error code indicating the success or failure of + the copy operation collected from the destination <a href="c3ref/sqlite3.html">database connection</a> + afterward. + +<p><b>Possible Enhancements</b> + +<p> + The implementation of this function could be enhanced in at least two ways: + + <ol> + <li> Failing to obtain the lock on database file zFilename (an <a href="rescode.html#busy">SQLITE_BUSY</a> + error) could be handled, and + <li> Cases where the page-sizes of database pInMemory and zFilename are + different could be handled better. + </ol> + +<p> + Since database zFilename is a file on disk, then it may be accessed + externally by another process. This means that when the call to + sqlite3_backup_step() attempts to read from or write data to it, it may + fail to obtain the required file lock. If this happens, this implementation + will fail, returning SQLITE_BUSY immediately. The solution would be to + register a busy-handler callback or + timeout with <a href="c3ref/sqlite3.html">database connection</a> pFile + using <a href="c3ref/busy_handler.html">sqlite3_busy_handler()</a> or <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a> + as soon as it is opened. If it fails to obtain a required lock immediately, + <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> uses any registered busy-handler callback or timeout + in the same way as <a href="c3ref/step.html">sqlite3_step()</a> or <a href="c3ref/exec.html">sqlite3_exec()</a> does. + +<p> + Usually, it does not matter if the page-sizes of the source database and the + destination database are different before the contents of the destination + are overwritten. The page-size of the destination database is simply changed + as part of the backup operation. The exception is if the destination database + happens to be an in-memory database. In this case, if the page sizes + are not the same at the start of the backup operation, then the operation + fails with an SQLITE_READONLY error. Unfortunately, this could occur when + loading a database image from a file into an in-memory database using + function loadOrSaveDb(). + +<p> + However, if in-memory database pInMemory has just been opened (and is + therefore completely empty) before being passed to function loadOrSaveDb(), + then it is still possible to change its page size using an SQLite "PRAGMA + page_size" command. Function loadOrSaveDb() could detect this case, and + attempt to set the page-size of the in-memory database to the page-size + of database zFilename before invoking the online backup API functions. + +<h2>Example 2: Online Backup of a Running Database</h2> + +<pre> +<span style="color:blue;font-style:italic">/*</span> +<span style="color:blue;font-style:italic">** Perform an online backup of database pDb to the database file named</span> +<span style="color:blue;font-style:italic">** by zFilename. This function copies 5 database pages from pDb to</span> +<span style="color:blue;font-style:italic">** zFilename, then unlocks pDb and sleeps for 250 ms, then repeats the</span> +<span style="color:blue;font-style:italic">** process until the entire database is backed up.</span> +<span style="color:blue;font-style:italic">** </span> +<span style="color:blue;font-style:italic">** The third argument passed to this function must be a pointer to a progress</span> +<span style="color:blue;font-style:italic">** function. After each set of 5 pages is backed up, the progress function</span> +<span style="color:blue;font-style:italic">** is invoked with two integer parameters: the number of pages left to</span> +<span style="color:blue;font-style:italic">** copy, and the total number of pages in the source file. This information</span> +<span style="color:blue;font-style:italic">** may be used, for example, to update a GUI progress bar.</span> +<span style="color:blue;font-style:italic">**</span> +<span style="color:blue;font-style:italic">** While this function is running, another thread may use the database pDb, or</span> +<span style="color:blue;font-style:italic">** another process may access the underlying database file via a separate </span> +<span style="color:blue;font-style:italic">** connection.</span> +<span style="color:blue;font-style:italic">**</span> +<span style="color:blue;font-style:italic">** If the backup process is successfully completed, SQLITE_OK is returned.</span> +<span style="color:blue;font-style:italic">** Otherwise, if an error occurs, an SQLite error code is returned.</span> +<span style="color:blue;font-style:italic">*/</span> +int backupDb( + <a href="c3ref/sqlite3.html">sqlite3</a> *pDb, <span style="color:blue;font-style:italic">/* Database to back up */</span> + const char *zFilename, <span style="color:blue;font-style:italic">/* Name of file to back up to */</span> + void(*xProgress)(int, int) <span style="color:blue;font-style:italic">/* Progress function to invoke */ </span> +){ + int rc; <span style="color:blue;font-style:italic">/* Function return code */</span> + <a href="c3ref/sqlite3.html">sqlite3</a> *pFile; <span style="color:blue;font-style:italic">/* Database connection opened on zFilename */</span> + <a href="c3ref/backup.html">sqlite3_backup</a> *pBackup; <span style="color:blue;font-style:italic">/* Backup handle used to copy data */</span> + + <span style="color:blue;font-style:italic">/* Open the database file identified by zFilename. */</span> + rc = <a href="c3ref/open.html">sqlite3_open</a>(zFilename, &pFile); + if( rc==SQLITE_OK ){ + + <span style="color:blue;font-style:italic">/* Open the <a href="c3ref/backup.html">sqlite3_backup</a> object used to accomplish the transfer */</span> + pBackup = <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init</a>(pFile, "main", pDb, "main"); + if( pBackup ){ + + <span style="color:blue;font-style:italic">/* Each iteration of this loop copies 5 database pages from database</span> + <span style="color:blue;font-style:italic">** pDb to the backup database. If the return value of backup_step()</span> + <span style="color:blue;font-style:italic">** indicates that there are still further pages to copy, sleep for</span> + <span style="color:blue;font-style:italic">** 250 ms before repeating. */</span> + do { + rc = <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step</a>(pBackup, 5); + xProgress( + <a href="c3ref/backup_finish.html#sqlite3backupremaining">sqlite3_backup_remaining</a>(pBackup), + <a href="c3ref/backup_finish.html#sqlite3backuppagecount">sqlite3_backup_pagecount</a>(pBackup) + ); + if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){ + <a href="c3ref/sleep.html">sqlite3_sleep</a>(250); + } + } while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ); + + <span style="color:blue;font-style:italic">/* Release resources allocated by backup_init(). */</span> + (void)<a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish</a>(pBackup); + } + rc = <a href="c3ref/errcode.html">sqlite3_errcode</a>(pFile); + } + + <span style="color:blue;font-style:italic">/* Close the database connection opened on database file zFilename</span> + <span style="color:blue;font-style:italic">** and return the result of this function. */</span> + (void)<a href="c3ref/close.html">sqlite3_close</a>(pFile); + return rc; +} +</pre> + + +<p> + The function presented in the previous example copies the entire source + database in one call to <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a>. This requires holding a + read-lock on the source database file for the duration of the operation, + preventing any other database user from writing to the database. It also + holds the mutex associated with database pInMemory throughout the copy, + preventing any other thread from using it. The C function in this section, + designed to be called by a background thread or process for creating a + backup of an online database, avoids these problems using the following + approach: + + <ol> + <li>Function <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a> is called to create an <a href="c3ref/backup.html">sqlite3_backup</a> + object to copy data from database pDb to the backup database file + identified by zFilename. + <li>Function <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> is called with a parameter of 5 to + copy 5 pages of database pDb to the backup database (file zFilename). + <li>If there are still more pages to copy from database pDb, then the + function sleeps for 250 milliseconds (using the <a href="c3ref/sleep.html">sqlite3_sleep()</a> + utility) and then returns to step 2. + <li>Function <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> is called to clean up resources + allocated by <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a>. + </ol> + +<p><b>File and Database Connection Locking</b> + +<p> + During the 250 ms sleep in step 3 above, no read-lock is held on the database + file and the mutex associated with pDb is not held. This allows other threads + to use <a href="c3ref/sqlite3.html">database connection</a> pDb and other connections to write to the + underlying database file. + +<p> + If another thread or process writes to the source database while this + function is sleeping, then SQLite detects this and usually restarts the + backup process when sqlite3_backup_step() is next called. There is one + exception to this rule: If the source database is not an in-memory database, + and the write is performed from within the same process as the backup + operation and uses the same database handle (pDb), then the destination + database (the one opened using connection pFile) is automatically updated + along with the source. The backup process may then be continued after the + sqlite3_sleep() call returns as if nothing had happened. + +<p> + Whether or not the backup process is restarted as a result of writes to + the source database mid-backup, the user can be sure that when the backup + operation is completed the backup database contains a consistent and + up-to-date snapshot of the original. However: + + <ul> + <li> Writes to an in-memory source database, or writes to a file-based + source database by an external process or thread using a + database connection other than pDb are significantly more expensive + than writes made to a file-based source database using pDb (as the + entire backup operation must be restarted in the former two cases). + + <li> If the backup process is restarted frequently enough it may never + run to completion and the backupDb() function may never return. + </ul> + +<p><b>backup_remaining() and backup_pagecount()</b> + +<p> + The backupDb() function uses the sqlite3_backup_remaining() and + sqlite3_backup_pagecount() functions to report its progress via the + user-supplied xProgress() callback. Function sqlite3_backup_remaining() + returns the number of pages left to copy and sqlite3_backup_pagecount() + returns the total number of pages in the source database (in this case + the database opened by pDb). So the percentage completion of the process + may be calculated as: + +<p style="font-family:fixed;margin-left:5em"> + Completion = 100% * (pagecount() - remaining()) / pagecount() + +<p> + The sqlite3_backup_remaining() and sqlite3_backup_pagecount() APIs report + values stored by the previous call to sqlite3_backup_step(), they do not + actually inspect the source database file. This means that if the source + database is written to by another thread or process after the call to + sqlite3_backup_step() returns but before the values returned by + sqlite3_backup_remaining() and sqlite3_backup_pagecount() are used, the + values may be technically incorrect. This is not usually a problem. + + +<div style="clear:both"></div> +<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/backup.in?m=6237da6d29">2022-04-18 02:55:50</a> UTC </small></i></p> + |