Answered by:
truncate_only' is not a recognized BACKUP option

Question
-
Hello All,
I am able to run the below command in sql 2005 but in 2008 getting error : truncate_only' is not a recognized BACKUP option.
Back Up Log [DBName] with Truncate_Only
dbcc shrinkfile([logFileName])
Thanks in Advance
AshwinFriday, April 24, 2009 10:34 AM
Answers
-
No, it's not supported any more in sql 2008, if you want to remove log chain, try to switch to simple recovery mode.
Regards,
Andrian- Proposed as answer by Andrian Setiawan Friday, April 24, 2009 10:52 AM
- Marked as answer by Jonathan KehayiasMVP Monday, April 27, 2009 12:48 AM
Friday, April 24, 2009 10:52 AM
All replies
-
Hi,
Check this link might be helpful
http://sqlserverpedia.com/blog/sql-server-management/backup-log-with-truncate_only/
Rajesh Jonnalagadda http://www.ggktech.comFriday, April 24, 2009 10:39 AM -
it became deprecated because of 'misuse' in 2005 & removed in 2008. You can achieve the same result by sending the file to the NUL blackhole
BACKUP LOG [DBNAME] TO DISK='NUL'
Make sure that if you are taking transaction log backups this will break your chain of transaction logs & you will need to take a full backup otherwise any log backups taken after the TRUNCATE_ONLY (or the 2008 equivalent) are useless- Edited by Nick Kavadias Friday, April 24, 2009 11:06 AM more info
- Proposed as answer by Nick Kavadias Sunday, April 26, 2009 12:03 AM
Friday, April 24, 2009 10:40 AM -
Hello,
Go throught the link.
http://experiencing-sql-server-2008.blogspot.com/2008/02/sql-server-2008-truncating-transaction.html
Hope this might be help ful.
Thanks,
ShanthiFriday, April 24, 2009 10:40 AM -
No, it's not supported any more in sql 2008, if you want to remove log chain, try to switch to simple recovery mode.
Regards,
Andrian- Proposed as answer by Andrian Setiawan Friday, April 24, 2009 10:52 AM
- Marked as answer by Jonathan KehayiasMVP Monday, April 27, 2009 12:48 AM
Friday, April 24, 2009 10:52 AM -
Hey,
Thanks for your reply.
I have done this.
It worked by using
dbcc shrinkfile ([DBName])Friday, April 24, 2009 11:51 AM -
For shrinking log file in sql server 2008, first u have to change the recovery model of database to simple,shrink the log file and then change back to the previous recovery model. following is the codeUSE dbname;GO-- Truncate the log by changing the database recovery model to SIMPLE.ALTER DATABASE dbnameSET RECOVERY SIMPLE;GO-- Shrink the truncated log file to 1 MB.DBCC SHRINKFILE (2, 1); -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)GO-- Reset the database recovery model.ALTER DATABASE dbnameSET RECOVERY FULL;GO
- Proposed as answer by RTTAdmin Tuesday, March 22, 2011 10:32 AM
Tuesday, July 28, 2009 6:57 AM -
Amit, Thank You! I just recovered over 100GB!!!Saturday, September 18, 2010 2:35 PM
-
Awesome, exactly what I was looking for. 100 gig here too.Monday, October 4, 2010 7:10 PM
-
it became deprecated because of 'misuse' in 2005 & removed in 2008
Now that's typical Microsoft..."We understand your needs better than you do"
Friday, January 21, 2011 9:11 PM -
Thanks for sharing right information. It helped us alot.Sunday, February 6, 2011 7:16 AM
-
Thanks, very useful information!
Sunday, April 3, 2011 7:09 PM -
I'm using SQL 2008 to build a BIDS package to migrate database objects from one database to another. The SSIS package is throwing this error that this TRUNCATE_ONLY option no longer exists. It's nice that Microsoft took away the function, but then they still call it in the Integration Services Looking for a workaround. The two databases I'm trying to transfer are both set to simple recovery.
Friday, April 15, 2011 4:19 PM -
When you take log backup with Truncate_only.
The inactive portion becomes reuse and breaks the LSN chain. And it will not generate any backup file on the disk.
After you perform Truncate_only immedietly you should perform full backup.
Saturday, April 16, 2011 1:18 PM -
When you take log backup with Truncate_only.
The inactive portion becomes reuse and breaks the LSN chain. And it will not generate any backup file on the disk.
After you perform Truncate_only immedietly you should perform full backup.
The thing is Microsoft has removed the commands in SQL server 2008 onwards.Read this post you may get it http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/
Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!
- Edited by muthukkumaran kaliyamoorthy Sunday, October 16, 2011 6:19 PM Fix url
Monday, April 18, 2011 10:20 AM -
Just switch to simple then switch back to full, then you can shrink the log file already
Thursday, April 21, 2011 1:10 PM -
-
I struggled with this doing it manually using the shrink Files option. Some DBs would, some wouldn't. I found the problem seemed to be the intial size.
Right click on the DB > Properties > files and I was able to change the inital size which in my case for some reason was 79. I changed it to 1.
Kim
Tuesday, May 7, 2013 2:04 AM -
Thank you, it works perfect!!!!
Monday, April 4, 2016 3:46 PM