June 9, 2010 at 2:09 pm
My boss has asked me to restore a database snapshot to a database other than the one that created the snapshot. According to all of the whitepapers I have read this is not possible. That the snapshot can only be used to revert the source database.
Has anyone here found a way to accomplish this?
I ask because we have a very large database (600GB +) mirrored between 2 sites. One in Texas, the other in North Carolina. Or production environment is in Texas, and our development environment is in North Carolina. We would like to refresh the development environment with a copy of the production environment. Currently we copy the backup file(s) onto an external disk and ship it.
We have created a snapshot of the mirror database, and can query it successfully. (Which proved invaluable when demonstrating to the executive team that the data really did transmit from Texas to North Carolina.)
Thanks in advance,
Greg
Greg Roberts
June 9, 2010 at 2:37 pm
Simple answer for your boss. You can't.
A snapshot only contains old values of pages changed in the source database since the snapshot was created. When you revert the source to the snapshot, those old pages are simply copied back, but the snapshot is not a complete database
You could always export from the snapshot and recreate the DB, but I suspect that's not a very good solution.
One option that may or may not be feasible. Fail the mirror over so that the North Carolina database is the principal, then take a backup, then fail the mirror back so that Texas is the principal. If you have a maint window that may be a quick option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2010 at 3:10 pm
I like the idea of failover, backing up, then failing back. unfortunately it take ~4.5 hours to backup the database. And it must be available 24x7 except for 2 hours on Sunday morning. When other maintenance is occurring.
Also, the app devs haven't yet figured out how to alter their application to properly failover. (That is being developed as we speak.)
Greg Roberts
June 9, 2010 at 3:13 pm
Greg Roberts-134214 (6/9/2010)
Also, the app devs haven't yet figured out how to alter their application to properly failover. (That is being developed as we speak.)
If they're using SNAC (SQL Native Access client) to connect to SQL, the failover capabilities are automatically present. They're a feature of the driver.
p.s. What are you using to take backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2010 at 6:53 am
AFIK they are using the native client, but the app is written a little odd. There are a lot of hard coded values instead of looks, etc. I know with the native client it is a simple setting in the config string. It's just a matter of the developers having the time to implement (1 minute? :-P) and test.
We're using Redgate SQL Backup. It was a decision made before my time, and not necessarily a bad one. The product works reasonably well, and it provides encryption and compression of the backup file(s).
Greg Roberts
June 10, 2010 at 7:42 am
Good luck with your devs. 🙂
Re backups, I just wanted to check that you were using a compressed backup tool.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply