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

[Janus Bahs Jacquet]

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)?

 

[scsa20]

It should be a standard Microsoft SQL backup. Please refer to Quickstart: Back up & restore database - SQL Server for restoring from a backup.

Since you want to just view it, you just create a new database in SSMS and restore the file to that new database.

 

[Janus Bahs Jacquet]

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!

 

[scsa20]

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?)

 

[Janus Bahs Jacquet]

Yeah, it’s not needed, but it sure would have been nice if it had been added nonetheless – I wouldn’t have needed to ask, then!