TSM Backup

How to exlude files from the tsm backup : This is code to exlude files from the tsm backup.

To enable this we need to to the following:

– Locate the tsm.opt file, usualy it is here: D:\tivoli\tsm\TDPSql
– add this line to the file, INCLEXCL D:\tivoli\tsm\TDPSql\exclude.txt
– Edit the code below, different code for sql 2000 and sql 2005. Note, if the path is not the same as above, you need to change the path acordingly. Make sure that xp_commandshell is enabled (Is done via sp_configure)
– Schedule this code in a job to run once every day. e.g 5 in the morning. The code will overwrite the file every time it is running so if any databses is added or removed, the new file will be correct.

Note this is code for log backups. If there is an need for full backups, you need to use an alternative code. I am working on that code right now.

– code for sql 2005
Declare @path as varchar(100)
set @path =’c:\file.txt’
declare @string as varchar(100)
set @string = ‘echo off > ‘+ @path
exec master..xp_cmdshell @string
declare Backup_cursor cursor fast_forward
for
select [name] from sys.databases where database_id >4 and recovery_model1
or is_read_only 0 or state_desc ’online’
open Backup_cursor
declare @dbname sysname
Fetch next from Backup_cursor into @dbname
while (@@fetch_status -1)
begin
set @string = ‘echo ‘+ ‘Includexcl exclude \…\’+@dbname+’\…\log >> ‘+@path
exec master..xp_cmdshell @string
fetch next from Backup_cursor into @dbname
end
close Backup_cursor
deallocate Backup_cursor
print @string
–sql 2000

Declare @path as varchar(100)
set @path =’D:\tivoli\tsm\TDPSql\exclude.txt’
declare @string as varchar(100)
set @string = ‘echo off > ‘+ @path
exec master..xp_cmdshell @string
declare Backup_cursor cursor fast_forward
for
select [name] from sysdatabases
where dbid >4 and convert(sysname,DatabasePropertyEx([name],’Recovery’)) ‘FULL’
or convert(sysname,DatabasePropertyEx([name],’Status’)) ’ONLINE’
or convert(sysname,DatabasePropertyEx([name],’Updateability’)) ’READ_WRITE’
open Backup_cursor
declare @dbname sysname
Fetch next from Backup_cursor into @dbname
while (@@fetch_status -1)
begin
set @string = ‘echo ‘+ ‘exclude “\…\’+@dbname+’\…\log*” >> ‘+@path
exec master..xp_cmdshell @string
fetch next from Backup_cursor into @dbname
end
close Backup_cursor
deallocate Backup_cursor
print @string

Litespeed directly to TSM code

Example script for taking tsm backups with litespeed

master.dbo.xp_backup_database @database = [Testdb],
@tsmconfigfile = ‘d:\Program Files\Tivoli\TSM\TDPSql\dsm.opt’,
@tsmobject = ‘SDWPCE1D_EDH\CONTROLS\CONTROLS-082207-000000-0-FUL’,
@desc = ‘Some description’,
@backupname = ‘testdb FUL’,
@with = SKIP,
@compressionlevel = 2,
@init = 1,
@OLRMAP = 1
Backup logs with litespeed : This code shoudl be used with all lite speed backups ( log backups) It works for sql 2005 and take cares of databses in offline mode, read only and simple mode/bulk load mode. It does create the folders if it is not there and compressed the file with an medium compression of 5.

declare @retcode int
declare @cmd varchar(150)
declare Backup_cursor cursor fast_forward
for
select [name] from sys.databases where database_id >4 and recovery_model=1
and is_read_only =0 and state_desc =’online’
open Backup_cursor
declare @dbname sysname
Declare @Path varchar(100)
set @path =’\\101.000.000.000\backupshare\3\Log\’
DECLARE @SQLString NVARCHAR(500)
Declare @today varchar(10)
set @today =CONVERT ( varchar(50) , getdate() ,121 )
Fetch next from Backup_cursor into @dbname
while (@@fetch_status -1)
begin
set @cmd = ‘md ‘ + @path + @dbname
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SET @SQLString = N’master.dbo.xp_backup_log @database= ”’ + @dbname + ”’ ,@filename=”’ + @path +@dbname +’\’+ @dbname +’_’+ @today + ‘.bak”’ +’ , @init= 1 ,@compressionlevel =5 ‘
Exec (@SQLString)
fetch next from Backup_cursor into @dbname
end
close Backup_cursor
deallocate Backup_cursor

Från Khan sql dba – mcitp www.addarr.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: