Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

We store the version number of our DBs as an extended property. As part of the automated build process, I want to be able to check that the "empty" DB that is part of the build set matches the version number of the executables being built.

I understand that I can't just read the *.bak file directly. I already query the (restored) DB to get that property. However, as part of the build process it seems silly to restore the entire DB just to get this one piece of information.

Is there a simple way to get just the extended properties without restoring the whole DB?

share|improve this question

migrated from stackoverflow.com Dec 6 '11 at 2:54

2 Answers

up vote 3 down vote

There's really no other way that I can think of than restoring the database. The best option would be to have versioning stored elsewhere so that you don't have to go through all this trouble just to get a version number.

What you could do is have a little configuration file and a small application that performs the backups for you, and stores a key/value pair of filename/version. That way you will have a reference as to what backup file is for which database version. An easy application to write and it would save you a ton of time with restoring/querying just to find out this bit of data.

share|improve this answer
One of the critically important points of storing the version info in the DB itself was to avoid having an external reference that could be lost, or altered independently from the DB. If the entire DB must be restored I can live with that. I just hoped there was a better way. – mickeyf Dec 6 '11 at 17:18
up vote 1 down vote

The extended properties for a database are stored in the hidden catalog table sys.sysxprops within the database itself, so you're not going to get to that info without restoring the database (or at least the PRIMARY filegroup).

As an alternative, you can add a description to a backup:

BACKUP DATABASE pubs TO DISK = 'pubs.bak' WITH DESCRIPTION = 'pubs 12/6/2011'

Then use RESTORE HEADERONLY to get that description (in the BackupDescription column):

RESTORE HEADERONLY FROM DISK = 'pubs.bak'

If you need to automate this process, you can apparently pass in a variable to the DESCRIPTION option, e.g. "DESCRIPTION = @property". Query the extended property, stuff it in a variable, then pass it to your BACKUP statement.

share|improve this answer
This has the desirable effect of embedding a version number in the DB in an easier to retrieve way, but requires the developer to always backup a modified/updated DB using the program/script that writes the description into the backup, not by using any of several other methods and utilities that they are in the habit of using. If they were that disciplined life would be simple. I already don't expect them to always get this right, I just want the build process to detect when they've forgotten to synchronize one of the DB itself, the change log, or the app that brings older DBs up to date. – mickeyf Dec 6 '11 at 17:41
+1 Neat idea, I like it. – Mark Storey-Smith Dec 7 '11 at 9:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.