I have a 200GB database running on SQL Server 2008 R2 Enterprise Edition. I just recently partitioned it in order to archive 11 months of history, so it was pushing 400GB prior to archiving.
Our company has 2 data centers in order to implment DR solutions. Most of the databases are at DC1 where there is tape backup infrastructure, where DC2 doesn't have tape backup capability.
This VLDB of 200GB resides at the DC2 location with no tape backup infrastructure. Our server team stopped doing tape backups about a year ago when the database .bak file exceeded 90GB and became too large to snap back to DC1 to backup to tape. I keep 2 disk copies of the .bak file, but still am concerned if a recovery is needed that requires going back more than 2 days.
I am looking for solutions. I thought I could setup a log shipping solution from DC2 to DC1, then each night stop the log shipping and run a backup of the secondary standby, but am concerned it could through the ability to start log shipping into a tiz. I have been doing a lot of research and see that it could cause a problem, but if a standby can be connected to and read from, you'd think a backup could be done.
The database grows by 30GB a month with a daily average of 1-2GB a day, so the traffic to log ship from DC2 to DC1 should be manageable. It is a vendor supplied database, so we try very hard not to modify it to support our environment, although, I did just partition it with the vendor's approval.
Anyone have any ideas of how best to save .bak files to tape from a location without tape infrastructure?
Best Answer
Log shipping to the primary site then doing backups isn't going to work as you can't backup a database which is waiting for t-log backups.
With SQL 2008 your only option would be to backup to local disk, have the storage array (I'm assuming that you have a storage array in both sites that can replicate data between the sites) replicate the backup LUN to the array at DC1. Then during the day once the backup has been fully copied over to DC1 snapshot the copy of the LUN at DC1, mount it to a server at DC1 and backup the database to tape.
Another option would be to upgrade to SQL Server 2012 and take advantage of AlwaysOn Availability Groups which would let you replicate the data to a server in DC1 then you can take your backups from the server in DC1.
A third option would be to buy more storage at DC2 and keep more than two backups around.
A fourth option would be to move the application to DC1 and run it from there.
A fifth option would be to buy a tape backup unit for DC2.