Solved

Backup & shrink transaction log for all user defined databases

Posted on 2012-04-04
11
325 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

827 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