Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

backup Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 8:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, 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
Post #1465214
Posted Wednesday, June 19, 2013 8:39 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:01 AM
Points: 711, Visits: 2,210
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
Post #1465217
Posted Wednesday, June 19, 2013 8:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, 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
Post #1465227
Posted Wednesday, June 19, 2013 8:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
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/
Post #1465235
Posted Wednesday, June 19, 2013 8:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
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

Post #1465238
Posted Wednesday, June 19, 2013 10:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:47 AM
Points: 53, Visits: 292
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
Post #1465275
Posted Wednesday, June 19, 2013 1:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
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)
Post #1465359
Posted Wednesday, June 19, 2013 9:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:47 AM
Points: 53, Visits: 292
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.
Post #1465445
Posted Wednesday, June 19, 2013 11:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, 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


  Post Attachments 
sql backup comparision.pdf (6 views, 5.57 KB)
Post #1465459
Posted Wednesday, June 19, 2013 11:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:47 AM
Points: 53, Visits: 292
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]
Post #1465463
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse