5

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.

Improve this question
  • In order to check your version number, you'll have to restore the backup. You can do it to a new database with a different name and then peek in the table. Perhaps you could incorporate the version number into the backup filename? Trust, but verify! – user92546 Feb 25 '12 at 4:41
  • You have a logic error. If you need to check for a version number then that is independent on if you check from copy or a restore. A backup is smaller but take longer. Backup = restore - mdf = attach. If you are going to replace the production mdf to check then the check is mute. – BalamBalam Feb 25 '12 at 4:49
  • Sorry for the dumb question, I'll do more homework and find a better starting point. Feels like a bad design, hence the bad question. – eselk Feb 26 '12 at 1:28
  • Back up and restore what? Everything? For what purpose? If, for example, all they want to do is compare against a previous version of something, that should be implemented as an application feature within the database; it shouldn't involve BACKUP/RESTORE at all. – Jon Seigel Jun 25 '12 at 16:53
4

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.

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy