Backup & shrink transaction log for all user defined databases

Dear Expert

I am trying to write T-SQL script that woul do:

1. backup all user defined databases
2. shrink transaction log for each user defined database

The script does give me lots of compile time error due to syntax errors and others.

I have attached a script herewith and your advice is highly appreciated.

Thanks in advance.
Sot
BackupAndShrink.txt
sthayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:
First, its not always a good idea to shrink your log files regularly. To manage their size, you can set up regular transaction log backups or set your recovery model to SIMPLE

You can simply set the backups from Sql Server Management Studio (SSMS), you already have the option to select user databases only
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ephraim WangoyaCommented:
To reiterate, get rid of the shrink code if you still want more control with your own script. Make it very simple


--This T-SQL does:
--1. backup all user defined databases

declare @name varchar(100) -- database name
declare @path varchar(25) -- path for backup files
declare @filename varchar(255) -- filename for backup
declare @filedate varchar(25) -- used for file name

set @path = 'd:\backup\'

--filedate holds date and time
select @filedate = convert(varchar(20), getdate(), 112) + '_' + replace(convert(varchar(20), getdate(), 108), ':', '')

declare db_cursor cursor for

	select name from master.dbo.sysdatabases where name not in ('maser', 'model', 'msdb', 'tempdb')


open db_cursor

fetch next from db_cursor into @name

while @@fetch_status = 0

begin
    
	set @filename = @path +@name + '_' +@filedate + '.bak'

	--full backup of the database
	backup database @name to disk = @filename with init, compression
	
	fetch next from db_cursor into @name
end

close db_cursor
deallocate db_cursor

Open in new window

0
AnujSQL Server DBACommented:
I agree with the above points, size your log file accordingly monitor the log growth for couple of days or peek business hours, and size it. Once you size this schedule regular transactional log backups this will allows to truncate the inactive portion of your log file automatically( only in sql server 2008) this makes your log file compact in size, so you don't need to shrink your file.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jogosCommented:
<< monitor the log growth for couple of days or peek business hours,>>
Make it weeks, there is probably some week or monthly action that is a larger consumer of log-space.

To keep logfiles small it's better to decrease the interval between the transactionlog backups.
0
AnujSQL Server DBACommented:
@jogos, make sense :-)
0
sthayAuthor Commented:
Dear ewangoya et al

Thanks for your comments.

My goal is to stop the transaction log file from growing too big. If shrink database/file is not recommended, can I truncate the transaction log simply switching from full recovery to simple recovery and then switch it back to full recovery model like below code?

Thanks in advance.
Sot
PS. I dont need to backup a transaction log as we dont need in time recovery anyways

--
begin
   
      set @filename = @path +@name + '_' +@filedate + '.bak'

      --full backup of the database
      backup database @name to disk = @filename with init, compression

      --set recovery to simple to truncate a transaction log  
      alter database @name
      set recovery simple;
      go
      
      --set recovery to full  
      alter database @name
      set recovery full;
      go

      
      fetch next from db_cursor into @name
end
--
0
Anthony PerkinsCommented:
can I truncate the transaction log simply switching from full recovery to simple recovery and then switch it back to full recovery model like below code?
That is pointless.  If you do that than you will need to run a Full-Backup after changing back to Full-Recovery.
0
Anthony PerkinsCommented:
But if you are not prepared to adequately maintain a database in Full-Recovery Model by doing frequent Transaction Log backups, than you need to switch to Simple and explain to all your users that they will risk losing all work from the last successful backup.
0
jogosCommented:
<<. I dont need to backup a transaction log as we dont need in time recovery anyways>>
That's confirming what Acperkins says in previous commpent. So change recovery model permanently to SIMPLE and include in your monitoring the fact that if any database has a recovery model FULL (or bulk-logged) it raises an error (or other notification).
0
sthayAuthor Commented:
dear experts, thanks for all the comments you made.
0
Anthony PerkinsCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.