SQL Clarifications

Question

SQL Clarifications

Lester Policarpio

SSCertifiable

Points: 7109
More actions
September 18, 2007 at 6:32 pm

#70113

Just want to clarify some points thanks in advance
1. Simple Recovery model - Ignores transaction logs?.. and is an auto truncate recovery model.
2.Simple shifting to Full recovery (or vice versa) doesn't affect the database at all.
3. The auto shrink option of the database shrinks only the transaction log(ldf) and not the data(mdf)

if No. 1 is true (auto truncate) is there a percent in which the tlog is truncated? example when tlog is 80% thats the time it will truncate it.
What is the best way (time/schedule) of backups using full,differential and tlog. example weekly(1) full backup and hourly diff and tlog...etc...
"-=Still Learning=-"
Lester Policarpio

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Hi Lester,

1: Does not ignore it. It uses it in case the server goes down, so the database can be recovered if it goes down unexpectedly. But this means that not all the log is required for this, parts of the log that are not required are recycled. You cannot make a log backup. In Simple the log is automatically truncated.

2: well, it does affect the recovery granurality. In Simple you can only restore a full and differential backups, in full, you can use log backups as well. Note that when you change to full recovery mode, it is not in effect until you take a full backup.

3: No, it shrinks both.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

Hi Lester,

concerning the best way to schedule the backups, it depends on your requirements (whether you can live with loosing one hour/one day worth of data, ho fast do you need it to be restored, how much time the backup takes, ...

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 3

oopsss wrong way of quoting any ways thanks for the fast reply

"-=Still Learning=-"

Lester Policarpio

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

So for the full recovery to take effect (after shifting from simple to full) I need to make a full backup?

"-=Still Learning=-"

Lester Policarpio

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 5

Another thing sir about full,diff and tlog is there a detailed site about this? I mean a detailed steps for every choices?

example:

1. FOr fast recovery - <this steps>

2. FOr full recovery(doesnt loose evern 1 hour) - <this steps>

etc..

"-=Still Learning=-"

Lester Policarpio

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 6

If i choose to optimize the backups of my databases meaning i want to assure that i cant always make a point in time recovery what could be the best flow of the full, diff and tlog? can you site your own experiece about this?

"-=Still Learning=-"

Lester Policarpio

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

I assume you meant " ... that i CAN always ... "

There's no "best" answer. I do a full backup every night of my 280 G database (takes 1/2 hour), and I backup transaction logs every 15 minutes or so during the business day. You can use differentials if you need to, but I don't. Differentials are a convenience during a restore because you don't need to restore all the trans logs since the last full backup, just the transaction logs since the last differential.

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 8

Thanks for the correction homebrew01

"-=Still Learning=-"

Lester Policarpio

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 9

Lester Policarpio (9/18/2007)
So for the fullrecovery to take effect (after shifting from simple to full) I need to make a full backup?

Hi Lester,

this is correct. If you switch from simple to full, SQL Server will still recycle the transaction log, and will not allow you to back up the transaction log until you take a full backup. It is intelligent enough to realize that if you have no full backup, the transaction log backup makes no sense (you cannot restore it without a full backup). So after switching to full recovery mode do take a full backup.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

Lester Policarpio (9/18/2007)
Another thing sir about full,diff and tlog is there a detailed site about this? I mean a detailed steps for every choices?
example:
1. FOr fast recovery - <this steps>
2. FOr full recovery(doesnt loose evern1 hour) - <this steps>
etc..

A pretty good place is BOL (Books On Line)

Search for 'Selecting a Recovery Model' and go from there.

It will tell you the basics around each option, to get you started.

/Kenneth