In the last video we looked at backing up your SQLite database. If you're using a hosted provider they should do it for you. If they're not, find a new hosting provider. That's very silly. Then dot backup, vacuum into to compress it and then just copying the actual files, the dot SQLite and the dot SQLite dash wall file.

I briefly mentioned light stream which you can look into if you want an open source sidecar that streams your backups ups out to s 3. Now in this video we're gonna look at exporting the database which, you know, potato potato. It sounds it's it's very similar to backing up but it is slightly different. And I'll show you how it is slightly different. So we're gonna run SQLite 3 database dot SQLite, and we're gonna pipe through, or rather just execute a command as well like we did the last time.

And this time we're gonna do dot dump, and that's all we're gonna do. And let me show you what comes out. It's gonna be a lot, so prepare yourself. Here we go. It gives you actual honest to goodness SQL statements.

And so this is more of an export than it is a backup because you can't just take this dot SQL file, which we're gonna pipe it into a dot SQL file. You can't just take this, and put it and open it as if it were a SQLite file. You can pipe it back into SQLite and that will create a database. But this is, extremely portable. So this is honest to goodness SQL that you could take and you could then use to import into a postgres or my SQL database.

So this this could be a good this is why I call it exporting and not backing up. This type of text compresses very nicely so let's we'll look in a second at piping it through gzip to compress it. But you can see here at the end it gives you every single thing that you might need and then closes it off with a commit. Before I show you all of that, let me tell you what we're gonna do. We're gonna take the output of that dump command and pipe it through gzip and then put that into a file and then we're gonna turn around and pipe that file back into SQLite so you can see kind of, the dump and restore process.

So we will do SQLite 3 database, dot SQLite. And then since it's only just this, not there are no spaces in this command, I don't think we need to wrap it in quotes. And then we're gonna pass it through gzip and we'll just put it into gzipped dot SQL dot Gzip. So I'm not gonna name it SQLite because this is not a backup. This is a bunch of SQL statements and now that is going to run.

While that runs, let me tell you about something else. There is a dot recover command. So the first error that dot dump runs into, game over. So if your bat if your database is in some sort of, corrupted or wonky state, you can run dot recover and it's gonna do its best to recover all of the data possible. So that is actually that's finished so we can just do it here.

Let me show you, without without that you can do dot recover and it's gonna look very similar because this database is not in a wonky state. But if it were in a wonky state, dot recover would get you a lot farther than dot dump which is going to stop at the first sign of wonkiness, which is a scientific term, wonkiness. No. The first sign of corruption dot dump is gonna error out. So should you ever need it, dot recover is here.

Hopefully hopefully you don't ever need it. I'm going to yeah, we're just gonna open nope, we're not gonna do anything. We're gonna wait until that finishes and there we go. So now over here, let's, let's see what we've got going on here. We've got gzipped dot SQL dotgz.

Okay. Now we're going to reimport this SQL dump because remember it's not a SQLite database, and all we're gonna do is we're just gonna pipe it into SQLite. So we're gonna use gzip and it is called gzipped.sequeltotgz, and you can just pipe it into SQLite 3 and give it some name. You're creating a new database. So we'll call this from gzip dot SQLite.

And if you run that, it's gonna take all of those, SQL statements and it's going to create a new database. So this is a compressed this is a compressed version just like vacuum into because there are no there's no pages whatsoever to say nothing of free list pages. It's just sequel statements. And so this is a forensically clean, compressed database and with gzip it compresses quite nicely. So if we look at this now, you'll see we have a from gzip.SQLite.

And like I said, it is much smaller than the original because there's no space in there. It's not sparse. And so if we were to open this up from yep yep, and then we look, there are all the tables that match from the original database. So this is less a backup. It could be used as a backup.

I don't see why not. But this is more of an export. So when it comes to exporting or getting raw SQL out that you need to put into another SQLite database or another database system, you can use dot dump. If you're in a wonky state, you can use dot recover. I would recommend gzipping it because it's gonna be a lot tighter.

There's a lot of repeated text in there, insert into over and over and over again. So gzipping it is probably a good idea. But this is another way with which or by which you can get all of your data out into a portable format.