October 12, 2016 at 2:52 am
I'm the reporting guy so I know how to make whizzy reports and write fast and efficient queries. When it comes to the DBA side of things I'm very much a beginner.
We have a replicated copy of the live data on our reporting server and this in turn is replicated to the failover server. Last night the drive holding the backups (and tempdb) on the failover generated a critical alert for space. The space is back down again and I presume that it was something to do with tempdb getting hit during the nightly backups.
The thinking is to stop backing up the failover database to free up the space permanently. The logic being if we lost those databases we would be able to restore from the backups of the live servers.
I can't help thinking this is a bad idea. Although it seems redundant to have backups of a copy of a copy, I'm very twitchy about not having those backups.
Can anybody advise please?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 12, 2016 at 3:03 am
I can't see any value in backing up a database that is a copy of another database that's already being backed up.
Also, are your backup files really on the same drive as tempdb? I would strongly recommend separating them.
John
October 12, 2016 at 3:14 am
Cheers John.
The backups are on the same drive as tempdb and I didn't think that was a good idea. If we stop the backups then tempdb will obviously be on its own drive but if we carry on with the backups I'll recommend moving it.
None of us could see any value in having the backups of copies of copies but I wanted to make sure there were going to be no drawbacks in not having them.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 12, 2016 at 6:27 am
Do you test the backups from your primary production servers? By test, I mean, the full range of tests[/url], including a restore? If so, toss those secondary backups. They're not doing anything. If not, I'd implement testing before I tossed those secondary backups.
"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
October 12, 2016 at 7:26 am
I have to say I don't know whether the backups are tested or not. The Ops guys we were going to speak to about getting rid of the secondaries have been busy all day but I will ask when do catch them.
We also need to make sure that these aren't the only backups. Not that they would be...right?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 12, 2016 at 7:51 am
BWFC (10/12/2016)
We also need to make sure that these aren't the only backups. Not that they would be...right?
You hope. That's why we verify everything. A little paranoia goes a long way with backups.
"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