
Please help...Backup vs Transaction log vs Restore is so important
Too often, people installs servers and do not get concerned about backup and
restore. But when the time comes to a crash, they get confused and they do
not know how to have their server and data back to work. I think, this is
the most important part of the administrative process.
If you think I am right, please give me a little help. If it can help
someone else, I gonna be glad.
I just read for a hundred times, the backup section of the BOL and another
book, but there is still someting that I do not understand at 100%
What is the BEST way to backup data on a SQL 6.5
What is the BEST sequence to backup my data (I think it is this but if it is
not, please tell me)
MASTER first
MSDB second
MYDB third
And does the restore should be done in a particular sequence. (MASTER vs
MSDB vs MYDB)
My own worst ennemy is the transaction log. (I think if I really understand
how it works and the way I can use il to protect the data integrity, it
gonna be one of my best friend)
(Can someone tell me if I do it correctly or if I do too much backup.
Any idea will be accepted)
This is what I plan to do.
5h00 am Full Backup of MYDB (this is the end of the production setup. The
production will restart at 7h00am)
According to the books, the full backup contains the
transaction log backup.
From there, a DUMP TRANSACTION every hours (to give me
the opportunity to restore at a specific point in time, without loosing to
much work in the case of a crash. The worst thing that can happen is the
lost of 1 hour) and the last DUMP TRANSACTION will be at 4H59am, just
before the DUMP DATABASE.
Wich one should I use (This is where I get confused) Which one is
normally used ??????
DUMP TRAN WITH TRUNCATE-ONLY
DUMP TRAN WITH NO-LOG
DUMP TRAN
INIT or NOT
Should I use the TRUNCATE ON CHECKPOINT or NOT
Why does the MSDB as the TRUNCATE ON CHECKPOINT set to ON (With that, I
cannot perform a DUMP TRANSACTION)
Any help or advice will be accepted.
Thanks for the help
Eric Perron