Full or Simple mode SQL backup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Full or Simple mode SQL backup

Full or Simple mode SQL backup

Rate Topic

Display Mode

Topic Options

Author

Message

M.Kahn

Posted Thursday, June 09, 2011 2:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 5:51 AM
Points: 46, Visits: 353

Hi,

I have a couple of question regarding sql 2005/2008 RC backup's mode. New job and new rules here
SQL backup's have 3 mode, Full, Differential and Tlog backup. All db's are in simple mode backup here. My questioin is, why should i use full mode not simple mode, in term of technolog accepts ? advantage or dis-advantage.

Log error:
Date 2011-06-08 16:28:09
Log SQL Server (Current - 2011-06-09 01:01:00)

Source Backup

Message
Database backed up. Database: LAX2, creation date(time): 2010/09/16(09:20:14), pages dumped: 219, first LSN: 156:214:37, last LSN: 156:230:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#a0e850c8-4e2f-4b4d-b470-4db5ad89f2c6'}). This is an informational message only. No user action is required.

Please, let me know and thnx

Post #1122277

GilaMonster

Posted Thursday, June 09, 2011 2:59 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

Please read through this - Managing Transaction Logs

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 #1122290

muthukkumaran Kaliyamoorthy

Posted Thursday, June 09, 2011 4:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123, Visits: 4,423

Kahn

Gail already given the log file management.

Also,read the post which will help you to understand the backups and models advantage and disadvantage.

SQL server Recovery Models and Backup types

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Post #1122338

M.Kahn

Posted Thursday, June 09, 2011 7:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 5:51 AM
Points: 46, Visits: 353

Thanks to all.

Special Thanks to Gail Shaw (Gail is best dude)

Post #1122522

AlexGreen

Posted Friday, June 17, 2011 8:49 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 8:56 AM
Points: 17, Visits: 40

SQL Server has three recovery models:
Simple: This model minimally logs most transactions.
Full: logs all transactions an retains all the transaction log records until after they're backed up.
Bulk-Logged: minimally logs bulk operations, such as index creation, bulk inserts, etc.

How you combine any of these models with full or differential backups will determine how precise your backup could be.

Post #1127425

GilaMonster

Posted Friday, June 17, 2011 8:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

AlexGreen (6/17/2011)

SQL Server has three recovery models:
Simple: This model minimally logs most transactions.

Simple minimally logs the same operations that bulk-logged minimally logs. The difference is that log records are inactive after a checkpoint, not a log backup

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 #1127430

mr.jeff.freedman

Posted Saturday, January 26, 2013 7:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 6:43 AM
Points: 5, Visits: 13

I got some help from this post and discussion. Thank you.

Post #1412029

« Prev Topic | Next Topic »

Permissions