November 26, 2011 at 6:40 pm
I am having the strangest issue over the last few days. I have a maintenance plan that backups all the user database on a daily basis. The first step in this job is to delete the backup files from the previous day, that step works fine. The next step is the backup database doing just the user databases. For some reason starting last week, the agent has been creating two copies of each database backup. Has anyone else seen this or a way to stop it. As a trial fix I deleted the maintenance job and built a new one. This did not solve my problem.
Details of setup
SQL Server 2008 R2 SP1 in a 2 node cluster
Backup location is a fiber connected storage drive.
Thanks
Aaron
November 26, 2011 at 6:52 pm
I am such a moron. I figured out what my problem was. This one is funny. Last week we tested our Disaster Recovery. I brought up a single node of the cluster at our DR site and restored all the production databases along with MSDB. So everyday at 8 PM the job on the production cluster ran the backup job and the server in my DR site ran the same job execpt it backed up all the data to the production shared drives across the network.
November 28, 2011 at 2:25 am
It's OK Aaron. 2 Backups are better than 1 (or none) 😉
November 28, 2011 at 2:50 am
Dev (11/28/2011)
It's OK Aaron. 2 Backups are better than 1 (or none) 😉
I think it is not OK (not always atleast). Imagine the hard disk gets full and somehow some production DB on that DB as well. Moreover, seems like the OP is not handling the DR exercise as it should be. To me, he should pay more attention to such critical exercises.
November 28, 2011 at 3:08 am
jewel.sacred (11/28/2011)
Dev (11/28/2011)
It's OK Aaron. 2 Backups are better than 1 (or none) 😉I think it is not OK (not always atleast). Imagine the hard disk gets full and somehow some production DB on that DB as well. Moreover, seems like the OP is not handling the DR exercise as it should be. To me, he should pay more attention to such critical exercises.
If you read OP's previos post you will understand he wants to get rid of duplicate backups because he (& I) is aware of the risk you pointed out.
I commented to give a moral support to OP so he shouldn't disappoint himeself. We all did misc. mistakes initially & they are lessons learned.
November 28, 2011 at 3:34 am
Dev (11/28/2011)
jewel.sacred (11/28/2011)
Dev (11/28/2011)
It's OK Aaron. 2 Backups are better than 1 (or none) 😉I think it is not OK (not always atleast). Imagine the hard disk gets full and somehow some production DB on that DB as well. Moreover, seems like the OP is not handling the DR exercise as it should be. To me, he should pay more attention to such critical exercises.
If you read OP's previos post you will understand he wants to get rid of duplicate backups because he (& I) is aware of the risk you pointed out.
I commented to give a moral support to OP so he shouldn't disappoint himeself. We all did misc. mistakes initially & they are lessons learned.
I can uderstand your valid point. But if you would have appended something like "But be careful with such critical exercise next time" OR something to push him to follow the standards, this would have been much much better comment for the OP and the persons to follow this thread. Learning the hard way is not the best way. 😉
November 28, 2011 at 3:45 am
jewel.sacred (11/28/2011)
Dev (11/28/2011)
jewel.sacred (11/28/2011)
Dev (11/28/2011)
It's OK Aaron. 2 Backups are better than 1 (or none) 😉I think it is not OK (not always atleast). Imagine the hard disk gets full and somehow some production DB on that DB as well. Moreover, seems like the OP is not handling the DR exercise as it should be. To me, he should pay more attention to such critical exercises.
If you read OP's previos post you will understand he wants to get rid of duplicate backups because he (& I) is aware of the risk you pointed out.
I commented to give a moral support to OP so he shouldn't disappoint himeself. We all did misc. mistakes initially & they are lessons learned.
I can uderstand your valid point. But if you would have appended something like "But be careful with such critical exercise next time" OR something to push him to follow the standards, this would have been much much better comment for the OP and the persons to follow this thread. Learning the hard way is not the best way. 😉
I would have to disagree on that last point, I find learning the hard way the better way. Yes we are all human at the end of the day and yes mistakes happen, its how we deal with this mistakes and problems which I find makes a good DBA as it provides more experience to dig into the problem, not just plod along day to day.
It's great having a server(s) that behaves and does exactly what you want, but what fun and experience do you get from that, you dont learn anything from it, I find that in interviews I have conducted that there are a lot of people who say they can do X,Y,Z but when we interview them we make a note of that and say, well you can do X,Y,Z but what would you do if A,B,C happens while doing X,Y,Z and they cant answer the question and dont get the job.
Thats just my 2p's worth
November 28, 2011 at 5:05 am
I would have to disagree on that last point, I find learning the hard way the better way. Yes we are all human at the end of the day and yes mistakes happen, its how we deal with this mistakes and problems which I find makes a good DBA as it provides more experience to dig into the problem, not just plod along day to day.
It's great having a server(s) that behaves and does exactly what you want, but what fun and experience do you get from that, you dont learn anything from it, I find that in interviews I have conducted that there are a lot of people who say they can do X,Y,Z but when we interview them we make a note of that and say, well you can do X,Y,Z but what would you do if A,B,C happens while doing X,Y,Z and they cant answer the question and dont get the job.
Thats just my 2p's worth
I agree that you learn from your mistakes but at the same time blunders would only make you lose. The point I want to make is that we should manage our environment properly. If the OP would have followed the Change Management Procedures closely (doubt the OP had a DR PLAN document. If had then would not have followed that which is even a bigger mistake), this would have not happened. I doubt you would be asking question from an interviewee that "Have you ever by mistake put two jobs in production, taking two backups of same DB on the same disk. If yes, the how you did you resolve it?". Such mistake is just to put yourself in trouble and asking to put you in the firing line. In such exercises, the test environment should have been isolated or at least no interaction with production environment should be possible.
As I said earlier, I can understand the point. To err is human; I have done many mistakes as well. But such things could be prevented with a well-controlled environment. And just to learn the hard way, does not give you the leverage to make the blunders. This could keep one unemployed for most part of his/her career. So while cheering up the OP, we should have warned him as well for his future endeavors.
November 29, 2011 at 6:56 am
1) I don't EVER want to "learn the hard way" when it comes to HA or DR!! :w00t:
2) I advise my clients to NEVER use maintenance plans. Get the awesome, free, fully documented maintenance suite from ola.hallengren.com.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 29, 2011 at 7:03 am
TheSQLGuru (11/29/2011)
1) I don't EVER want to "learn the hard way" when it comes to HA or DR!! :w00t:
Me too 😀
November 29, 2011 at 7:14 am
Aaron Christenson (11/26/2011)
The first step in this job is to delete the backup files from the previous day Aaron
A small point, and you may have a good reason for doing it the way you are... I wouldn't delete yesterday's backup until I knew that today's backup was successful.
November 29, 2011 at 7:58 am
I guess I should have explained better. My backups are desiged to delete all the previous days backups because I have a tape solution that backs-up those files throughout the next day. I have always thought that if I need to go back to tape to get database data older than 1 day then I have bigger problems than needing to go to tape.
What I am still curious is why the backup job from the DR node which had a different server name was backing up data to the production drive space. It should have been backing up the data to the DR drive space.
November 29, 2011 at 8:01 am
What I am still curious is why the backup job from the DR node which had a different server name was backing up data to the production drive space. It should have been backing up the data to the DR drive space.
Check for the path / path variable (if any) in your job.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply