How do you handle your SQL backups?

[evilution]

I'm trying to find out how mid sized companies are handling their database backups.

Currently, on each server, we provide the DBAs with a chunk of SAN disk where they spin off a copy off a flat file copy the database. After they complete their backup to disk they run a dsmc command to send that file to TSM.

This process works great as it provides them an on disk copy to use as they seem fit. For example they can rename and retain the file as a gold copy for as long as they want or they can use the existing file to perform prod-to-qual restores/refreshes whenever they want. In fact they have written scripts to do just that. They have automated the entire refresh process for qual and test. Access is controlled via NTFS perms and service accounts are used to process the restore request.

When building the servers we ask them how much storage they need for one full copy fo the database and that is what we give them. The trouble is sometimes they ask for more than really need. Back in the day we overlooked this behavior because storage wasn't as expensive and databases weren't as big.

Today we can no longer support the process of providing each server with SAN disk for backups. We are now entertaining using direct to TSM TDP backups for DR but when discussing this with the DBAs they questioned how they will support the refresh operations. They provided us with statistics that show APP/DEV is running in upwards of 50 restore operations each day. These are all using the flat files on disk and not TSM. Some of these are huge restores some are very small but long story short TSM would be unable to sustain this type of load. So we are kinda back to square one.

I'm just curious how everyone else supports not DR but database refresh operations, gold code redirected restores and production to test type operations. Please chime in with what you are doing.

 

[moon-buddy]

I am curious on how you achieve generating flat files and satisfy point-in-time restores. I don't think you are achieving this correctly without quiescing the database. This is one reason why we stick to the TDP for TSM solution instead of dumping flat files.

Our approach is to do SQL logs via TDP on the hour or via MS SQL tools which also ends up in TSM. Restore is not an issue since we use VTL on Data Domain and replicated to another Data Domain for DR purposes.

Our DBAs can restore anytime without bottlenecks or congestions.

 

[evilution]

We dumping transaction logs and log backups to disk as well. They all land in the DBBackup folder with the flat files. Then once a day we come through and sweep up all those files.

We have tested this process time and time again and it works great for DR. It also allows APP/DEV to refresh qual/test from the disk copy of the flat file whenever they want. Additionally it allows them to rename a file so that it wont be overwritten by the following nights backup. Those renamed files can be stored for longer periods of time and used as gold code or fresh starting points for app dev.

Without that local disk to spin off flat files I believe we could still provide DR protection but we cant seem to find a way to support the refresh operations. There's more minor stuff as well. Such as the DBA's insist on running a verify after the backup so basically TSM needs to do a synthetic restore on every database every night. The DBA can no longer rename files and move them around. The process of redirected restores is a PITA. An after backup change in retention is practically impossible to do. Not to mention the CF that will be involded to provide access control to the TDP restore process IF a couple hundred developers need restore access to specific databases but not all of them. Sooooooo yea this is ugly. I don't understand how this can be accomplished without providing the DBA a chunk of disk to write to first.