DR restores - what SQL ops are needed?
-
We use Veritas Backup Exec as our enterprise backup system. Being that their SQL agents are expensive, I am not using them for some low-impact MSDE installations I have in the environment.
However, I need to have a recovery procedure outlined in my DR documentation.
I wrote OSQL batch files and SQL scripts that backup all DB's to an NTFS directory and I back them up to tape from there. In the case of a disaster, does anyone know what the state of SQL will be after a full operating system disaster recovery.
I think its safe to say that SQL services will not be working. I am hoping someone on the list will know the basic steps for SQL recovery based on file level DB backups.
Thanks in advance.
-
Hi
We use a similar concept where I work. SQL maintenance plan backs up to disk, then backupexec takes these files to tape.
My recovery plan is based on restoring to a clean SQL install, then restoring master, MSDB and then restoring the user db`s.
Its not perfect but I have used it successfully once. Sometimes, when the master is restored, SQL will fail to find certain db`s when restarted, but these problems can be negotiated.
Cheers
James
-
Your DB Disaster recovery plan should depend on how big your DB is how transaction oriented it is. Accordingly plan on FULL, DEF and Trans log backup. Some of my DB's are fairly large and trans oriented. For these - I do a Full backup twice a week, do diff twice a day and do trans log backup every 1/2 hr. I also have a standby read-only DB for these on a diff server, so in case of a disaster, I just need to recover that DB, applying the last successfull trans log.
For all the other smaller DB's which are fairly static, I take a full backup once a day. I store all these on a NAS, which are picked up by Veritas to tape nightly. Again make sure you implemtent a good disaster recovery plan and test it off and on to see how affective it is and how long it takes for you to complete the recovery process.
-
The MSDE DB's mentioned in the thread have very low transaction rates. They typicaly feed one to two user apps that are used a couple times per week only. Accordingly, they get a nightly full backup only.
Because we will not be using our standard DR method (involves the use of the backup exec SQL agent) I was curious how SQL would behave after a typical system restore.
Since SQL services will not be able to start, I'm thinking the easiest task will be to reinstall SQL server on the DR'd box and use SQL scripts to restore all DB's including master and MSDB.
Our policy is that a non-technical person should be able to do the restores based on our policies and instructions. You never know how many of your I.T. people may be involved in the disaster...
Thanks for all the responses.
-
Our policy is that a non-technical person should be able to do the restores based on our policies and instructions. You never know how many of your I.T. people may be involved in the disaster...
What is the definition of "non-technical person"?
Trying to write out and script the steps to (re-)build a box from the ground up and then get SQL up and running is really nuts. I think the expecting Sue the secretary to do it is unreasonable
; as well the sub-CIO who is fast-tracking through management positions on his way up the corporate ladder (i.e doesn't know what a DOS prompt is. ).If they expect you to script to that level of detail they need to step back and take a realistic look at the DR plan. They may need to consider a consulting/contract for DR that brings in at least a junior level DBA/Network person who is familiar with install and how SQL works.
I'm just throwing out my $0.02.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply