Solved

Backup & shrink transaction log for all user defined databases

Posted on 2012-04-04
11
322 Views
Last Modified: 2012-04-14
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
0
Comment
Question by:sthay
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 200 total points
Comment Utility
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
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
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
 
LVL 15

Assisted Solution

by:Anuj
Anuj earned 200 total points
Comment Utility
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
 
LVL 25

Expert Comment

by:jogos
Comment Utility
<< 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
 
LVL 15

Expert Comment

by:Anuj
Comment Utility
@jogos, make sense :-)
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:sthay
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 100 total points
Comment Utility
<<. 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
 

Author Comment

by:sthay
Comment Utility
dear experts, thanks for all the comments you made.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
You are welcome.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now