July 24, 2009 at 2:34 pm
Hello.
I have a production database and it is desired that every month we make a Month-End copy of the database (with a name like db_2009_07_31).
We are using SSRS and I can pass in the target Server & Database to SSRS with no problem. However, Tables, Stored Procedures and Functions will get modified in the production database as time goes on and therefore some SSRS reports would not work for the database copies that do not have the same objects as the production database.
I am not sure what I can do in order to keep snapshots up to date.
I am considering a DDL trigger in the production database and then script some sp to apply changes to the month end copies.
Has anyone done this or is there maybe a better way...Replication?
Thanks in advance
Anthony
July 30, 2009 at 6:52 am
How big is the affected database?
If all business wants is to have a snapshot, have you considered taking a full backup and restoring it as the new database?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 30, 2009 at 7:19 am
Right now, each copy is 6gb.
The way I am creating the end of month copy is by simply restoring from the production database backup and specifying a new database name (ie db_2009_07_31).
I am looking for the best solution to keep the database objects (not data) in the month end copies up to date so that they have the same schemas as the active production database.
If someone changes a stored procedure or modifies a table schema in the production database, I want those changes to be pushed to the database copies because if you try to run a SSRS report against the old database that does not have that object or the old object is there but missing something then the report will fail.
July 30, 2009 at 2:45 pm
Got it.
You are in the right version 😀
SS2K5 replication by default replicates DDL changes to subscribers so, replication is your answer.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply