How to find which message failed?

[David Carlin]

I've successfully restored a backup of my email via GYB to another account, however a handful of messages failed due to attachment issues, like these examples below.

What can I do to find the subject or sender of these messages so I can review if I want to keep them via some other means?

Also an FYI... I was concerned "All Mail" on my new personal account showed 70,000 fewer messages than my school account I am transferring from - the issue was that conversation mode was ON with the new account and OFF with my school account.  Turning conversation mode OFF on the new account so they were setup the same way resolved this concern - both accounts under "All Mail" show similar number of messages now.

Thanks for this fantastic tool!  I was able to restore ~173K messages taking up 10GB of space in 2 days versus the 5 weeks I've spent using google's own transfer tool and never having it successfully complete. 

restoring 15 messages (168915/173213)
ERROR: 400: Invalid attachment. Please check https://support.google.com/mail/answer/6590.. Skipping message restore.
restoring 15 messages (170491/173213)
ERROR: 400: Invalid attachment. Please check https://support.google.com/mail/answer/6590.. Skipping message restore.
restoring 15 messages (171091/173213)
ERROR: 400: Invalid attachment. Please check https://support.google.com/mail/answer/6590.. Skipping message restore.
restoring 15 messages (171886/173213)

[David Carlin]

I figured out one solution, providing this information in case its helpful to others:

1) Download/install DB Browser for SQLite: https://sqlitebrowser.org/

2) Go to File Menu and pick "Open Database Read Only..." and open the "msg-db.sqlite" file located inside your GYB backup folder - make sure you pick the READ ONLY option unless you know what you are doing!

3) Click on "Browse Data" tab.

4) Select the "messages" table from the pull down menu.

5) Under the "message_num" column you can filter by message number - put in the message number from the GYB error.  Example, message 168915 (one of the errors in my first post).

6) Copy the message_filename column - for example "2004\1\7\f9f0bfc6b29e977.eml"

7) Go to command prompt, change directory to folder where your GYB backup is contained and execute "more 2004\1\7\f9f0bfc6b29e977.eml" - and you can see if this email is important to you or not!

David

[David Carlin]

I made an assumption in the example in previous email below reading the GYB output that messages 168915, 170491, 171091, 171886 failed when in fact this is not the case.  I assumed the ERROR messages corresponded to the message number above the error.  It ended up that the missing messages were "around" those numbers.  What I did was open the <new email address>-restored.sqlite database which contains a single table with a list of message numbers that were successfully restored.  Any missing numbers in the sequence are emails that failed restoration.

The fastest way for me to compile a list of missing message numbers was a bash one-liner to make a file with numbers 1 - 173213 on each line.  I then exported the sqlite table to another file and diff'd them - the resulting output from diff was only the missing emails numbers.

I then grabbed a copy of sqlite command line tools from https://www.sqlite.org/download.html and ran the following query:

sqlite> SELECT message_filename FROM messages WHERE message_num in (77280, 113401, 113710, 114164, 115363, 115680, 118514, 121333, 121541, 121544, 121573, 122009, 122014, 126845, 126865, 128791, 132413, 136047, 141613, 141619, 141622, 147235, 149622, 149839, 149906, 151122, 151235, 151532, 151540, 152408, 152526, 155093, 155606, 158202, 160652, 161169, 164212, 164213, 165214, 167949, 168107, 168906, 170482, 171087, 171890);

this gave me a list of file locations so I could open the emails up that failed to restore in notepad and see if any of them were worth preserving.  Almost all were emails with MSI attachments over 10 years ago - seems google doesn't like MSI attachments currently.  A few were emails with multiple "From:" lines in the headers, and I was able to verify this by opening the emails once I found the correct failed message numbers.

One final thing I'd like to note.. One restore failed because of "WARNING! file GYB-GMail-Backup-<old email>\2008\7\30\11b75e581f29eb2b.eml does not exist for message 136047 this message will be skipped." - it turns out Windows quarantined this file after it was backed up because it had a virus in it! 

After examining each ERROR and WARNING instance of the GYB output when restoring my email, each of these email restores failed for a good reason.

Thanks for writing this tool!  I am confident now my emails are all migrated (as best as possible) from my old school account to my new personal account.

David

On Wednesday, May 10, 2023 at 8:20:14 AM UTC-4 David Carlin wrote:

[Mike Lazarski]

Thank you for this info! I've been trying to figure out what to do with 401: Authorization fails in a restore to a group. 

My restored.sqlite table does not have any blanks for message_num. Although I did encounter a handful of 401 errors. 

Was your message_num different from the index number at the end of the restored.sqlite table?

I spot-checked one of the messages that 401 authError'd in the restored group and it was there so I'm a bit confused if I they were in fact restored or not. 

Thanks again for this write-up! 

[David Carlin]

My <new email>-restored.sqlite database contains a single table, "restored_messages".  It has a single column, "message_num".  I don't have any "blank" rows where skipped messages occurred, instead I just have fewer rows than my backup's sqlite database, and the message_num column is missing numbers from its sequence..  They can be identified by gaps in the numerical sequence of "message_num" column.  The final number in the message_num columns of both my restored sqlite database and backup database match since it was able to restore the final message.  What doesn't match is the number of rows, since some messages weren't able to be restored. 

See example screenshot below - rows 168864 and 168865 of the database jump from message_num 168905 to 168907 - so I know message_num 168906 was skipped.

In my restore, messages were only missing for three reasons - 1) virus scanner quarantined one of the messages from my backup 2) Some backed up messages had multiple From: headers and Gmail rejected restoring them 3) Some backed up messages had an attachment type Gmail doesn't like, so Gmail rejected the restoration.  All of those skipped messages appeared as gaps in the "message_num" sequence, so I could find those gaps, look up the messages in the backup and determine I didn't care about those messages anyways.

Your error is something else that I never encountered - "401: Authorization".  Perhaps this was a transient problem and GYB retried and was successful in restoring the emails?  I don't know.  

David

--
--
You received this message because you are subscribed to the Google
Groups "Got Your Back: Gmail Backup" group.
To post to this group, send email to got-yo...@googlegroups.com
To unsubscribe from this group, send email to
got-your-bac...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/got-your-back?hl=en?hl=en

---
You received this message because you are subscribed to a topic in the Google Groups "Got Your Back: Gmail Backup" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/got-your-back/Z96ILiOhdk0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to got-your-bac...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/got-your-back/c48c3563-264a-457c-abf5-eaf821d7c19an%40googlegroups.com.

[David Carlin]

I didn't see your attachments initially - definately seems like GYB skipped those messages because it says "Giving Up".  I would expect that you would see missing numbers in the message_num column of the restore sqlite database.

David

On Thursday, May 25, 2023 at 7:35:06 AM UTC-4 David Carlin wrote:

]Your error is something else that I never encountered - "401: Authorization".  Perhaps this was a transient problem and GYB retried and was successful in restoring the emails?  I don't know.  

[Mike Lazarski]

Thank you for the additional clarity and screenshots. 

I feel fairly confident GYB has not completely 'Given up' since the restored message sequence does line up.