1

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?

Improve this question
1
  • 1
    What kind of connectivity is there between DC1 and DC2? It sounds like DC2 is the DR site...can you move the app to DC1, where the tape drives are? Lastly, do you have the SQL Server 2008 R2 backup compression turned on? With EE, you paid for it.
    – Darin Strait
    Commented Apr 17, 2012 at 13:05

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • Thanks all. Okay, so log shipping may work right? If I set it up as a standby which should allow connections and read-only activity. Then I could do a copy-only backup, right? I need to try this in a test environment. The next paragraph is a little deep for this DBA as it sounds very much like storage administration. We are going to begin looking at SMSQL (Snap Manager for SQL) which I believe is in the ballpark of what you describe. Option 3 above...Upgrading to SQL Server 2012 would be cool. Both the log shipping and AlwaysOn Availability would require some $$$ for the extra storage. Option
    – user8225
    Commented Apr 17, 2012 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.