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

Some thoughts below. Please post back (here) for further elaboration, if needed.

Here's what I generally do:

Db Backup once a day (at night time).
Log backup hourly.

Do _not_ use TRUNCATE_ONLY or NO_LOG at log backup, since they only truncates the log
(without backup). Visualize these as if you did a backup to the filename "nul".
Do _not_ set "truncate log on checkpoint", since that truncates the log (without
backup) regularly.

I generally backup master one per day (at the "night" run).

Why is trnc log on checkpoint set for MSDB? Well, I guess that this is so the db
doesn't become full quite of a sudden. If you know how to reset the dboption, and how
to backup the log - go ahead. And then run backup for msdb "every hour" instead of
"every day".

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.

You say that you do a full DB backup at night and a log backup hourly.

Does it means that if a crash occurs at 14h59, you lost 1 hour of collecting data. (If so, I cannot do that because I do not want to loose any data)  Should I backup my log every minutes or what ????

Thanks

Eric Perron

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for web developers.

I would suggest that you mirror the LOG. So if the original LOG volume
is toast; you can still restore from the mirror like this:

(for simplicity sake; let's suppose that the database crashed at 0059 -
which is
59 minutes since we did a full backup on 0000)

-- Restore full backup.
RESTORE DATABASE mydb
FROM mydb_backupdevice
WITH NORECOVERY
GO

-- Restore last unfinished log backup.
RESTORE LOG mydb_log
FROM mydb_log_mirror
WITH RECOVERY, STOPAT = 'June 19, 2000 00:59 AM'
GO

--
Regards,

Gavin Bong

ALlan

On Sun, 18 Jun 2000 14:24:21 +0800, "Gavin Bong"

Along the lines of the other suggestions:

If you have a crash, and you still have the log file(s) available, you can still
backup the log. And then restore up to just before the crash (NO_TRUNCATE). This
requires that the .mdf file is available in version 7.

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.