August 22, 2008 at 5:13 am
We perform a database and transaction log backup on all our databases twice a day. Nowhere in the standard script is there the command to truncate the log.
Our Infrastructure team have now installed the latest version of IBM Tivoli Storage Manager for disaster management. Their consultant then informed me that he had set up a backup of the transaction logs on the hour and would I please amend my backup script to stop truncating the log. I have now got him to stop this TSM transaction log backup until I have the resources to investigate fully.
Has anyone got a TSM and knows what this consultant is referring to? We do not have TRUNCATE in the script.
Can anyone help?
:sick:
Madame Artois
August 22, 2008 at 6:38 am
Backing up the transaction log effectively truncates it.
TSM can backup the .BAK and/or TRN files generated by maintenance plans or your own scripts. But it can also backup the database/transaction log directly via SQL Server. I suspect that's what TSM is doing, so your consultant wants you to stop backing up the database/transaction log at all.
If TSM is backing up directly via SQL Server, then backup using your scripts or TSM, not both!
To recover the database you need an unbroken chain of transaction log backups since the last full backup.
Therefore, if you continue to backup the transaction log with your scripts alongside TSM, then to recover the database, you are going to have to restore the transactions logs from both your backup and TSM, all in the correct sequence!!
August 22, 2008 at 7:09 am
TSM is not backing up the transaction logs at the moment; he wanted to turn it on. I protested that I hadn't been consulted and hadn't had time to investigate so it didn't get turned on.
The current backup procedure I use is the do a full backup of both database and transaction log via script to a separate area. If the transaction logs are backed up separately from the database i.e. database by SQLServer, transaction log by TSM how would you do a restore? Restore the last database via EM then cycle through the transaction logs via TSM?
We're using SQL2000; is there a different strategy for SQL2005?
:sick: (not feeling quite so sick now, just a bit queasy)
Madame Artois
August 22, 2008 at 8:05 am
database by SQLServer, transaction log by TSM how would you do a restore? Restore the last database via EM then cycle through the transaction logs via TSM?
Precisely... just don't forget to do the initial database restore with "NORECOVERY"
No difference for SQL 2000 vs SQL 2005
August 26, 2008 at 5:03 am
Thanks for the information, guys. I'm supposed to be getting further information from the company who supplied the consultant but nothing has arraived as yet.
I'll keep you posted if they come up with anything else.
:Whistling:
Madame Artois
August 27, 2008 at 10:52 am
We use Tivoli as our primary backup and restore system. What I found is that the Tivoli system can interfere with the normal database processing (soaking up the CPU and causing performance problems) even though it supposedly is only doing a standard SQL backup. The other main issue with we had with Tivoli was that the restore times were "impressive" - and NOT in a good sense.
What we ended up doing was a sort of compromise. I have a regular SQL maintenance job that does backups to disk and then the .bak files are backed up in the Tivoli system. Restoring to file is slow, but much faster than attempting to restore a database.
Steve G.
August 28, 2008 at 1:05 am
Thanks for that Steve. I've had still had nothing from the company. However I did find a copy of IBM Redbook on the subject. Having read parts of that I worry about the decisions that the consultant must have taken prior to my involvement.
Will keep you posted on this but I'm starting to feel queasy again!
:sick:
Madame Artois
August 29, 2008 at 5:51 am
I used TSM at the last place, approx 3 years ago, and I found the only issues I had were when restoring.
As I've said on this page, restoring a SQL database backed up via Tivoli Storage manager to another server, for testing, is an error prone process due to the fact that you have to do it through the command line. The GUI is too unreliable! The command line consists of the following options.
tdpsqlc Restore database_name FULL /SQLSERVER=target /FROMSQLSERVER=source /TSMNODENAME=source_sqldb
/RELOCATE=data_instance /TO=data_file_location /RELOCATE=log_instance /TO=log_file_location
/INTO=database_name /RECOVERY=No
so a typical command might look like:
tdpsqlc Restore CMDB FULL /SQLServer=MyDestinationServer /FROMSQLSERVER=MySourceServer /TSMNODE=MySourceServer_SQLDB
/TSMPASSWORD=secret /RELOCATE=CMDB_Data /TO="c:\MSSQL\data\CMDB_Data.MDF"
/RELOCATE=CMDB_Log /TO="c:\MSSQL\data\CMDB_Log.LDF" /INTO=CMDB /RECOVERY=YES /REPLACE
To aid me in this, I wrote a spreadsheet to generate the restore commands. It can handle multi file restores and restoring to different locations. You can download a copy from the page mentioned above. The TSM GUI may well have got better, but if this helps anyone, I'll be happy 😉
Dave J
August 29, 2008 at 6:20 am
Thanks for your help. I'll start researching this fully next week; I have been involved in planned work this week.
If anyone has any other input, I'd be glad to know of your experience
Madame Artois
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply