Backing up .sqlite (Core Data)

Question

I have a core data based application that uses Dropbox to backup and restore data. The way I backup is pretty straight forward. I copy the .sqlite file on the user's dropbox.

Now my backup and restore functionality are working fine. The issue is with the .sqlite file itself. It appears that the .sqlite file is incomplete.

I entered about 125 entries in my application and took a backup. The backup appeared in my dropbox but when I use a .sqlite explorer tool to see the contents, I only see records upto 117th entry.

I tried updating the first entry and then again observing .sqlite file but no changes again.

What's even more strange is that the app appears to have recorded all changes. When I add a new entry or update an existing one and restart the app, the newly added data seems to persist. But this newly added data does not appear in my .sqlite file.

I am backing up using this code:

AppDelegate *appDelegate = [[UIApplication sharedApplication] delegate];
NSString *filePath = [[[appDelegate applicationDocumentsDirectory] path] stringByAppendingPathComponent:@"MyApp.sqlite"];


if (account) {
    if ([filesystem isShutDown]) {
        filesystem = [[DBFilesystem alloc] initWithAccount:account];
        [DBFilesystem setSharedFilesystem:filesystem];
    }

    DBPath *newPath = [[DBPath root] childPath:[NSString stringWithFormat:@"Backup - %@.sqlite", [NSDate date]]];
    DBFile *file = [[DBFilesystem sharedFilesystem] createFile:newPath error:nil];
    [file writeContentsOfFile:filePath shouldSteal:NO error:nil];
    [filesystem shutDown];

}

I also copied the .sqlite file from the Simulator's folder and tried seeing it in the .sqlite browser. It still exhibits the same behaviour. Any reason why this must be happening?

Answer 1

Starting with iOS 7 / OS X 10.9, Core Data uses "Write-Ahead Logging" (WAL) as default journaling mode for the underlying SQLite store file. This is explained in

Technical Q&A QA1809: New default journaling mode for Core Data SQLite stores in iOS 7 and OS X Mavericks

With the WAL mode, Core Data keeps the main store file untouched and appends transactions to a -wal file in the same location. After the Core Data context is saved, the -wal file is not deleted, and the data in that file is not merged to the store file either. Therefore, simply making copies of the store file will likely cause data loss and inconsistency.

That should explain why your .sqlite file alone is incomplete. As as solution you can (also explained in that Technical Note):

• Disable WAL-mode (and use the "old" rollback journaling mode) for the SQLite store by setting the

  @{NSSQLitePragmasOption:@{@"journal_mode":@"DELETE"}};
  

  option when adding the persistent store, or

• Use the

  - (NSPersistentStore *)migratePersistentStore:(NSPersistentStore *)store toURL:(NSURL *)URL options:(NSDictionary *)options withType:(NSString *)storeType error:(NSError **)error
  

  method to make a backup copy of the Core Data store.