Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Backup & shrink transaction log for all user defined databases

Posted on 2012-04-04
11
Medium Priority
?
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 400 total points
ID: 37809722
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:Ephraim Wangoya
ID: 37809729
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 400 total points
ID: 37809774
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:jogos
ID: 37809834
<< 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
ID: 37809841
@jogos, make sense :-)
0
 

Author Comment

by:sthay
ID: 37822630
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
ID: 37823319
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
ID: 37823328
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 200 total points
ID: 37826998
<<. 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
ID: 37845818
dear experts, thanks for all the comments you made.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37846424
You are welcome.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 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