SQL doc loses all changes if database is updated?

Documents SQL Server 2005 and 2008 databases.

Moderators: JonathanWatts, David Atkinson, Michelle Taylor

SQL doc loses all changes if database is updated?

Postby crystalusa » Wed Oct 13, 2010 11:03 pm

We bought SQL Doc so that we can document "version 2" of our production database.

SQL Doc points to a copy of the database on my local SQL Server. Once a week or so we backup/restore from production to my local machine and run some scripts that bring the schema up to v2.

I spent about 10 hours adding in all of last week's changes, brought over the latest copy of the database to my local server, ran the scripts, and now all my work is gone!

This is the second time this happens. How do I save my work and prevent this from happening again?
crystalusa
 
Posts: 1
Joined: Wed Oct 13, 2010 10:59 pm

Postby james.billings » Fri Oct 15, 2010 9:50 am

Thanks for your post - are you referring to adding descriptions against various objects in your database?

If so, these are stored in the database itself as extended properties (assuming you have write access) - as detailed here:

So, when you restore your database, you will get a version that doesn't have all the descriptions you previously created and then lose them.

Is it possible for you to add the descriptions against the production database so that when you restore, you keep all those?

Failing that, you may be able to produce a script you can re-run after restoring to replace all the descriptions you entered previously. The example here creates a stored procedure you can then execute that will produce all the create commands for the extended properties. I think it looks like it only works on tables, but you can perhaps amend it to work on other objects too. You could then run this after documenting everything, produce the script, then run it on the restored data.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Doc 2

Who is online

Users browsing this forum: No registered users and 0 guests