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