Please help...Backup vs Transaction log vs Restore is so important 
Author Message
 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



Sat, 30 Nov 2002 03:00:00 GMT
 Please help...Backup vs Transaction log vs Restore is so important

Eric,

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.


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

I agree.
Quote:

> 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




Tue, 03 Dec 2002 03:00:00 GMT
 Please help...Backup vs Transaction log vs Restore is so important
Hi Tibor,

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.



Tue, 03 Dec 2002 03:00:00 GMT
 Please help...Backup vs Transaction log vs Restore is so important
Eric,

Quote:
>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 ????
**No**

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

Quote:
>> This way to your good health! <<

http://members.xoom.com/agedashi/


Thu, 05 Dec 2002 03:00:00 GMT
 Please help...Backup vs Transaction log vs Restore is so important
Sorry to come in here but
If you back up the tran log every hour and have a crash at 1/2 past
the hour.   Try to get to your current non backed up tran log and back
it up then you will have o problems

ALlan

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

Quote:

>Eric,

>>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 ????
>**No**

>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

>>> This way to your good health! <<
>http://members.xoom.com/agedashi/



Thu, 05 Dec 2002 03:00:00 GMT
 Please help...Backup vs Transaction log vs Restore is so important
Eric,

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.


Quote:
> Hi Tibor,

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

> Thanks

> Eric Perron

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



Fri, 06 Dec 2002 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

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

2. DTS vs sp_detachdb vs BACKUP/Restore

3. Transaction Log vs restore at a specific point in time

4. DB Restore and Trans Log vs.Differential Backup question

5. Transaction Log backup Error. Please help

6. Applying Transaction Log Backups, Help please !!

7. Transaction Log backup help please.

8. Truncate log on Checkpoint vs. Truncate Transaction Log

9. HELP Paradox vs Access vs Foxpro vs ????????????

10. Access vs Approach vs Paradox vs FilePro vs Filemaker Pro

11. Transaction log backups and restores

12. Restore Database from Backup without Transaction Log?


 
Powered by phpBB® Forum Software