SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


backup


backup

Author
Message
Krishna1
Krishna1
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 556
Dear all

If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.
(I do not take transaction log back up at all.)
Will these recovery models give same results or am I missing some thing.
Note that I donot want to restore to particular point of time. Only want to restore to last back up.
Lots of info availsble on net but for my situation I feel recovery model does not matter. Correct me if i am wrong.
Also want to check if for recovery model full if i take only full back up every day will the transaction log keep on growing or it will truncate when full bavkup taken?

Thanks and regsrds
s_osborne2
s_osborne2
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 2292
Hi,

If your recovery model is full and you don't take a log backup then your log file will fill the drive it is sat on. If you don't care about point in time recovery use the simple recovery model.

Thanks,


Simon



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Krishna1
Krishna1
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 556
Thanks for quick reply. How do I test this. I need to show the diff that log will not get trucated even if we take full back every day. Can u tell so.e was to demonstrate the difference between a imple and full recovery model
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6054 Visits: 5280
Krishna1 (6/19/2013)
Thanks for quick reply. How do I test this. I need to show the diff that log will not get trucated even if we take full back every day. Can u tell so.e was to demonstrate the difference between a imple and full recovery model


It is easy to test this

Follow the below steps:
1. Set the recovery model as FULL
2. Create a table in the database
3. Insert lots of data in the table and check the log size
4. Take a FULL backup
5. Repeat steps 3 & 4 as many times as you want

Note down the log size after every iteration and you will find that the log size keeps on increasing.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226540 Visits: 46328
Krishna1 (6/19/2013)
How do I test this. I need to show the diff that log will not get trucated even if we take full back every day.


Create a database, set it to full recovery. Schedule full backups. Run data modifications (create, populate and drop a table if you have nothing else). Wait for the full drive error.

Please read through this: http://www.sqlservercentral.com/articles/Administration/64582/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


balasaukri
balasaukri
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 301
If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.
(I do not take transaction log back up at all.)
Will these recovery models give same results or am I missing some thing.


---- Taking Full Backup on the database which is in either Full or Simple or Bulk-logged recovery model will give the same result only and there won't be any difference.

Thanks
Bala
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3269 Visits: 6497
balasaukri (6/19/2013)
If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.
(I do not take transaction log back up at all.)
Will these recovery models give same results or am I missing some thing.


---- Taking Full Backup on the database which is in either Full or Simple or Bulk-logged recovery model will give the same result only and there won't be any difference.

Thanks
Bala


Sorry, but that's wrong. The recovery model setting has no influence on when and whether the log files get "cleared". Just follow Gail's link a little further up for an explanation as to why.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
balasaukri
balasaukri
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 301
Aplogies if I didn't mean what I actually mean.

I mean to say the content (data) of backup file will be same. If a full backup is taken on a database with any type recovery model - the content (data) in the backup file would be the same.

And ofcourse Full backup will make certain effect on the Logfile - It won't mark log file for deletion (truncation) if a db in Full or Bulk Recovery model. It will take certain portion of Log file for sychronization.

Only Log backup will mark the log file for deletion (truncation) after the log backup except two situations - (i) When it is being using used with conjuction of NO_TRUNCATE or COPY_ONLY. (ii) When the log backup completes while Full/ Differential backup is still running. As the Full/ Differential backup requires certain portion of log backup it wont allow log backup to truncate.
Krishna1
Krishna1
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 556
Dear All

Thanks for your replies. I have done testing of .ldf file size using 2 newly created DB. One DB is simple recovery model and other Full recovery model.
Created following table in both DBs
create table a ( a1 char(1000) default 'a1',
a2 char(1000) default 'a2',
a3 char(1000) default 'a3',
a4 char(1000) default 'a4',
a5 char(1000) default 'a5',
a6 char(1000) default 'a6',
a7 char(1000) default 'a7' ,
i integer)

and used
insert into a (i) values (1)
go 20000

Please refer to the pdf file of my findings. From the PDF file i noticed that Full recovery model after transaction log back up us taken it resused the log space. But in the Simple recovery model its keeps on growing, which i know is not correct.
Let me know what is that i am doing wrong

Regards
Krishna1
Attachments
sql backup comparision.pdf (9 views, 5.00 KB)
balasaukri
balasaukri
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 301
Please post the output
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'your db name'
dbcc sqlperf(logspace) [for the two databases]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search