Database Info

HOWTO: Backup SMS Database

Playing around with the ls -R command, I found where the SMS database is kept, and it's somewhat readable in a text editor...
UPDATE (thanks staulkor): This database is viewable, and searchable using an SQLite database viewer.
Code:
/data/data/com.android.providers/telephony/databases/mmssms.db
Just run the following command to back it up to the SD Card:
Code:
busybox cp /data/data/com.android.providers.telephony/databases/mmssms.db /sdcard
Happy hacking

You can use an SQLite viewer to read the database correctly

Would seem simple enough to write a script to run on a PC/Mac to back up the database and load it into some sort of viewer... searchable perhaps?
I'm not too familar with databases, but would seem simple enough to make a lightweight app to keep the SMS archive...

Cool, maybe we'll soon see a port of PIM Backup. Would be nice...

Hmm...
/data/data/com.android.providers/telephony/databases/mmssms.db
Click to expand...
Click to collapse
and
busybox cp /data/data/com.android.providers.telephony/databases/mmssms.db /sdcard
Click to expand...
Click to collapse
are two different paths - I think both are supposed to be
Code:
/data/data/com.android.providers.telephony/databases/
(since I can't cd to the other one).
At any rate - thanks for the tip, but can anyone else confirm this works for them? On my android (R30) I get "opendir failed, Permission denied" if I try to read the contents of that directory.

Seeing as it's fully possible to write an application to back up the database to the sdcard (or even to a website), wouldn't that make more sense?
Afterall, there's no need to have the actual database -- and the method is highly restrictive to both people with root on their phones and those with a USB cable and computer handy.
Also, backing up this database will not backup any MMS attachments, which are stored here:
/data/data/com.android.providers.telephony/app_parts/
Refer to the "part" table for an index matching the files there to messages/filenames. (For each MMS, there should be an XML file that helps identify the type of message, and then the file itself, such as a jpeg).

is this the best and safest way to back up sms database as of yet? I have about 1000 sms and it slows down my messaging so I've been try to find a way to free that up. Backing them up would be great so i can clear them.
If anyone has tried this, please share with the rest of us on its effect on the phone.
Thank you,
Hbguy
P.S: This site is truely GREAT because pros are patient with noobs

i don't understand this...i downloaded the file managers on marketplace, and cannot when i click on data file, there is nothing in there..so i dowonloaded a terminal program and still don't understand how to use the commands to move sms from memory to sim...can u do a quick step-by-step? do i have to use any specific programs to do this? sorry, so used to the simplicity of WM6 on my old phone, having trouble adapting to this platform..thanks for any help.

jazzmanmonty said:
i don't understand this...i downloaded the file managers on marketplace, and cannot when i click on data file, there is nothing in there..so i dowonloaded a terminal program and still don't understand how to use the commands to move sms from memory to sim...can u do a quick step-by-step? do i have to use any specific programs to do this? sorry, so used to the simplicity of WM6 on my old phone, having trouble adapting to this platform..thanks for any help.
Click to expand...
Click to collapse
You need to have root on your phone to follow the instructions given above (if you don't know what that means, you don't have it). For non-root users, most of the Android system on the G1 is locked down -- including /data and all its subfolders.
If you do have root on the phone, you can follow the instructions at the top from a terminal while logged in as root. You can verify you are root by running the program "id":
# id
uid=0(root) gid=0(root)
From there, to copy the SMS/MMS database to your sdcard, you can follow the above instructions. If you don't have busybox (ie, "busybox: not found"), you can use the "cat" command as so:
cat /data/data/com.android.providers.telephony/databases/mmssms.db > /sdcard/mmssms.db
You may also want any files stored here:
/data/data/com.android.providers.telephony/app_parts/
Which are from MMSs (if you've no MMSs saved, then you've no need to bother).
Also, not sure if there is a clarification needed for you. The instructions provided offer no way to access SMSs stored on the SIM, as Android uses a PDU interface that doesn't use the SIM's SMS storage space. It is possible to read SMSs off the SIM, however, and into the internal memory storage. See the settings window in the messaging application.
If someone does take the time to create an application to do this, then it would not require root access. Applications are allowed to access the SMS/MMS database, provided the user grants them permission, and could then be used to read the data from the database and into a new database or other file (eg, csv).

do ya think it would be posible to move these permanently to the memory card? ummmm.... ill try if i get a chance ..... ya can connect to phone via adb in recovery mode right ? an undo whatwver i break?

how would you view your backed up sms messages using staulkors sql database?
step by step would be great
hbguy

Just to be clear - for most people on RC30, this probably will not work.
Per http://forum.xda-developers.com/showthread.php?t=442480 the hacks to gain root access have been blocked since RC30 was deployed in early November. Someone should write an application to support this, but instead everybody is busy creating new useless calculators and niche geolocation applications.
If I'm wrong about the above, it would be terrific for anyone to chime in and clarify that.

goodwinm said:
Cool, maybe we'll soon see a port of PIM Backup. Would be nice...
Click to expand...
Click to collapse
https://github.com/jberkel/sms-backup-plus

Where is MMS saved?

I'm sure someone has figured this out.
Where in the file system are the pictures I receive via mms saved?
I'm sure they have to be somewhere, there has to be a way to get them without the fwd to pc email / attach image to new email / email back to phone trick.
Bonus points, I deleted a thread - can I still find those images somewhere in the file system and restore them?

I believe they're stored in a sqlite3 database, so it's not as easy as just copying a file.
It looks like this is the database:
/data/data/com.android.providers.telephony/databases/mmssms.db
I don't have any mms/sms messages atm (recently wiped phone), so I don't have any data to look at in the database.
If you want, give me an IM or PM and I'll give you my cell number so you can send me an sms with a pic, so I can figure out how to extract it from the database

i dont think its in the database: its the same size no matter how many messages there is

Found it. The text and attachments are saved in /data/data/com.android.providers.telephony/app_parts
They are stored in files named something like "PART_1228000671287", with no extension.
To see what type of files they are, you can run this command on the phone (assuming you have RC30 v1.2).
sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "SELECT _data, ct FROM part;"
The first column is the full path to the file, and the 2nd column is the mime type of the file

Awesome, good work Android Master JesusFreke.
too bad I have the factory RC30
(why oh why did I hit the update button that night?)*
but that's cool to know that they can be found.
Now we just need someone to apply that knowledge into making a SaveImage option appear in the MMS thread.
* on an unrelated note I need to put in another thread: when RC29 came I noticed the camera worked like 5 times better. I was even able to snap pics of the kids on the swings and they came out clear, when RC30 came it was back to blurriness as usual.

JesusFreke said:
Found it. The text and attachments are saved in /data/data/com.android.providers.telephony/app_parts
They are stored in files named something like "PART_1228000671287", with no extension.
To see what type of files they are, you can run this command on the phone (assuming you have RC30 v1.2).
sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "SELECT _data, ct FROM part;"
The first column is the full path to the file, and the 2nd column is the mime type of the file
Click to expand...
Click to collapse
Would it be possible to write a program that would automatically extract the imgs/vids from that path and put them on the sd card with the correct file extension?

Sure. It would certainly be possible for rooted phones. I'm not sure for non-rooted phones.

I should have added on rooted phones on my first post.
Guess i should get off my butt and start messing around with some code.
I'm kinda leery about messing with root comands.

I think it would be possible for rooted as well. Can't applications have message privys so they can read messages? if they can read them can't they save the data?

JesusFreke said:
Found it. The text and attachments are saved in /data/data/com.android.providers.telephony/app_parts
They are stored in files named something like "PART_1228000671287", with no extension.
To see what type of files they are, you can run this command on the phone (assuming you have RC30 v1.2).
sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "SELECT _data, ct FROM part;"
The first column is the full path to the file, and the 2nd column is the mime type of the file
Click to expand...
Click to collapse
Now I can save my pictures people texted me! Thanks dude!

Backup Sprint Visual Voicemail?

Is there any way to backup the visual voicemail data in its entirety? I've tried looking for the data files using ASTRO but can't find anything other than the core app.
I also came up dry searching this forum.
Thanks

This may or may not be of use to you, but the best way IMO to do this would be just to use Google Voice for your voicemail needs. All your voicemails are stored on Google's servers, so you won't have to worry about it. But aside from Google Voice, I am pretty sure that as long as you don't delete the voicemails, they will remain there after a ROM flash or whatever you intend on doing.

mrinehart93 said:
This may or may not be of use to you, but the best way IMO to do this would be just to use Google Voice for your voicemail needs. All your voicemails are stored on Google's servers, so you won't have to worry about it. But aside from Google Voice, I am pretty sure that as long as you don't delete the voicemails, they will remain there after a ROM flash or whatever you intend on doing.
Click to expand...
Click to collapse
That's a good thought, but I already have over a hundred voicemails on my Hero that I'd like to save without individually emailing to myself. They don't survive my ROM updates, because usually I end up wiping first or else the ROM won't work.

Hmm... is there a way to refresh the Visual Voicemail app? I haven't used it in ages, so I don't remember. If there is, you may be able to refresh the app after a flash and it'll redownload all the voicemails.

Hmm. i'm actually curious if anyone knows how to get back to using sprint vvm cause i used youmail and everything is still forwarded there

apatcas said:
Hmm. i'm actually curious if anyone knows how to get back to using sprint vvm cause i used youmail and everything is still forwarded there
Click to expand...
Click to collapse
i believe you dial *38 and it will disable the forwarding...

chavo2005 said:
i believe you dial *38 and it will disable the forwarding...
Click to expand...
Click to collapse
thank you... been trying that for a while

Anyone know where the VVM files are stored??

Anyone able to help me out? I want to migrate my old VMMs to a 2.1 ROM, but have no way to transfer them or back them up!
thanks

oktanedroid said:
Anyone know where the VVM files are stored??
Click to expand...
Click to collapse
To my understanding, as a former sprint employee, all voicemail is stored server side at sprint. We would always tell customers that this is no way to back up. Sorry, but I hope that helps.

oktanedroid said:
Anyone able to help me out? I want to migrate my old VMMs to a 2.1 ROM, but have no way to transfer them or back them up!
thanks
Click to expand...
Click to collapse
Try using your sound recorder and see if that file is stored locally, should be. About the only way to back up your VM's

snip3rm00n said:
To my understanding, as a former sprint employee, all voicemail is stored server side at sprint. We would always tell customers that this is no way to back up. Sorry, but I hope that helps.
Click to expand...
Click to collapse
I am certain the phone stores it locally somewhere. Because when you receive a voicemail, it needs to download to your phone before you can hear it.
Also, if you put the phone in Airplane mode, you can still listen to VMMs, proving it is stored locally.
What are some good locations to look for where these files are stored??

Okay, I finally figured it out.
You need root permissions to find it.
You can find it at:
/data/data/com.coremobility.app.vnotes
The individual voicemail files are stored as AMR files. You can open them with QuickTime player.
AMR (adaptive multi rate):
Compressed audio format developed by Ericsson; used by many 3G cell phones for voice recordings such as MMS messages; incorporates the Algebraic Code Excited Linear Prediction (ACELP) algorithm, which is designed to efficiently compress human speech audio.

Now it turns out the voicemail data is stored in an SQL database called "vnotes_db".
I used Firefox SQL viewer to open it up. The data fields show voicemail ID #, sender phone #, recipient, guid (?), time sent, time listened to, and some other data.
The voicemail ID is in decimal in the database, but the file numbers are called VN-xxx.AMR, where xxx is the hexadecimal equivalent of voicemail ID.
Does anyone who knows more about computers know of a way I can take this data and automagically convert cryptic filenames to something like "mm/dd/yy hh:mm phone#sender.AMR"?
Thanks

where is the db located on the phone, and do you know how to push the vm's back onto the phone, say after flashing to a new rom?
thanks for your work.
UPDATE:
i was able to just once to get access to the /data/data/com.coremobility.app.vnotes on my phone. within that, there are a couple of folders, one titled "files" and one titled "databases". i copied those to my computer, changed and updated the rom on my phone, and was able to copy those folders back and got my old vvm on my phone.
i used droid explorer to get them off my phone, but i am having some trouble accessing the /data folder with it now, so i will probably have to use adb to push the files.
hope this helps anyone looking to do this.

help with /data/data/com.coremobility.app.vnotes please
I'm try to copy all the voicemails to my computer so i can reflash my phone but i hvaen't been able to copy the files in /data/data/com.coremobility.app.vnotes
i've been able to copy files /data/data and i get the some folders transfered to my computer but not /data/data/com.coremobility.app.vnotes

help with /data/data/com.coremobility.app.vnotes
sorry didn't mean to hit submit....
here is what i've tried.
adb shell
su
i get
#
now i tried
mount -o remount,rw -t ( to be honest not really sure what this mean)
than tried
chmod 777 /data/data
chmod 777 /data/data/com.coremobility.app.vnotes/files
chmod 777 /data/data/com.coremobility.app.vnotes/database
( I think this is supposed to give me permission)
than i did
exit
exit (so went back to )
C:android/tools
from here i did
adb pull /data/data/com.coremobility.app.vnotes c:\voice
and i get filed to copy /data/data/com.coremobility.app.vnotes/files/blah.AMR to c:voice permission denied.
I would really appreciate any help. just trying to learn.... thanks

anyway, i found an easier way to back up vvm's. you do need an app from the market that you will have to pay for called root explorer. it allows you to browse your phone and copy/cut/paste files and folders, including the elusive /data/data folder.
all i did before loading up a new rom was to navigate to the /data/data/ folder and copy the /com.coremobility.app.vnotes folder onto my sdcard. i had a /backups folder so i just copied it there.
flashed a new rom, reinstalled root explorer, navigated back to the /data/data folder and then deleted /com.coremobility.app.vnotes off the phone. copied the backup from my sdcard to the /data/data folder on the phone. rebooted, and all of my old vvm's are on my "new" phone.
do not delete the backup off your sdcard until you verify they are all there. sometimes, you might have to copy each folder (there are four with com.coremobility.app.vnotes) one at a time to the right location.
i was using droid explorer initially to try, but i lost access to the /data/ folder on my phone. droid explorer is a great program to use while explorer the phone from windows, but i think it's still a little buggy.
good luck.

You don't need to pay for Root explorer.
There are two ways you can get VVMs off your handset:
1. Forward them to your email. Open each VVM you want to forward, hit menu, forward, and enter your email. It then sends to your email as a WAV attachment.
2. Tarball the entire directory and pull it off. Using adb shell, create a tarball of /data/data/com.coremobility.app.vnotes/* to /sdcard/whateveryouwant.tar then adb pull it off to your local drive. Inside are AMR files which are playable by Quicktime, and VLC player, and several other media players.

Won't Titanium Backup work? It stores the app and data. It's free! Just a thought.
Sent from my HERO200 using XDA App

Edit Lockscreen Text - Not Carrier Text - CM7

Apologies in advance if I am posting in the wrong section.
I've been talking to some people over on the CM forums about this and we haven't found an answer yet.
If you look in my screenshot, right below the alarm, my name is on my lockscreen. This was put here using an old setting on CyanogenMod 6.1.2 on my HTC Evo 4g. You did this on CM6 by going to Settings > Location & Security. Then there was a checkbox to "Show Custom Message". You then put in text!
Does anyone know which file stores this value on CM7/Android 2.3? I've pulled the eri.xml file from my phone and I can edit my carrier text but I have not been able to find my name. I like being able to edit that text and I wanted to update it to something new but the settings no longer exist under CM7. Thanks for looking!

I have found a ton of utils for editing carrier text but nothing for the text pointed to in the red arrow.

.
.
.
.
.
.
.
*bump*

re
Not the answer you are looking for here but wouldnt it be easier and less annoying and stressful to just flash back to the rom where you could change it, change it to what you want and then flash back ?
that is if i am understanding you correctly and your name shows up now of course with the new rom...
i would think if you changed your name to whatever you wanted then you could go back to the new rom and it would appear the way you want it.

I'm going to give that a try tomorrow. Not sure if it sticks through an upgrade or not but worth a try. Im just curious at this point what confit file it is. Wish there was a filemon or similar tool for Android. Although, if can dump all the files off the phone before and after I should be able to do comparisons....hrm. Project for tomorrow.

OK, finally took the time to work on this a little further.
I reflashed CM6, booted and confirmed there was no lockscreen text. Took a nandroid and copied off the system.img
I then put a lockscreen text in - used a custom phrase that would not be in any of the config files normally. Rebooted, took a nandroid and copied off this system.img.
Extracted both system.img files using unyaffs.
I did a binary and CRC comparison off all the files, no differences. So apparently this setting is stored somewhere else. Hrm......

AHA. Found the little bugger finally.
Had to pull the data.img and extract that. The setting is located in
/data/com.android.providers.settings/databases/settings.db
lock_screen_custom_msgTESTTEXT
lock_screen_show_custome_msg1
These are the two options that I found. So, now to see if I can safely edit that DB file and modify the text on CM7. I already have the text on my screen so the data fields are in the DB file already so I suspect I can just edit that file and then replace it on my phone.

Hm. Editing is no good. Blew away all my settings and I not longer can enable any lockscreen.

Hah. Got it! Using ADB works. Instructions:
adb shell
sqlite3 /data/data/com.android.providers.settings/databases/settings.db
.tables (you should see a secure table listed)
.header on
.mode column
select * from secure;
Now scroll through the results. Find the "lock_screen_custom_m" item under name. Note the ID number to the left. Write the ID number down.
update secure set value='your custom message here' where _ID=NN;
NN is the ID number you wrote down.
.quit
Reboot your phone.
There you go, just in case anyone else is interested.

jdostal said:
Hah. Got it! Using ADB works. Instructions:
adb shell
sqlite3 /data/data/com.android.providers.settings/databases/settings.db
.tables (you should see a secure table listed)
.header on
.mode column
select * from secure;
Now scroll through the results. Find the "lock_screen_custom_m" item under name. Note the ID number to the left. Write the ID number down.
update secure set value='your custom message here' where _ID=NN;
NN is the ID number you wrote down.
.quit
Reboot your phone.
There you go, just in case anyone else is interested.
Click to expand...
Click to collapse
Nice find!
I used a derivative of your method to reset the alarm notification on the GB lockscreen when the buggy POS alarm app G-Alarm left it there after removing all the alarms and uninstalling it.
That one is in system instead of secure, and it can be set to a blank string per http://developer.android.com/reference/android/provider/Settings.System.html#NEXT_ALARM_FORMATTED to clear it

Neat! Glad it could help someone out.

jdostal said:
Neat! Glad it could help someone out.
Click to expand...
Click to collapse
Yup, much appreciated! Also great to learn how to edit databases on Android.
Also, I didn't have a USB cable so I did it with the terminal editor. For convenience (i.e. having a real keyboard) I used Webkey for PC access to my phone and its built-in terminal editor to make the changes.

That sounds painful!
I don't know where I read the guide, but definitely use ADB. With ADB you can basically connect to the shell on your phone from your PC through a command prompt. Makes doing this sort of thing MUCH easier!

jdostal said:
That sounds painful!
I don't know where I read the guide, but definitely use ADB. With ADB you can basically connect to the shell on your phone from your PC through a command prompt. Makes doing this sort of thing MUCH easier!
Click to expand...
Click to collapse
Did you miss the part where I didn't have a USB cable?

[TOOL][ICS][GB][FROYO] Extract contents of Samsung Memo App's Database to CSV File

Many people, including myself, enjoyed using the stock Memo application on Froyo and GB. Unfortunately, it relies on TouchWiz so it is pretty unlikely that it will ever be ported to ICS. There are a plethora of note-taking apps out there to replace it, but how do you get your data back?
You don't need a running version of Memo to do this, just a backup of your Memo.db database.
I've attached a couple of shell scripts that you can run either on your phone or `nix / Mac to help you out. Here is the plan:
extract-memos.sh will take the memo data in a copy of your Memo.db, add a new table with columns for human-readable modified and created dates, as well as color, and write out the result (all memos) in a CSV file. You should be able to read the CSV file in most spreadsheet programs and save the data or transform it as you might need for another use.
Code:
[email protected]:~/Documents/memo-export$ sh extract-memos.sh
Checking database: /sdcard/memo-extract/com.sec.android.app.memo/databases/Memo.db
Checking output directory: /sdcard/memo-extract
You may want to see if there is anything important in
/sdcard/memo-extract/com.sec.android.app.memo/shared_prefs/memo_shared_pref.xml
Data exported to
/sdcard/memo-extract/memo_20120714-100915.txt
Code:
[email protected]:~/Documents/memo-export$ cat /sdcard/memo-extract/memo_20120714-100915.txt
_id,title,content,color,modify_t,create_t,color_text,modify_datetime,create_datetime
1,20120712,"A yellow memo",1,1342138985041,1342138985041,Yellow,"2012-07-12 17:23:05","2012-07-12 17:23:05"
2,20120712,"A tan memo",2,1342139010315,1342139010315,Tan,"2012-07-12 17:23:30","2012-07-12 17:23:30"
3,20120712,"A green memo",3,1342139028326,1342139028326,Green,"2012-07-12 17:23:48","2012-07-12 17:23:48"
4,20120712,"A blue memo",4,1342139045894,1342139045894,Blue,"2012-07-12 17:24:05","2012-07-12 17:24:05"
5,20120712,"A pale blue memo",5,1342139064381,1342139064381,BlueGray,"2012-07-12 17:24:24","2012-07-12 17:24:24"
extract-memos.sh expects to find the database in /sdcard/memo-extract/com.sec.android.app.memo/databases
and will write the output in /sdcard/memo-extract
If you already have a copy of Memo.db (I suggest a copy, as the script adds a new table to the database), you can adjust the input and output directories using the environment variables dbdir and outdir.
If you're not command-line savvy, you might want to try creating a copy of Memo.db using extract-files.sh first. It will try, in order:
The GB location -- /data/data/com.sec.android.app.memo
ClockworkMod backups -- /sdcard/clockworkmod/backups
TitaniumBackup backups (default location only) -- /sdcard/TitaniumBackup
and, if successful, copy/extract into /sdcard/memo-extract
You likely need busybox on your phone to run these scripts on the phone. You will need root if you intend to copy from /data/data
To run them on the phone:
Copy the scripts to your phone; here I am assuming you saved them to /sdcard/
Get a shell started on the phone (adb shell, or a terminal emulator on the phone)
Change directory to where you have the scripts
# cd /sdcard/
Uncompress the files, if you haven't already
# gunzip extract-files.sh.gz
# gunzip extract-memos.sh.gz
Get a copy of Memo.db, either using your own approach, or
# sh extract-files.sh
Assuming that succeed, extract the memos into a CSV file
# sh extract-memos.sh
Thanks to m4xm4n for pushing me to make this available. If you find it helpful, please take the time to poke both our Thanks buttons.
If you have problems, please PM me and I'll try to resolve them.
Latest version is 2012-07-14

What is the warning about memo_shared_pref.xml? I don't even have the file!
I had some reasonably useless information about "check" this and that in my com.sec.android.app.memo/shared_prefs/memo_shared_pref.xml My instance of that file dates back to the first release of the phone. I didn't see it on a fresh GB-created version, so it might be Froyo cruft, or who knows. You probably should check this file to see if there is anything useful to you in it.​
My _id values aren't all there
Missing _id values are from the memos you have deleted.​
What are _id values?
They are an internal number that the Memo app used to keep track of your memos. You probably don't need them to import.​
What are the long numbers for modify_t and create_t?
Internal "unixtime" in milliseconds. If that sounded like gibberish, use the human-readable versions that are in your local timezone.​
The create/modify times seem off by several hours
Strangely, I have some memos that seem around eight hours off in the opposite way I would expect if they were in UTC. I don't know why.​

(reserved for information on importing into other apps)
There are a ton of note-taking apps out there. Right now, I haven't found any that can import the content, the created and modified dates both, and hopefully the color in a reasonable way. If I do, I'll post here how to get the data easily into that app.

Would you be able to post a new link to extract-memos.sh? I just tried clicking on the current one, and both only led to a page that said "410 Gone."