Solved

Backup & shrink transaction log for all user defined databases

Posted on 2012-04-04
11
324 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
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:ewangoya
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 200 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 100 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
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…
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.

778 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