SQL doc loses all changes if database is updated?
crystalusa
Posts: 2
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?
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?
Comments
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.
Redgate Software