Debian Codegraph @

www/recovery.html

raw
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>Recovering Data From A Corrupt SQLite Database</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 Recovering Data From A Corrupt SQLite Database
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#recovering_some_data_from_a_corrupt_sqlite_database">1. Recovering (Some) Data From A Corrupt SQLite Database</a></div>
126 <div class="fancy-toc2"><a href="#limitations">1.1. Limitations</a></div>
127 <div class="fancy-toc1"><a href="#recovery_using_the_recover_command_in_the_cli">2. Recovery Using The ".recover" Command In The CLI</a></div>
128 <div class="fancy-toc1"><a href="#building_the_recovery_api_into_an_application">3. Building The Recovery API Into An Application</a></div>
129 <div class="fancy-toc2"><a href="#source_code_files">3.1. Source Code Files</a></div>
130 <div class="fancy-toc2"><a href="#how_to_implement_recovery">3.2. How To Implement Recovery</a></div>
131 <div class="fancy-toc2"><a href="#example_implementations">3.3. Example Implementations</a></div>
132 </div>
133 </details>
134 </div>
135
136
137
138
139 <h1 id="recovering_some_data_from_a_corrupt_sqlite_database"><span>1. </span>Recovering (Some) Data From A Corrupt SQLite Database</h1>
140
141 <p>
142 SQLite databases are remarkably robust. Application faults and
143 power failures typically leave the content of the database intact.
144 However, it is possible to <a href="howtocorrupt.html">corrupt an SQLite database</a>.
145 For example, hardware malfunctions can damage the database file, or a
146 rogue process can open the database and overwrite parts of it.
147
148 </p><p>
149 Given a corrupt database file, it is sometimes desirable to try to
150 salvage as much data from the file as possible. The recovery API
151 is designed to facilitate this.
152
153 </p><h2 id="limitations"><span>1.1. </span>Limitations</h2>
154
155 <p>
156 It is sometimes possible to perfectly restore a database that has
157 gone corrupt, but that is the exception. Usually
158 the recovered database will be defective in a number of ways:
159
160 </p><ul>
161 <li><p>
162 Some content might be permanently deleted and unrecoverable.
163 This can happen, for example, if a rogue process overwrites part
164 of the database file.
165
166 </p></li><li><p>
167 Previously deleted content might reappear. Normally when SQLite
168 does a DELETE operation, it does not actually overwrite the old content
169 but instead remembers that space is available for reuse during the next
170 INSERT. If such deleted content is still in the file when a recovery
171 is attempted, it might be extracted and "resurrected".
172
173 </p></li><li><p>
174 Recovered content might be altered.
175 For example, the value stored in a particular row
176 might change from 48 to 49. Or it might change from an integer into
177 a string or blob. A value that was NULL might become an integer.
178 A string value might become a BLOB. And so forth.
179
180 </p></li><li><p>
181 Constraints may not be valid after recovery. CHECK constraints,
182 FOREIGN KEY constraints, UNIQUE constraints, type constraints on
183 <a href="stricttables.html">STRICT tables</a> - any of these might be violated in the recovered
184 database.
185
186 </p></li><li><p>
187 Content might be moved from one table into another.
188 </p></li></ul>
189
190 <p>
191 The recovery API does as good of a job as it can at restoring a database,
192 but the results will always be suspect. Sometimes (for example if the
193 corruption is restricted to indexes) the recovery will perfectly restore
194 the database content. However in other cases, the recovery will be imperfect.
195 The impact of this imperfection depends on the application. A database that
196 holds a list of bookmarks is still a list of bookmarks after recovery.
197 A few bookmarks might be missing or added or altered after recovery, but
198 the list is "fuzzy" and imperfect to begin with so adding a bit more
199 uncertainty will not be fatal to the application. But if an accounting
200 database goes corrupt and is subsequently recovered, the books might be
201 out of balance.
202
203 </p><p>
204 It is best to think of the recovery API as a salvage undertaking.
205 Recovery will extract as much usable data as it can from the wreck
206 of the old database, but some parts may be damaged beyond repair and
207 some rework and testing should be performed prior to returning the
208 recovered database to service.
209
210 </p><h1 id="recovery_using_the_recover_command_in_the_cli"><span>2. </span>Recovery Using The ".recover" Command In The CLI</h1>
211
212 <p>
213 The easiest way to manually recover a corrupt database is using
214 the <a href="cli.html">Command Line Interface</a> or "CLI" for SQLite. The CLI is a program
215 named "sqlite3". Use it to recover a corrupt database file using
216 a command similar to the following:
217
218 </p><div class="codeblock"><pre>sqlite3 corrupt.db .recover &gt;data.sql
219 </pre></div>
220
221 <p>
222 This will generate SQL text in the file named "data.sql" that can be used
223 to reconstruct the original database:
224
225 </p><div class="codeblock"><pre>sqlite3 recovered.db &lt;data.sql
226 </pre></div>
227
228 <p>
229 The ".recover" option is actually a command that is issued to the
230 CLI. That command can accept arguments. For example, by running:
231
232 </p><div class="codeblock"><pre>sqlite3 corruptdb ".recover --ignore-freelist" &gt;data.sql
233 </pre></div>
234
235 <p>
236 Notice that the ".recover" command and its arguments must be contained
237 in quotes. The following options are supported:
238
239 </p><p>
240 </p><blockquote>
241 <dt>--ignore-freelist</dt>
242 <dd><p>
243 Ignore pages of the database that appear to be part of the
244 freelist. Normally the freelist is scanned, and if it contains
245 pages that look like they have content, that content is output.
246 But if the page really is on the freelist, that can mean that
247 previously deleted information is reintroduced into the database.
248 </p></dd>
249
250 <dt>--lost-and-found <i>TABLE</i></dt>
251 <dd><p>
252 If content is found during recovery that cannot be associated
253 with a particular table, it is put into the "lost_and_found"
254 table. Use this option to change the name of the
255 "lost_and_found" table to "TABLE".
256 </p></dd>
257
258 <dt>--no-rowids</dt>
259 <dd><p>
260 If this option is provided, then rowid values that are not also
261 INTEGER PRIMARY KEY values are not extracted from the
262 corrupt database.
263 </p></dd>
264
265 </blockquote>
266
267 <h1 id="building_the_recovery_api_into_an_application"><span>3. </span>Building The Recovery API Into An Application</h1>
268
269 <h2 id="source_code_files"><span>3.1. </span>Source Code Files</h2>
270
271 <p>If you want to build the recovery API into your application, you will
272 need to add some source files to your build, above and beyond the usual
273 "sqlite3.c" and "sqlite3.h" source files. You will need:
274
275 </p><p>
276 </p><blockquote>
277 <table border="0" cellpadding="20">
278 <tr>
279 <td>
280 <a href="https://sqlite.org/src/file/ext/recover/sqlite3recover.c">sqlite3recover.c</a>
281 </td><td>
282 This is the main source file that implements the recovery API.
283 </td>
284 </tr>
285
286 <tr>
287 <td>
288 <a href="https://sqlite.org/src/file/ext/recover/sqlite3recover.h">sqlite3recover.h</a>
289 </td><td>
290 This is the header file that goes with sqlite3recover.h.
291 </td>
292 </tr>
293
294 <tr>
295 <td>
296 <a href="https://sqlite.org/src/file/ext/recover/dbdata.c">dbdata.c</a>
297 </td><td>
298 This file implements two virtual tables name "sqlite_dbdata" and
299 "sqlite_dbptr" that are required by sqlite3recover.c.
300 </td>
301 </tr>
302 </table>
303 </blockquote>
304
305 <p>
306 The two C source files above need to be linked into your application in the
307 same way as "sqlite3.c" is linked in. And the header file needs to be
308 accessible to the compiler when the C files are being compiled.
309
310 </p><p>
311 Additionally, the application, or more specifically the sqlite3.c linked
312 into the application, must be compiled with the following option:
313 </p><pre>
314 -DSQLITE_ENABLE_DBPAGE_VTAB
315 </pre>
316
317 <h2 id="how_to_implement_recovery"><span>3.2. </span>How To Implement Recovery</h2>
318
319 <p>These are the basic steps needed to recover content from a corrupt
320 database:
321
322 </p><ol>
323 <li><p>
324 Creates an sqlite3_recover handle by calling either
325 sqlite3_recover_init() or sqlite3_recover_init_sql().
326 Use sqlite3_recover_init() to store the recovered content
327 in a separate database and use sqlite3_recover_init_sql()
328 to generate SQL text that will reconstruct the database.
329
330 </p></li><li><p>
331 Make zero or more calls to sqlite3_recover_config() to set
332 options on the new sqlite3_recovery handle.
333
334 </p></li><li><p>
335 Invoke sqlite3_recover_step() repeatedly
336 until it returns something other than SQLITE_OK. If it
337 returns SQLITE_DONE, then the recovery operation completed without
338 error. If it returns some other non-SQLITE_OK value, then an error
339 has occurred. The sqlite3_recover_run() interface is also
340 available as a convenience wrapper that simply invokes
341 sqlite3_recover_step() repeatedly until it returns something other
342 than SQLITE_DONE.
343
344 </p></li><li><p>
345 Retrieves any error code and English language error message using the
346 sqlite3_recover_errcode() and sqlite3_recover_errmsg() interfaces,
347 respectively.
348
349 </p></li><li><p>
350 Invoke sqlite3_recover_finish() to destroy the sqlite3_recover object.
351 </p></li></ol>
352
353 <p>
354 Details of the interface are described in comments in the
355 <a href="https://sqlite.org/src/file/ext/recover/sqlite3recover.h">sqlite3_recover.h header file</a>.
356
357 </p><h2 id="example_implementations"><span>3.3. </span>Example Implementations</h2>
358
359
360 <p>
361 Examples of how the recovery extension is used by SQLite itself
362 can be seen at the following links:
363
364 </p><ul>
365 <li><p><a href="https://sqlite.org/src/info/30475c820dc5ab8a8?ln=999,1026">https://sqlite.org/src/info/30475c820dc5ab8a8?ln=999,1026</a>
366 </p><p>
367 An example of the recovery extension found in the
368 "fuzzcheck" testing utility in the SQLite tree.
369
370 </p></li><li><p><a href="https://sqlite.org/src/info/84bb08d8762920285f08f1c0?ln=7299,7361">https://sqlite.org/src/info/84bb08d8762920285f08f1c0?ln=7299,7361</a>
371 </p><p>
372 The code that implements the ".recover" command in the <a href="cli.html">CLI</a>.
373 </p></li></ul>
374 <p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
375