December 19, 2013 at 8:01 am
I recently started a new gig as DBA and their current backup plans are creating a question for me.
We use development, staging, and production environments.
For production servers, I completely understand the need for full, diff, and trx backups at varying frequency.
For development and staging - once a day, all 3 are done in the same job. What is the point of that?
Our staging environment is barely used at all. We're an agile shop, doing 2 week sprints, and we launch everything to production the day before sprint end. The day before that, everything gets pushed to staging as a practice run for the push to production the following day. That's pretty much the extent of it. During the sprint, once in awhile, I'm tasked to do one-offs to production and I test those scripts in staging 1st, but I'm talking maybe once every couple days. We care so little about the staging environment, that if/when it does crash & burn, we just wipe it all out and restore from production backups.
Development does have a huge amount of activity, so I'm on-board for Full backups once a day, but shouldn't the transaction log and diff backups happen more frequently than once a day and not at the same time as the full?
Side-note... the previous DBA was extremely secretive and proprietary about how and why he did things and was let go unexpectedly before anybody could document anything or formulate any kind of exit strategy. I'm flying blind.
Any advice/enlightenment would be appreciated.
December 19, 2013 at 8:04 am
No point. In fact, unless losing 24 hours work off dev is unacceptable, there probably doesn't need to be diffs and logs, or maybe weekly fulls and daily diffs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2013 at 8:16 am
On our development environment we have weekly full backups and have an automated process to create daily database snapshots.
There is no need to perform all three in one go. Switching the dev dbs to simple recovery model and disabling the transaction log backup would leave you in the same position and potentially allow you to recover some disk space by shrink the log files (in a controlled way).
December 19, 2013 at 8:31 am
Keep in mind that the development server is just that - development. The developers should be able to rebuild anything they lose from the previous day if they're scripting everything.
As for the documentation, documentation ranks very close to meetings in my list of favorite things. I know it has to be done, but I'd much rather be coding. Nonetheless, congratulations on your new DBA role. Good luck in figuring out what does what and how everything works. Hang in there - it can be very rewarding.
December 19, 2013 at 8:41 am
I am the documentation nazi! It's a big reason I got the gig. They needed someone to not only figure it out and clean it up, but also to document the clean-up.
December 19, 2013 at 12:11 pm
As long as development is in source control, I wouldn't sweat it much. I had nightly backups running on dev (along with source control) just because it made it faster to get them back online with data AND code. But, the data was just test data. If we lost it, it was nothing in terms of down time, just my time (which I tried to save where I could). Our staging was exactly the same, we rebuilt it from prod on a nightly basis and sometimes on demand. No need to back it up at all (unless you're just testing your production backup scripts).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply