SQL Server backup, attach to BAK file?

Question

I have a SQL Server database with a full text index. When I created the app that uses it, I made a backup of the database and I ship the .BAK file with the app. As part of the app install it does a restore of this .BAK file. I like this option because it auto-creates all of the tables for me, with the default rows some tables need, and includes the full text index as well. Nice and simple.

Now I need to add a backup/restore feature, and the end-user wants it to work more like an MS Office app than a client/server app. He thinks of it as a file, and just wants to backup to a file, and then restore whenever he wants... not your typical SQL Server backup, and not automated either, just simple "copy" backup, no appends/backup sets/etc.

I'm thinking of using BACKUP DATABASE TSQL command, and he doesn't mind that it only saves to a folder on the server. Then restore database TSQL command to restore -- what I currently use for my initial install.

I ran in to one problem though, and this is my question. I have a version number that I keep in a row in a table that I use to avoid old apps connecting to new databases and vice-versa. I'd like to check this version before allowing the restore. I know if it were an .MDF file I could attach to it and check before restoring, but can I do this with a backup (.BAK) file?

Am I missing a better backup/restore method that would work better for my design? For example, should I detach the .MDF file and copy it and the log file, as the backup, and then attach it as the "restore" process? Will that preserve everything the way backup/restore does, including the full text index?

I'm using SQL Server 2008 Express and my app is C# .NET 3.5.

Edit: Backup does not need to happen while other users are in the database, it is fine if exclusive access is required, it is just a small office with a few people that can easily shutdown if needed.

Answer 1

First there's no way using the native tools to mount a backup file and read the data in it. That said, what you can do is write your version information into the description of the backup using the DESCRIPTION keyword. Then read this information using the RESTORE DATABASE WITH HEADERONLY to get the information and make sure that they have the correct version.

Answer 2

There's not much information you can get from a backup without restoring it. About the best you're going to get is the information from RESTORE HEADERONLY, which is nice, but can't really give you the application-specific data you need. You could perhaps go by date instead of version number in your code, or as user92546 suggests, incorporate the version number into the filename. If you were really paranoid about someone manipulating your filenames, you could create a short hash of the version number and a secret, and use that for your filenames.

Honestly though, it seems to me that if the application really does treat the database as a file, you're good using the attach/detach method and saving it off somewhere.