• Please be aware: Kaspersky Anti-Virus has been deprecated
    With the upgrade to Plesk Obsidian 18.0.64, "Kaspersky Anti-Virus for Servers" will be automatically removed from the servers it is installed on. We recommend that you migrate to Sophos Anti-Virus for Servers.
  • The Horde webmail has been deprecated. Its complete removal is scheduled for April 2025. For details and recommended actions, see the Feature and Deprecation Plan.
  • We’re working on enhancing the Monitoring feature in Plesk, and we could really use your expertise! If you’re open to sharing your experiences with server and website monitoring or providing feedback, we’d love to have a one-hour online meeting with you.

Resolved How do I access a database dump from a backup?

Janus Bahs Jacquet

New Pleskian
Server operating system version
Windows 2019 Server
Plesk version and microupdate number
18.0.42 Update #1
I have a remote Windows 2019 server running Plesk. Incremental backups are done daily and stored locally on the server; complete backups are done weekly and stored in a DigitalOcean space. Backups include database dumps of the SQL Server (Express) database.

My own machine is a Mac, running Ventura 13.3.

It seems several hundred rows were recently deleted from a table in the production database, so I would like to retrieve a backed-up version of that table to compare with and transfer the missing rows back over. I do not want to restore the backup, since other things have changed in the database since then, including in the same table the rows have gone missing from.

I’ve downloaded the appropriate full backup (about 17 GB). The downloaded file was a ZIP file (not TZST or TAR!) and it extracted with no problems as a regular ZIP file. Inside it, in the subfolder domains > mydomain.com > databases > myDatabase, there is a ZIP file called backup_dbdump_2304270300.zip, which is about 225 MB in size.

I went through many, many, many failed attempts to extract this file. Suggestions I’ve seen here include using command-line tar, 7-zip, Peazip, The Unarchiver and others, but none of those would extract it – all failed saying the file was not in a known format. The only thing I could find that did anything useful was PowerArchiver (which isn’t free, but has a 30-day free trial), which was able to open the file and confirm that the compression was zstd-ws (whatever that is precisely – some form of Zstandard). Extracting the file took nearly ten minutes, but it succeeded.

The resulting file is called backup_dbdump_2304270300 (no extension) and about 2 GB in size – and I cannot get ANYTHING to do ANYTHING useful with this file at all. I have no idea what format it even is. It looks like a binary file (opening it as text reveals some familiar-looking strings like “MSSQLExpress” here and there, but most of it is just gibberish), but beyond that, I have no idea what it is.

So my questions are:
  • What format are the database dumps used in Plesk backups?
  • How can I get to the actual contents of the file (hopefully as an SQL dump)?
  • If accessing the actual file contents directly is not possible, is there a way to restore the dump into a separate database (I can easily create a temporary one, for instance)?
 
Thank you so much! In hindsight, I suppose it’s fairly obvious that it’s just a .bak file, but (1) I didn’t realise that .bak files are binary, and (2) with all the trouble extracting files within files, I was a bit lost.

Restoring to a new database worked exactly as it should!
 
No problem, and ye Microsoft's SQL backups are binary formats (MySQL backups is just standard SQL query entries in plain text) because, it's Microsoft. And the extensions isn't really needed thus the Plesk backup doesn't include it (tbh file extensions in general isn't needed, as long as you throw the right interpreter at whatever file, it'll work, like opening a PNG in Photoshop without an actual extension on it would still open the file because the file's header is what tells the program what it is).

(isn't file systems and applications fun?)
 
Back
Top