SQLDumpSplitter » Use cases » Backup archiving by table
Download SQLDumpSplitter Free Trial » Buy SQLDumpSplitter Now »
The situation
Retention is never uniform. The audit log and the ledger have to be kept for years - possibly by law - while the session table, the cache table and last quarter's staging data can go after a month. But the monthly backup is one .sql file per database, so the only choice is to keep everything or throw everything away.
It also makes the archive unreadable in practice: a 20 GB dump per month has no single table you can pull out of it later, and restoring one table means importing the entire file into a scratch database first.
What you do
- Split the dump by table. Each table in the dump becomes its own
.sqlfile. - Archive them according to the policy that applies to each one: the audit and ledger files go to long-term storage, the rest go on the normal monthly rotation.
- To restore one table later, run that table's file. No scratch database, no importing everything else first.
Each file is still valid SQL and runs on its own, because the split follows the dump's own statement boundaries. The original dump is left exactly as it was, so this is a non-destructive extra step - split a copy, and keep the original if you prefer.
What it will not do
- It does not compress or encrypt. The output is plain SQL files. If your retention policy requires an encrypted archive, encrypt the files afterwards with your usual tool.
- It does not manage retention. It splits; deciding what to keep and for how long is your backup process.
- It does not connect to a database and does not restore anything - you run the files with your normal client.
- Whole tables only. Splitting by table gives you one file per table; it does not extract a date range out of a table. To cut a table by rows, split by row count instead.
Related
Splitting: by table, by file size, by row count. Producing the dump from a live database: DBToFile. Cutting other kinds of files by value: DataFileSplitter.