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 (
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.