I've done some reading around this subject here and on StackOverflow but nothing really answers my question enough for me to go forward.
I'll explain we have a MS SQL 2008 server running, the transaction log file backup has been failing every night, now I've had a look at some of the other errors we've had, long and the short of it, it looks like this could be disk or I/O or so I'm informed, as this is a hosted machine we've initiated a change but that takes time and Sods law means the closer you are to making a change the more likely something catastrophic will happen. ie I need to make sure we're safe.
I'm not a DBA (If I was I probably wouldn't be asking questions).
Now as I understand it, if the transaction log backup fails when you are on a Full recovery model, then you're stuffed.
So I've changed to a simple recovery model (I appreciate the implications interms of point in time better we have a backup than one that will fail on restore) as I understand it a Simple Model writes everything to the database during the backup, what I'm not sure of is, what this really means in terms of the transaction log. ie if the database died this moment and I had to restore from a backup, but the transaction log backup fails what this means to me. We have a backup plan for the DB and the logs. We know the logs backup fails, but if it's in simple recovery mode and the database is backed up but the log back up fails would I be able to recover the database? By recover lets get this straight, I'm talking about getting the thing working again by simple restoring from my last back up?
Now to the 2nd part, if we assume there is a problem with a sector on the disk (Appreciate I need to move as soon as I can, but new machine etc etc are out of my sphere of influence), then it occurs to me that deleting and recreating a log file won't work as the file is likely to be in the same part of the disk. So wouldn't creating new log file and removing the old log file (but leaving the old file on the disk) be the thing to do? As that would effectively stop the bad sector being used (Different partition would also solve this, just this is a C: and D: affair and space is tight on C:.
So if so, how do I do this? I guess I'm really asking for step by step here as I'm unsure of all implications, I've read elsewhere that you will be in a whole world of pain if you just delete a log file by say stopping the service deleting the file and recreating new file same name and starting the service. So what do I do?
Really appreciate all comments
Thanks in adavance