| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>SQLite Backup API</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
SQLite Backup API |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#using_the_sqlite_online_backup_api">1. Using the SQLite Online Backup API</a></div> |
| 126 |
<div class="fancy-toc2"><a href="#other_backup_techniques">1.1. Other Backup Techniques</a></div> |
| 127 |
<div class="fancy-toc1"><a href="#example_1_loading_and_saving_in_memory_databases">2. Example 1: Loading and Saving In-Memory Databases</a></div> |
| 128 |
<div class="fancy-toc2"><a href="#error_handling">2.1. Error handling</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#possible_enhancements">2.2. Possible Enhancements</a></div> |
| 130 |
<div class="fancy-toc1"><a href="#example_2_online_backup_of_a_running_database">3. Example 2: Online Backup of a Running Database</a></div> |
| 131 |
<div class="fancy-toc2"><a href="#file_and_database_connection_locking">3.1. File and Database Connection Locking</a></div> |
| 132 |
<div class="fancy-toc2"><a href="#backup_remaining_and_backup_pagecount_">3.2. backup_remaining() and backup_pagecount()</a></div> |
| 133 |
</div> |
| 134 |
</details> |
| 135 |
</div> |
| 136 |
|
| 137 |
|
| 138 |
|
| 139 |
|
| 140 |
|
| 141 |
|
| 142 |
<div class="fancy"> |
| 143 |
<h1 id="using_the_sqlite_online_backup_api"><span>1. </span>Using the SQLite Online Backup API</h1> |
| 144 |
|
| 145 |
<p> |
| 146 |
Historically, backups (copies) of SQLite databases have been created |
| 147 |
using the following method: |
| 148 |
|
| 149 |
</p><ol> |
| 150 |
<li> Establish a shared lock on the database file using the SQLite API (i.e. |
| 151 |
the shell tool). |
| 152 |
</li><li> Copy the database file using an external tool (for example the unix 'cp' |
| 153 |
utility or the DOS 'copy' command). |
| 154 |
</li><li> Relinquish the shared lock on the database file obtained in step 1. |
| 155 |
</li></ol> |
| 156 |
|
| 157 |
<p> |
| 158 |
This procedure works well in many scenarios and is usually very |
| 159 |
fast. However, this technique has the following shortcomings: |
| 160 |
|
| 161 |
</p><ul> |
| 162 |
<li> Any database clients wishing to write to the database file while a |
| 163 |
backup is being created must wait until the shared lock is |
| 164 |
relinquished. |
| 165 |
|
| 166 |
</li><li> It cannot be used to copy data to or from in-memory databases. |
| 167 |
|
| 168 |
</li><li> If a power failure or operating system failure occurs while copying |
| 169 |
the database file the backup database may be corrupted following |
| 170 |
system recovery. |
| 171 |
</li></ul> |
| 172 |
|
| 173 |
<p> |
| 174 |
The <a href="c3ref/backup_finish.html#sqlite3backupinit">Online Backup API</a> was created to |
| 175 |
address these concerns. The online backup API allows the contents of |
| 176 |
one database to be copied into another database file, replacing any |
| 177 |
original contents of the target database. The copy operation may be |
| 178 |
done incrementally, in which case the source database does not need |
| 179 |
to be locked for the duration of the copy, only for the brief periods |
| 180 |
of time when it is actually being read from. This allows other database |
| 181 |
users to continue without excessive delays while a backup of an online |
| 182 |
database is made. |
| 183 |
</p><p> |
| 184 |
The effect of completing the backup call sequence is to make the |
| 185 |
destination a bit-wise identical copy of the source database as it |
| 186 |
was when the copying commenced. (The destination becomes a "snapshot.") |
| 187 |
|
| 188 |
</p><p> |
| 189 |
The online backup API is <a href="c3ref/backup_finish.html#sqlite3backupinit">documented here</a>. |
| 190 |
The remainder of this page contains two C language examples illustrating |
| 191 |
common uses of the API and discussions thereof. Reading these examples |
| 192 |
is no substitute for reading the API documentation! |
| 193 |
|
| 194 |
</p><h2 id="other_backup_techniques"><span>1.1. </span>Other Backup Techniques</h2> |
| 195 |
|
| 196 |
<p>The Online Backup API is the original method for making backups of a |
| 197 |
live SQLite databases. Other more recent techniques for accomplishing |
| 198 |
the same thing include: |
| 199 |
|
| 200 |
</p><ul> |
| 201 |
<li><p> |
| 202 |
The <a href="lang_vacuum.html#vacuuminto">VACUUM INTO</a> command will make a vacuumed copy of a live |
| 203 |
SQLite database into a separate file. |
| 204 |
|
| 205 |
</p></li><li><p> |
| 206 |
The <a href="rsync.html">sqlite3_rsync</a> program makes a copy of a live SQLite database |
| 207 |
to or from a remote system using an SSH connection. |
| 208 |
</p></li></ul> |
| 209 |
|
| 210 |
<h1 id="example_1_loading_and_saving_in_memory_databases"><span>2. </span>Example 1: Loading and Saving In-Memory Databases</h1> |
| 211 |
|
| 212 |
<pre style='margin-left:10ex;'> |
| 213 |
<span style="color:blue;font-style:italic;">/*</span> |
| 214 |
<span style="color:blue;font-style:italic;">** This function is used to load the contents of a database file on disk </span> |
| 215 |
<span style="color:blue;font-style:italic;">** into the "main" database of open database connection pInMemory, or</span> |
| 216 |
<span style="color:blue;font-style:italic;">** to save the current contents of the database opened by pInMemory into</span> |
| 217 |
<span style="color:blue;font-style:italic;">** a database file on disk. pInMemory is probably an in-memory database, </span> |
| 218 |
<span style="color:blue;font-style:italic;">** but this function will also work fine if it is not.</span> |
| 219 |
<span style="color:blue;font-style:italic;">**</span> |
| 220 |
<span style="color:blue;font-style:italic;">** Parameter zFilename points to a nul-terminated string containing the</span> |
| 221 |
<span style="color:blue;font-style:italic;">** name of the database file on disk to load from or save to. If parameter</span> |
| 222 |
<span style="color:blue;font-style:italic;">** isSave is non-zero, then the contents of the file zFilename are </span> |
| 223 |
<span style="color:blue;font-style:italic;">** overwritten with the contents of the database opened by pInMemory. If</span> |
| 224 |
<span style="color:blue;font-style:italic;">** parameter isSave is zero, then the contents of the database opened by</span> |
| 225 |
<span style="color:blue;font-style:italic;">** pInMemory are replaced by data loaded from the file zFilename.</span> |
| 226 |
<span style="color:blue;font-style:italic;">**</span> |
| 227 |
<span style="color:blue;font-style:italic;">** If the operation is successful, SQLITE_OK is returned. Otherwise, if</span> |
| 228 |
<span style="color:blue;font-style:italic;">** an error occurs, an SQLite error code is returned.</span> |
| 229 |
<span style="color:blue;font-style:italic;">*/</span> |
| 230 |
int loadOrSaveDb(<a href="c3ref/sqlite3.html">sqlite3</a> *pInMemory, const char *zFilename, int isSave){ |
| 231 |
int rc; <span style="color:blue;font-style:italic;">/* Function return code */</span> |
| 232 |
<a href="c3ref/sqlite3.html">sqlite3</a> *pFile; <span style="color:blue;font-style:italic;">/* Database connection opened on zFilename */</span> |
| 233 |
<a href="c3ref/backup.html">sqlite3_backup</a> *pBackup; <span style="color:blue;font-style:italic;">/* Backup object used to copy data */</span> |
| 234 |
<a href="c3ref/sqlite3.html">sqlite3</a> *pTo; <span style="color:blue;font-style:italic;">/* Database to copy to (pFile or pInMemory) */</span> |
| 235 |
<a href="c3ref/sqlite3.html">sqlite3</a> *pFrom; <span style="color:blue;font-style:italic;">/* Database to copy from (pFile or pInMemory) */</span> |
| 236 |
|
| 237 |
<span style="color:blue;font-style:italic;">/* Open the database file identified by zFilename. Exit early if this fails</span> |
| 238 |
<span style="color:blue;font-style:italic;">** for any reason. */</span> |
| 239 |
rc = <a href="c3ref/open.html">sqlite3_open</a>(zFilename, &pFile); |
| 240 |
if( rc==SQLITE_OK ){ |
| 241 |
|
| 242 |
<span style="color:blue;font-style:italic;">/* If this is a 'load' operation (isSave==0), then data is copied</span> |
| 243 |
<span style="color:blue;font-style:italic;">** from the database file just opened to database pInMemory. </span> |
| 244 |
<span style="color:blue;font-style:italic;">** Otherwise, if this is a 'save' operation (isSave==1), then data</span> |
| 245 |
<span style="color:blue;font-style:italic;">** is copied from pInMemory to pFile. Set the variables pFrom and</span> |
| 246 |
<span style="color:blue;font-style:italic;">** pTo accordingly. */</span> |
| 247 |
pFrom = (isSave ? pInMemory : pFile); |
| 248 |
pTo = (isSave ? pFile : pInMemory); |
| 249 |
|
| 250 |
<span style="color:blue;font-style:italic;">/* Set up the backup procedure to copy from the "main" database of </span> |
| 251 |
<span style="color:blue;font-style:italic;">** connection pFile to the main database of connection pInMemory.</span> |
| 252 |
<span style="color:blue;font-style:italic;">** If something goes wrong, pBackup will be set to NULL and an error</span> |
| 253 |
<span style="color:blue;font-style:italic;">** code and message left in connection pTo.</span> |
| 254 |
<span style="color:blue;font-style:italic;">**</span> |
| 255 |
<span style="color:blue;font-style:italic;">** If the backup object is successfully created, call backup_step()</span> |
| 256 |
<span style="color:blue;font-style:italic;">** to copy data from pFile to pInMemory. Then call backup_finish()</span> |
| 257 |
<span style="color:blue;font-style:italic;">** to release resources associated with the pBackup object. If an</span> |
| 258 |
<span style="color:blue;font-style:italic;">** error occurred, then an error code and message will be left in</span> |
| 259 |
<span style="color:blue;font-style:italic;">** connection pTo. If no error occurred, then the error code belonging</span> |
| 260 |
<span style="color:blue;font-style:italic;">** to pTo is set to SQLITE_OK.</span> |
| 261 |
<span style="color:blue;font-style:italic;">*/</span> |
| 262 |
pBackup = <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init</a>(pTo, "main", pFrom, "main"); |
| 263 |
if( pBackup ){ |
| 264 |
(void)<a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step</a>(pBackup, -1); |
| 265 |
(void)<a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish</a>(pBackup); |
| 266 |
} |
| 267 |
rc = <a href="c3ref/errcode.html">sqlite3_errcode</a>(pTo); |
| 268 |
} |
| 269 |
|
| 270 |
<span style="color:blue;font-style:italic;">/* Close the database connection opened on database file zFilename</span> |
| 271 |
<span style="color:blue;font-style:italic;">** and return the result of this function. */</span> |
| 272 |
(void)<a href="c3ref/close.html">sqlite3_close</a>(pFile); |
| 273 |
return rc; |
| 274 |
} |
| 275 |
</pre> |
| 276 |
|
| 277 |
|
| 278 |
<p> |
| 279 |
The C function above demonstrates one of the simplest, |
| 280 |
and most common, uses of the backup API: loading and saving the contents |
| 281 |
of an in-memory database to a file on disk. The backup API is used as |
| 282 |
follows in this example: |
| 283 |
|
| 284 |
</p><ol> |
| 285 |
<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> |
| 286 |
object to copy data between the two databases (either from a file and |
| 287 |
into the in-memory database, or vice-versa). |
| 288 |
</li><li>Function <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> is called with a parameter of |
| 289 |
<tt>-1</tt> to copy the entire source database to the destination. |
| 290 |
</li><li>Function <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> is called to clean up resources |
| 291 |
allocated by <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a>. |
| 292 |
</li></ol> |
| 293 |
|
| 294 |
<h2 id="error_handling"><span>2.1. </span>Error handling</h2> |
| 295 |
|
| 296 |
<p> |
| 297 |
If an error occurs in any of the three main backup API routines |
| 298 |
then the <a href="rescode.html">error code</a> and <a href="c3ref/errcode.html">message</a> are attached to |
| 299 |
the destination <a href="c3ref/sqlite3.html">database connection</a>. |
| 300 |
Additionally, if |
| 301 |
<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 |
| 302 |
by both the <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> call itself, and by the subsequent call |
| 303 |
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> |
| 304 |
does not overwrite an <a href="rescode.html">error code</a> stored in the destination |
| 305 |
<a href="c3ref/sqlite3.html">database connection</a> by <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a>. This feature is used |
| 306 |
in the example code to reduce the amount of error handling required. |
| 307 |
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> |
| 308 |
calls are ignored and the error code indicating the success or failure of |
| 309 |
the copy operation is collected from the destination <a href="c3ref/sqlite3.html">database connection</a> |
| 310 |
afterward. |
| 311 |
|
| 312 |
</p><h2 id="possible_enhancements"><span>2.2. </span>Possible Enhancements</h2> |
| 313 |
|
| 314 |
<p> |
| 315 |
The implementation of this function could be enhanced in at least two ways: |
| 316 |
|
| 317 |
</p><ol> |
| 318 |
<li> Failing to obtain the lock on database file zFilename (an <a href="rescode.html#busy">SQLITE_BUSY</a> |
| 319 |
error) could be handled, and |
| 320 |
</li><li> Cases where the page-sizes of database pInMemory and zFilename are |
| 321 |
different could be handled better. |
| 322 |
</li></ol> |
| 323 |
|
| 324 |
<p> |
| 325 |
Since database zFilename is a file on disk, then it may be accessed |
| 326 |
externally by another process. This means that when the call to |
| 327 |
sqlite3_backup_step() attempts to read from or write data to it, it may |
| 328 |
fail to obtain the required file lock. If this happens, this implementation |
| 329 |
will fail, returning SQLITE_BUSY immediately. The solution would be to |
| 330 |
register a busy-handler callback or |
| 331 |
timeout with <a href="c3ref/sqlite3.html">database connection</a> pFile |
| 332 |
using <a href="c3ref/busy_handler.html">sqlite3_busy_handler()</a> or <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a> |
| 333 |
as soon as it is opened. If it fails to obtain a required lock immediately, |
| 334 |
<a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> uses any registered busy-handler callback or timeout |
| 335 |
in the same way as <a href="c3ref/step.html">sqlite3_step()</a> or <a href="c3ref/exec.html">sqlite3_exec()</a> does. |
| 336 |
|
| 337 |
</p><p> |
| 338 |
Usually, it does not matter if the page-sizes of the source database and the |
| 339 |
destination database are different before the contents of the destination |
| 340 |
are overwritten. The page-size of the destination database is simply changed |
| 341 |
as part of the backup operation. The exception is if the destination database |
| 342 |
happens to be an in-memory database. In this case, if the page sizes |
| 343 |
are not the same at the start of the backup operation, then the operation |
| 344 |
fails with an SQLITE_READONLY error. Unfortunately, this could occur when |
| 345 |
loading a database image from a file into an in-memory database using |
| 346 |
function loadOrSaveDb(). |
| 347 |
|
| 348 |
</p><p> |
| 349 |
However, if in-memory database pInMemory has just been opened (and is |
| 350 |
therefore completely empty) before being passed to function loadOrSaveDb(), |
| 351 |
then it is still possible to change its page size using an SQLite "PRAGMA |
| 352 |
page_size" command. Function loadOrSaveDb() could detect this case, and |
| 353 |
attempt to set the page-size of the in-memory database to the page-size |
| 354 |
of database zFilename before invoking the online backup API functions. |
| 355 |
|
| 356 |
</p><h1 id="example_2_online_backup_of_a_running_database"><span>3. </span>Example 2: Online Backup of a Running Database</h1> |
| 357 |
|
| 358 |
<pre style='margin-left:10ex;'> |
| 359 |
<span style="color:blue;font-style:italic;">/*</span> |
| 360 |
<span style="color:blue;font-style:italic;">** Perform an online backup of database pDb to the database file named</span> |
| 361 |
<span style="color:blue;font-style:italic;">** by zFilename. This function copies 5 database pages from pDb to</span> |
| 362 |
<span style="color:blue;font-style:italic;">** zFilename, then unlocks pDb and sleeps for 250 ms, then repeats the</span> |
| 363 |
<span style="color:blue;font-style:italic;">** process until the entire database is backed up.</span> |
| 364 |
<span style="color:blue;font-style:italic;">** </span> |
| 365 |
<span style="color:blue;font-style:italic;">** The third argument passed to this function must be a pointer to a progress</span> |
| 366 |
<span style="color:blue;font-style:italic;">** function. After each set of 5 pages is backed up, the progress function</span> |
| 367 |
<span style="color:blue;font-style:italic;">** is invoked with two integer parameters: the number of pages left to</span> |
| 368 |
<span style="color:blue;font-style:italic;">** copy, and the total number of pages in the source file. This information</span> |
| 369 |
<span style="color:blue;font-style:italic;">** may be used, for example, to update a GUI progress bar.</span> |
| 370 |
<span style="color:blue;font-style:italic;">**</span> |
| 371 |
<span style="color:blue;font-style:italic;">** While this function is running, another thread may use the database pDb, or</span> |
| 372 |
<span style="color:blue;font-style:italic;">** another process may access the underlying database file via a separate </span> |
| 373 |
<span style="color:blue;font-style:italic;">** connection.</span> |
| 374 |
<span style="color:blue;font-style:italic;">**</span> |
| 375 |
<span style="color:blue;font-style:italic;">** If the backup process is successfully completed, SQLITE_OK is returned.</span> |
| 376 |
<span style="color:blue;font-style:italic;">** Otherwise, if an error occurs, an SQLite error code is returned.</span> |
| 377 |
<span style="color:blue;font-style:italic;">*/</span> |
| 378 |
int backupDb( |
| 379 |
<a href="c3ref/sqlite3.html">sqlite3</a> *pDb, <span style="color:blue;font-style:italic;">/* Database to back up */</span> |
| 380 |
const char *zFilename, <span style="color:blue;font-style:italic;">/* Name of file to back up to */</span> |
| 381 |
void(*xProgress)(int, int) <span style="color:blue;font-style:italic;">/* Progress function to invoke */ </span> |
| 382 |
){ |
| 383 |
int rc; <span style="color:blue;font-style:italic;">/* Function return code */</span> |
| 384 |
<a href="c3ref/sqlite3.html">sqlite3</a> *pFile; <span style="color:blue;font-style:italic;">/* Database connection opened on zFilename */</span> |
| 385 |
<a href="c3ref/backup.html">sqlite3_backup</a> *pBackup; <span style="color:blue;font-style:italic;">/* Backup handle used to copy data */</span> |
| 386 |
|
| 387 |
<span style="color:blue;font-style:italic;">/* Open the database file identified by zFilename. */</span> |
| 388 |
rc = <a href="c3ref/open.html">sqlite3_open</a>(zFilename, &pFile); |
| 389 |
if( rc==SQLITE_OK ){ |
| 390 |
|
| 391 |
<span style="color:blue;font-style:italic;">/* Open the <a href="c3ref/backup.html">sqlite3_backup</a> object used to accomplish the transfer */</span> |
| 392 |
pBackup = <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init</a>(pFile, "main", pDb, "main"); |
| 393 |
if( pBackup ){ |
| 394 |
|
| 395 |
<span style="color:blue;font-style:italic;">/* Each iteration of this loop copies 5 database pages from database</span> |
| 396 |
<span style="color:blue;font-style:italic;">** pDb to the backup database. If the return value of backup_step()</span> |
| 397 |
<span style="color:blue;font-style:italic;">** indicates that there are still further pages to copy, sleep for</span> |
| 398 |
<span style="color:blue;font-style:italic;">** 250 ms before repeating. */</span> |
| 399 |
do { |
| 400 |
rc = <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step</a>(pBackup, 5); |
| 401 |
xProgress( |
| 402 |
<a href="c3ref/backup_finish.html#sqlite3backupremaining">sqlite3_backup_remaining</a>(pBackup), |
| 403 |
<a href="c3ref/backup_finish.html#sqlite3backuppagecount">sqlite3_backup_pagecount</a>(pBackup) |
| 404 |
); |
| 405 |
if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){ |
| 406 |
<a href="c3ref/sleep.html">sqlite3_sleep</a>(250); |
| 407 |
} |
| 408 |
} while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ); |
| 409 |
|
| 410 |
<span style="color:blue;font-style:italic;">/* Release resources allocated by backup_init(). */</span> |
| 411 |
(void)<a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish</a>(pBackup); |
| 412 |
} |
| 413 |
rc = <a href="c3ref/errcode.html">sqlite3_errcode</a>(pFile); |
| 414 |
} |
| 415 |
|
| 416 |
<span style="color:blue;font-style:italic;">/* Close the database connection opened on database file zFilename</span> |
| 417 |
<span style="color:blue;font-style:italic;">** and return the result of this function. */</span> |
| 418 |
(void)<a href="c3ref/close.html">sqlite3_close</a>(pFile); |
| 419 |
return rc; |
| 420 |
} |
| 421 |
</pre> |
| 422 |
|
| 423 |
|
| 424 |
<p> |
| 425 |
The function presented in the previous example copies the entire source |
| 426 |
database in one call to <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a>. This requires holding a |
| 427 |
read-lock on the source database file for the duration of the operation, |
| 428 |
preventing any other database user from writing to the database. It also |
| 429 |
holds the mutex associated with database pInMemory throughout the copy, |
| 430 |
preventing any other thread from using it. The C function in this section, |
| 431 |
designed to be called by a background thread or process for creating a |
| 432 |
backup of an online database, avoids these problems using the following |
| 433 |
approach: |
| 434 |
|
| 435 |
</p><ol> |
| 436 |
<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> |
| 437 |
object to copy data from database pDb to the backup database file |
| 438 |
identified by zFilename. |
| 439 |
</li><li>Function <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> is called with a parameter of 5 to |
| 440 |
copy 5 pages of database pDb to the backup database (file zFilename). |
| 441 |
</li><li>If there are still more pages to copy from database pDb, then the |
| 442 |
function sleeps for 250 milliseconds (using the <a href="c3ref/sleep.html">sqlite3_sleep()</a> |
| 443 |
utility) and then returns to step 2. |
| 444 |
</li><li>Function <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> is called to clean up resources |
| 445 |
allocated by <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a>. |
| 446 |
</li></ol> |
| 447 |
|
| 448 |
<h2 id="file_and_database_connection_locking"><span>3.1. </span>File and Database Connection Locking</h2> |
| 449 |
|
| 450 |
<p> |
| 451 |
During the 250 ms sleep in step 3 above, no read-lock is held on the database |
| 452 |
file and the mutex associated with pDb is not held. This allows other threads |
| 453 |
to use <a href="c3ref/sqlite3.html">database connection</a> pDb and other connections to write to the |
| 454 |
underlying database file. |
| 455 |
|
| 456 |
</p><p> |
| 457 |
If another thread or process writes to the source database while this |
| 458 |
function is sleeping, then SQLite detects this and usually restarts the |
| 459 |
backup process when sqlite3_backup_step() is next called. There is one |
| 460 |
exception to this rule: If the source database is not an in-memory database, |
| 461 |
and the write is performed from within the same process as the backup |
| 462 |
operation and uses the same database handle (pDb), then the destination |
| 463 |
database (the one opened using connection pFile) is automatically updated |
| 464 |
along with the source. The backup process may then be continued after the |
| 465 |
sqlite3_sleep() call returns as if nothing had happened. |
| 466 |
|
| 467 |
</p><p> |
| 468 |
Whether or not the backup process is restarted as a result of writes to |
| 469 |
the source database mid-backup, the user can be sure that when the backup |
| 470 |
operation is completed the backup database contains a consistent and |
| 471 |
up-to-date snapshot of the original. However: |
| 472 |
|
| 473 |
</p><ul> |
| 474 |
<li> Writes to an in-memory source database, or writes to a file-based |
| 475 |
source database by an external process or thread using a |
| 476 |
database connection other than pDb are significantly more expensive |
| 477 |
than writes made to a file-based source database using pDb (as the |
| 478 |
entire backup operation must be restarted in the former two cases). |
| 479 |
|
| 480 |
</li><li> If the backup process is restarted frequently enough it may never |
| 481 |
run to completion and the backupDb() function may never return. |
| 482 |
</li></ul> |
| 483 |
|
| 484 |
<h2 id="backup_remaining_and_backup_pagecount_"><span>3.2. </span>backup_remaining() and backup_pagecount()</h2> |
| 485 |
|
| 486 |
<p> |
| 487 |
The backupDb() function uses the sqlite3_backup_remaining() and |
| 488 |
sqlite3_backup_pagecount() functions to report its progress via the |
| 489 |
user-supplied xProgress() callback. Function sqlite3_backup_remaining() |
| 490 |
returns the number of pages left to copy and sqlite3_backup_pagecount() |
| 491 |
returns the total number of pages in the source database (in this case |
| 492 |
the database opened by pDb). So the percentage completion of the process |
| 493 |
may be calculated as: |
| 494 |
|
| 495 |
</p><p style="font-family:fixed;margin-left:5em"> |
| 496 |
Completion = 100% * (pagecount() - remaining()) / pagecount() |
| 497 |
|
| 498 |
</p><p> |
| 499 |
The sqlite3_backup_remaining() and sqlite3_backup_pagecount() APIs report |
| 500 |
values stored by the previous call to sqlite3_backup_step(), they do not |
| 501 |
actually inspect the source database file. This means that if the source |
| 502 |
database is written to by another thread or process after the call to |
| 503 |
sqlite3_backup_step() returns but before the values returned by |
| 504 |
sqlite3_backup_remaining() and sqlite3_backup_pagecount() are used, the |
| 505 |
values may be technically incorrect. This is not usually a problem. |
| 506 |
|
| 507 |
|
| 508 |
</p><div style="clear:both"></div> |
| 509 |
</div><p align="center"><small><i>This page was last updated on 2025-11-13 07:12:58Z </small></i></p> |
| 510 |
|