Please help with Backup and Restore 
Author Message
 Please help with Backup and Restore

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



Sun, 01 Dec 2002 03:00:00 GMT
 Please help with Backup and Restore

Quote:
> 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

DUMP TRAN is the normal method to archive log data.  This also removes the
committed transactions

DUMP TRAN WITH TRUNCATE-ONLY and DUMP TRAN WITH NO_LOG do not archive log
data; these commands only remove committed transactions from the log and are
issued to address a full log situation.  Once you have issued either of
these commands, you won't be able to run a regular DUMP TRAN until you dump
the database.  This is because the sequence of log dumps has been broken and
subsequent log dumps are useless.

Whether or not you specify INIT depends on how you want manage your media.
If you are dumping directly to tape, you would usually specify INIT for the
first tape dump (to erase the tape) and NOINIT for the subsequent dumps (to
append to the tape).  Of course, you'll need to change the tape before the
INIT dump.

A couple of additional considerations:  Be sure your log is large enough so
it does not fill up between dumps.  Make sure you get notified when
transaction dumps fail because the log will otherwise eventually fill up.
Be sure to regularly test your recovery procedure.

Hope this helps.



Mon, 02 Dec 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Please help...Backup vs Transaction log vs Restore is so important

2. Please help...Backup vs Transaction log vs Restore is so important

3. Help please, restore a database backup problem!

4. please help me vb+sqlserver+backup&restore.

5. newbie question - backup/restore - please advise

6. COMBO BOX, PLEASE HELP, PLEASE HELP, PLEASE HELP!

7. MSSQL BACKUPS - Deletion (update the backup drop down box, in the restore function)

8. HELP: Unicode problems while Restoring from Backup

9. Backup restore help

10. Need help with a problem regarding Backup and Restore for Standby Server

11. Backup restore to another SQL server HELP!


 
Powered by phpBB® Forum Software