Full TDP SQL Backups versus Log Backups

[Rouen]

Hi there

We are still running TSM 5.5 Server and TDP for SQL 5.5.

We have a SQL DB about 2 TB big. The DBA's want us to backup the LOGS every hour whilst the FULL DB Backup runs (this full backup takes about 4 - 5 hours).

They want to have the ability to restore 'midway' through the DB backup.

This means they want to restore the previous night's full backup and 'roll' the logs till the 'midway' mark.

I told them this does not make sense and don't know if such a restore will actually work (I don't have a test environment at the moment to test this).

Also, when I try to run a LOG backup with a full DB backup, the log backup will pend until the DB finishes (or miss completely).

Please advise.

Rouen

 

[dms19]

I dont know that you can do that within TSM itself, but the SQL dba should be able to set up hourly dumps of the logs to flat files and you can back them up from a landing space with the BA Client.

 

[GregE]

I dont know that you can do that within TSM itself, but the SQL dba should be able to set up hourly dumps of the logs to flat files and you can back them up from a landing space with the BA Client.

Don't do that. You'll have a big problem if you ever try and restore logs backed up by TSM TDP.

Check this discussion. http://adsm.org/forum/showthread.php?t=21414

 

[dms19]

That's interesting, thanks for the heads up. We've actually had this exact process in place for the last 4+ years and haven't seen an issue. We've done dozens of restores and haven't an issue to date. Perhaps it was dumb luck.

 

[GregE]

Interesting. "Dozens" is too many to be dumb luck. Now you have me curious, though I did my tests several times back when it happened. It was consistent - it worked ok everytime I didn't try to use TSM logs that took place before SQL logs. Everytime I did, it failed with the error.

Had you restored TSM full backup, then applied TSM log backups that occured AFTER SQL log backups that took place IN BETWEEN that TSM full and the TSM logs? (geesh, that's hard to ask coherently)

 

[dms19]

Interesting. "Dozens" is too many to be dumb luck. Now you have me curious, though I did my tests several times back when it happened. It was consistent - it worked ok everytime I didn't try to use TSM logs that took place before SQL logs. Everytime I did, it failed with the error.

Had you restored TSM full backup, then applied TSM log backups that occured AFTER SQL log backups that took place IN BETWEEN that TSM full and the TSM logs? (geesh, that's hard to ask coherently)

Are we talking about the same thing? When you say TSM log backups, are you referring to a TDP for SQL log backup, or a SQL dump (outside of TSM) to flat files and then backed up via the BA Client?

Our process is as follows:
1. TDP Full's [nightly]
2. SQL log dumps to flat files [hourly I believe]
3. BA client backups of the landing space for SQL dumps [nightly]

We don't actually use the following in any of the .cmd files
tdpsqlc backup * log

In most cases we have restored two things, the SQL database (TDP) followed by the logs (from the BA Client) to a landing space, which were then handled by the DBA's. If there were issues with them being spun up I was never made aware of them and assumed over the last few years, its been a successful process.

Now you've got me worried and I'll need to follow up in-house.

 

[dms19]

I just remembered....we did run into an issue similar to what you posted in that other link, when we were trying to incorporate log backups with TDP as well as paranoid DBAs doing things on their own. There was some issue with the reset of the logs being out of sync with what TSM noted from the time of a full to a log backup.

In the end we came up with what I listed below, doing the database Fulls via TDP and the logs as SQL dumps and grabbing those logs in the BA Client nightly backup. That way they were able to spin up logs if necessary before the actual FULL happened taking us out of the mix for those during business hour issues.

They wanted us to schedule hourly database and log backups within TSM, after we were done laughing (most of the databases take 2 - 4 hours to backup) we suggested the nightly full + hrly log dumps outside of TSM.

 

[GregE]

I didn't know that a mix of TDP full and SQL log like that would work. A DBA told me it would cause problems, BUT we never tried to restore from that scenario so it was never verified one way or the other. Thanks for posting, that's good info to know.

 

[dms19]

I think its a debatable topic as we've have many discussions about the TDP Full still tripping the SQL side up into resetting the logs and throwing off the SQL dumps. We've had success with our setup and I know the SQL dumps have saved us from having to do a bunch of restores during the day because a DBA commited something they shouldnt have, etc.

 

[TonyB]

Had a chat with one of our DBAs this morning - he seems to think that its very possible to mix the two methods, as long as no action is taken at the MSSQL side to break the LSN sequence (the log chain).

Apparently if the DBAs switch from full to simple recovery mode for some reason you'll be having a wee moment if recovery is required (via either method).

T

EDIT: hmm just did some reading and from what I can see on SQL2000 a full backup will break the log chain, whereas on SQL2005+ it won't...asked my DBA, will see what he thinks.

 

[Rouen]

Log Backups together with FULL DB backup

Hi there

Thanks for the many replies.

I would still like to TEST this scenario where I run a SQL FULL DB backup and a SQL LOG backup AT THE SAME TIME.

I successfullly scheduled the DB backup, which is currently running but now that I am trying to schedule the LOG backup as well, the schedule misses all the time.

How do I get BOTH SCHEDULES to run ?

Or is this not possible ?

I can run this manually, using two seperate sessions but I need to allow the schedules to do 'their thing'.

Many thanks,

Rouen

 

[mccleld]

You'd need to run two schedulers - the TSM client scheduler doesn't allow for more than one simultaneous scheduled operation to run. Problem is that, iirc IBM don't support more than one scheduler running on a client with the *same* node_name - that's not to say that it wouldn't work though...

//DMc