Setting up SQL Server backups with Tivoli TDP agent

INSTALLATION

For SQL Server 2000 => TDPSQL 5.2.1 agent
For SQL Server 2005 => TDPSQL 5.2.1 + patch 3
Patch 3 is required to avoid ACO5423E error

For 2000 file, you will need to extract it manually onto c:\TEMP, then find the appropriate SETUP.exe, run it and follow the instructions. For 2005 file, it will do that automatically.

PDF file in the same directory contains all further details about the installation and configuration.

CONFIGURATION

Ask Tivoli administrator to register TDP for SQL as a client node within the TSM server.
Set up the option and configuration file

TESTING
tdpsqlc query TSM
If this command fails, then connection to the Tivoli Manager is not set up properly.
tdpsqlc query TDP
This command just checks whether your configuration file is set properly.
tdpsqlc query SQL *
The agent will try to connect to the databases.

RUNNING THE BACKUP
TDPSQLC Backup *|dbname[,dbname,...] [Full/Difffull/Log]
/DIFFESTimate=10 -- to estimate the size of changed blocks to 10 percent
/BUFFers=128 -- this will put the size of buffer to 128Kb. The buffer is used to transfer the data between TDP and TSM

We have been running a differential backup on 50G database. It takes two minutes to finish it as most of our data are static. The full backup takes 1 hour.

EXCLUDING THE DATABASE FROM THE BACKUP

To exclude the database from the Tivoli backup, for example you can add this command to your option file:
EXCLUDE.BACKUP "\...\DBEMPL\...\*"

If you want to exclude just for certain backups, create a separate option file, for example opt_small_databases_bakup.opt
and then run your backup command:
"C:\Program Files\Tivoli\TSM\TDPSql\tdpsqlc" backup * full /TSMOPTFile="C:\Program Files\Tivoli\TSM\TDPSql\dsm_small_databases_bakup.opt"

This will backup up all database except for DBEMPL.

SCHEDULING THE BACKUP
It can be done either via TSM or as a SQL Server job

RESTORE
TDPSQLC Restore *|dbname[,dbname,...] [Full/Difffull]|[FIle/Log=*|logicalname[,logicalname,...]] [parameters]
The relevant parameters are
For the restore:
/DBOonly - prevents general users from accessing a restored database.
/STOPAT - restores a SQL database to a specified point in time.

DOCUMENTATION
http://publib.boulder.ibm.com/infocenter/tivihelp/v1r1/index.jsp?topic=/com.ibm.itsmfd.doc/ab5m000171.htm

Various useful small SQLs

Find the edition

select serverproperty('Edition')

Who are you connected as?

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

Show current time

select getdate()

Show current server

select @@servername

Show current version

select @@version

or even better:

xp_msver

Google the results to see what numbers mean

Find out current service pack

SELECT SERVERPROPERTY('ProductLevel')

Sizing

EXEC sp_msforeachdb @command1="use [?] exec sp_spaceused"

EXEC sp_spaceused

EXEC sp_spaceused table_name

List Backups in the last 30 days

SELECT s.database_name, S.backup_start_date, S.type,
M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE DATEDIFF(DAY,S.backup_start_date,GETDATE()) <>

Auto close and auto shrink

SET NOCOUNT ON

SELECT [name] AS DatabaseName^?
, CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) AS RecoveryType^?
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose^?
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink^?
FROM master.dbo.sysdatabases Order By DatabaseName^?

To create the shink log commands for ALL databases in FULL recovery mode:

SELECT ' use '+name +
char(10) + char(13)+ 'go ' + char(10) + char(13)+
'dbcc shrinkfile ('+name+',0,truncateonly)'+
char(10) + char(13)+ 'go ' + char(10) + char(13)+
FROM master.dbo.sysdatabases
where CONVERT(varchar(100),DATABASEPROPERTYEX([Name],'Recovery'))='FULL'

Check the last statistics update

SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(o.id,i.id)
FROM sysobjects AS o WITH (NOLOCK)
JOIN sysindexes AS i WITH (NOLOCK)
ON object_id(o.name) = i.id
ORDER BY STATS_DATE(o.id, i.id) desc

Check the instance parameters

select * from sys.configurations order by name

List database objects

SELECT * FROM sysobjects WHERE TYPE = 'P'

SELECT name, type FROM sysobjects WHERE id={number}

List all Tables

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

ORDERBY TABLE_SCHEMA, TABLE_NAME

Execute SQL command for each table

EXEC sp_MSForEachTable 'ALTER TABLE ? ETC ETC'

To release the deallocated space

DBCC UPDATEUSAGE ('dbname')

Fix orphaned users

exec sp_change_users_login 'Report'

CREATE LOGIN [abclogin] WITH PASSWORD=N'passw0rd', DEFAULT_DATABASE=[DBname], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE DBName

GO

sp_change_users_login 'update_one', 'youruser', 'abclogin

Log Shipping

select * from log_shipping_monitor_primary

select * from log_shipping_monitor_secondary

select * from log_shipping_monitor_history_detail