April 3, 2009 at 9:38 pm
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???
April 3, 2009 at 9:53 pm
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.
April 4, 2009 at 2:44 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2010 at 12:17 pm
sql server doesn't allows the transaction log backup when the recovery model of the DataBase is in SIMPLE Recovery model:-)
April 9, 2012 at 2:25 am
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.;-)
April 9, 2012 at 3:07 am
Please note: 3 year old thread.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply