Today I got this error while trying to shrink my SharePoint 2010 databases on my dev machine:

‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

I searched for the error and found different sites, e.g.:

• http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/
• http://www.brentozar.com/archive/2009/08/backup-log-with-truncate-only-in-sql-server-2008/
• http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/d0958b81-8cfb-4b2b-8b5a-8e50c835f920

But there was no complete script for “truncating” all of my databases  at once.

Here is my script:

[sourcecode language=”sql”]–TRUNCATE_LOG for all databases at once on SQL SERVER 2008 R2
–https://blog.kenaro.com/2011/08/28/truncate-shrink-log-files-of-databases-in-sql-server-2008-r2/

DECLARE c CURSOR FOR SELECT database_id, name, recovery_model_desc FROM sys.databases — WHERE name=’sharepoint_config’; 
DECLARE @dbname VARCHAR(1024); 
DECLARE @rmod VARCHAR(1024); 
DECLARE @id INT; 
DECLARE @lfile VARCHAR(1024);  

OPEN c;  

FETCH NEXT FROM c INTO @id, @dbname, @rmod; 

WHILE @@FETCH_STATUS = 0 
BEGIN
IF @rmod = ‘FULL’
BEGIN
SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
PRINT @lfile
EXEC(‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY SIMPLE’)
EXEC(‘USE [‘+@dbname+’]; DBCC SHRINKFILE([‘+@lfile+’], 1)’)
EXEC(‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY FULL ‘)
END ELSE
IF @rmod = ‘SIMPLE’
BEGIN
SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)  
PRINT @lfile
EXEC(‘USE [‘+@dbname+’]; DBCC SHRINKFILE([‘+@lfile+’], 1)’)
END
FETCH NEXT FROM c INTO @id, @dbname,@rmod; 
END;  

CLOSE c 
DEALLOCATE c  
[/sourcecode]

For me it works like expected.