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

Can we take a transaction log backup in simple recovery model? Expand / Collapse
Author
Message
Posted Friday, April 03, 2009 9:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 11, 2013 4:51 AM
Points: 12, Visits: 43
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
Posted Friday, April 03, 2009 9:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Saturday, April 04, 2009 2:44 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
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
Posted Monday, July 05, 2010 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #947609
Posted Monday, April 09, 2012 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:37 AM
Points: 1, Visits: 115
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.
Post #1280054
Posted Monday, April 09, 2012 3:07 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
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 »

Add to briefcase

Permissions Expand / Collapse