Can we take a transaction log backup in simple recovery model?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Can we take a transaction log backup in...

Can we take a transaction log backup in simple recovery model?

Rate Topic

Display Mode

Topic Options

Author

Message

randhirdadwal

Posted Friday, April 03, 2009 9:38 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:29 AM
Points: 12, Visits: 40

Can we take a transaction log backup in simple recovery model?

I have tried doing that and I was able to take a transactional log backup only using the option with with truncate only. It shows command completred successfully in management studio, however when I checked the physical location of log back it did not show any file. Any ideas???

Post #690339

KenSimmons

Posted Friday, April 03, 2009 9:53 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:07 AM
Points: 1,221, Visits: 2,614

Transaction Log backups are only supported using the Full and Bulk-Logged recovery models because the Simple recovery model truncates the transaction log on checkpoint (when the data is written to disk) and marks the space as available for reuse.

Ken Simmons
http://twitter.com/KenSimmons

Post #690348

GilaMonster

Posted Saturday, April 04, 2009 2:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 38,028, Visits: 30,334

randhirdadwal (4/3/2009)

I have tried doing that and I was able to take a transactional log backup only using the option with with truncate only.

Backup log with truncate only means truncate the log and don't write a backup file out.
No matter what recovery model the DB is in, that command will never write a backup file out and, on full/bulk logged, it will break the recovery chain preventing further log backups until a full backup is taken.

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

riyazuddin_md

Posted Monday, July 05, 2010 12:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 07, 2010 11:11 AM
Points: 1, Visits: 3

sql server doesn't allows the transaction log backup when the recovery model of the DataBase is in SIMPLE Recovery model Smile

Post #947609

bhupendrakumar2004sharma

Posted Monday, April 09, 2012 2:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:08 PM
Points: 1, Visits: 105

in simple recovery model logs are truncated automatically by SQl server. so logs does not exits on log files,this is the reason we cannot take log backup in the simple recovery model.. we can only backed up log file whether in full recovery model or bulked-log model. Wink

Post #1280054

GilaMonster

Posted Monday, April 09, 2012 3:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 38,028, Visits: 30,334

Please note: 3 year old thread.

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

« Prev Topic | Next Topic »

Permissions