I do not remember where I picked up the parts to this, but have been using it for some time. It is a simple procedure that I have a scheduled job setup to backup all my databases ever three days and truncate all the logs. Notice, there is a hard coded path in the procedure where it stores the backups which you will have to change.
CREATE PROCEDURE BackupAllDatabases AS
SET QUOTED_IDENTIFIER off
select getdate() 'Start Time'
set nocount on
declare @dbname varchar(36),@cmd varchar(255),@logname varchar(2048)
declare dbname_cursor cursor
for select d.name, rtrim(f.name)
from master..sysdatabases d left join master..sysaltfiles f
on d.dbid = f.dbid and f.groupid = 0
where d.name != 'tempdb'
order by d.name
open dbname_cursor
fetch dbname_cursor into @dbname, @logname
while @@fetch_status = 0
begin
if DATABASEPROPERTYEX(@dbname,'Status') = 'ONLINE'
begin
select @cmd ='use ['+@dbname+'] backup database ['+@dbname+'] to DISK=''c:\MSSql\Backups\'+@dbname+'-'+convert(varchar(50),GetDate(),106)+'.bak'' with init'
print @cmd
execute (@cmd)
select @cmd ='use ['+@dbname+'] BACKUP LOG ['+@dbname+'] WITH TRUNCATE_ONLY'
print @cmd
execute (@cmd)
select @cmd ='use ['+@dbname+'] DBCC SHRINKFILE(['+@logname+'] , 255)'
print @cmd
execute (@cmd)
end
fetch dbname_cursor into @dbname, @logname
end
close dbname_cursor
deallocate dbname_cursor
select getdate() "End Time"
USE AT YOUR OWN RISK ....