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.
BACKUP
/RESTORE
at all. – Jon Seigel Jun 25 '12 at 16:53