tricky tricky tricky backup question --- not really
-
I am trying to figure out what the best backup strategy is for our environment. It has one in place that I dont quite like; but before I changed it, I wanted to run by you experts what I had in mind so you could tell me if the new way was good or not -
Current Environment -
- There are 6 databases on the server catering to one application each
- Each of these databases have m staging tables and n reference tables
- Staging tables are overwritten every time the application runs, and reference tables remain the
same all the time (except for very infrequent manual update requests)
- The backup requirements are very much only around the ability to have the reference table data
(&schema) available and the schema of staging tables. All staging table data can be recovered
through an application re-run and the time required to recreate the data through a re-run is an
acceptable recovery time
- All applications run once a month
Current Maintenance Strategy -
- Currently, there are 3 SQL jobs that run monthly right before the first of these 6 applications run
- the first one truncates all staging data
- the second one performs full backups of all databases (reference data is to the tune of few
megabytes) so these backups are not bulky. They are retained for 6 weeks - so that covers one
month
- the third job runs "space reclaim" tasks, and shrinks the database & transaction log
- All backups are to SAN mounted to the server which are backed up during enterpise backups to
TAPES that are shipped offsite for DR provisions
What am I proposing??
- I dont see a lot of value in the third job; in fact I get the feeling its hurting by shrinking the
DB and the log only to have it grow again during the next cycle. I could change it to stop shrinking
and just reorganize all used and unused space together????
- If possible, export the schema/dependencies for all objects within the database to a script that I
can use to recreate the database. This could be my alternative to the second job, although I dont
quite know what would be a good way of exporting all the DB object scripts in batch mode
- As for the first, I could just run it as part of the application through a stored procedure
The only reason I am proposing these changes is because business cannot decide on a set date in the month when they would want the monthly processing to kick off. Some months it is the last Friday, some others it could be the 25th day of the month. We have the above 3 jobs I mentioned scheduled on 18th day of the month, but the 2009 schedules were out recently and it turns out there are some application schedules for the 16th and the 17th.
I do not see why business should have to be constrained on when they should run the app. And this was how I felt I could work around it.
As always, any inputs you could provide would be truly appreciated!
-
I would definitely get rid of the shrink tasks. Automated database/log shrinking is almost always a bad idea.
I think the backups should be fine. Don't bother with scripts as part of that. You'd have to re-create the data as well as the objects, and that's much easier to do with backups. Have the scripts in source control, and update them if objects/schema/procs are changed as per usual practices on that subject.
On the truncation, that probably would work in the application. It seems like the most logical place for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared,
Thanks much for the quick response! But the reason I was thinking of the scripts-export was currently the backups follow the TRUNCATES .... if they dont, the backups will get bulky (will shoot up from few MB to 100s of GB).
-
I don't suppose it would be possible to have the data you keep be in one database or in one file in a database, and the rest be in another database, or another file in the same database?
If you could do that, you just back up the stuff you need, and keep the scripts in version control.
The other option would be to keep a calendar table of when the applications will be run, and have the backup job check that each night, and run the truncate and backup if the next day is the day for the application (or however that would need to work).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks again .. that sounds like a good idea -
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply