December 11, 2015 at 5:07 am
Hi All,
I've searched SSC and have asked Google and the answer to my question seems more of a hack than a standard, supported, production ready process so I'll post the question here in hope of someone having solved the problem before me.
-----------------------------
Scenario (SQL 2008 R2):
You receive a one off backup of live database. This is the only backup you will ever receive. At 24 hour intervals you will be sent (via FTP) a transaction log backup. You will never be given another full backup and you will get a log backup everyday, forever.
Requirement:
You have to provide the customer with a read/write copy of the database using the one-off full backup and the daily log backups. The customer is perfectly happy for the database to be overwritten every 24 hours and for any updates to be lost (yes, really!).
Some things to consider:
This could be manageable for a few days - i.e. restore backup, apply logs, off we go. But 10, 20, 100 days later it'll be a case of restore backup, apply 100 log backups! Full backup is ~50GB, tlogs 100MB - 2GB. Have to rule out log shipping, etc as live database is completely cut off from customers network.
-----------------------------
The only solution I know is really...horrible. These are the steps:
1) Restore database with norecovery
2) Apply log file(s) with standby
3) Database is in standby/readonly mode
4) Take database offline
5) Create new empty database with similar number of data and log files
6) Take new database off line
7) At OS level replace newly created data and log files with copies from standby/readonly database (change file names to match those being overwritten)
8) Bring standby/readonly database online (ready to accept next days log backups)
9) Bring new database online (ready for read/write) -- this generates a warning but taking it back off line and on-line again seems to update the system catalog and doesn't report a warning.
Is there a supported/standard way of achieving this?
December 11, 2015 at 7:04 am
Yikes.
Why not simply keep the database unrecovered and continue to apply log files to it every day? Basically build your own log shipping process. It's still subject to utterly breaking down and never being recoverable. This is an extremely brittle setup.
However, you're correct, depending on the size & number of transactions, applying these things after 10-20 days (never mind 100) is going to be a nightmare.
Also, this has implications for the database that you're receiving all this from. It really only has a single log backup every day? That basically means that they're willing to lose up to 24 full hours of their data? I assume they have additional full backups running on the other machine, or they're also looking at a recovery nightmare should things go south.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2015 at 7:06 am
Grant Fritchey (12/11/2015)
Yikes.Why not simply keep the database unrecovered and continue to apply log files to it every day?
Customer seems to want a read-write DB where the changes are discarded every evening (weird, but...)
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
December 11, 2015 at 7:16 am
Yup, as Gail said, the customer wants a read/write database copy. I believe they have some scripts (and possibly data) that they apply (and upload) and are happy to do this every time the read/write copy database is overwritten.
I just wanted to make sure that I wasn't missing a fundamental and simple solution to this.
I think one option is to try to (reluctantly) understand the changes they make to the database and why it has to be read/write and maybe see if this can be avoided. At least that way we can keep a single standby/readonly copy of the database and apply the next days log backup to it relatively easily.
Sigh...
December 11, 2015 at 7:21 am
Grant Fritchey (12/11/2015)
Also, this has implications for the database that you're receiving all this from. It really only has a single log backup every day? That basically means that they're willing to lose up to 24 full hours of their data? I assume they have additional full backups running on the other machine, or they're also looking at a recovery nightmare should things go south.
It is indeed very worrying that only a single log backup is done a day! It's possible they are doing a copy only backup every 24 hours to distribute via FTP while some other backup application does more frequent backups.
December 11, 2015 at 7:38 am
Oh, I didn't understand that it has to be writeable...
This really doesn't make a lot of sense... at all...
What the heck are they writing to this database that they're willing to throw away every day? I mean... screw it, set up a copy of the database that's blank and store what they write and then UNION all the read queries... Yeah, ugly, but hey, less ugly than what you're currently faced with.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2015 at 7:58 am
Grant Fritchey (12/11/2015)
I mean... screw it, set up a copy of the database that's blank and store what they write and then UNION all the read queries... Yeah, ugly, but hey, less ugly than what you're currently faced with.
Hmmm that actually does sound like a better plan. Once I understand what it is they do I'll see if this works.
I'll report back with what the final solution is if/when one is found.
December 11, 2015 at 7:59 am
feersum_endjinn (12/11/2015)
It's possible they are doing a copy only backup every 24 hours to distribute via FTP while some other backup application does more frequent backups.
No.
A copy-only log backup doesn't truncate the log, but it can't magically read portions of the log already truncated by the more frequent normal log backups.
It's theoretically possible they're doing the reverse. Copy-only log backups more frequently and one log backup that truncates the log run once a day, but I'd say it's highly unlikely that it's even been considered.
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
December 11, 2015 at 8:14 am
Honestly, the reapply all logs up to today, without getting another full backup just won't work. You'll eventually have too much work to do overnight and it's too brittle.
First, you will need to get a second backup at some point. You will have issues on your side at some point. However, barring that,
What I'd think about doing is restoring with standby and continue applying logs. I know that you can't back this up and save it, or use it, but you can ETL stuff over. I'd build jobs to clear out another database, and then ETL in the standby database, table by table. I'd remove constraints, and add them back if needed, but really I'd just keep moving over all data.
It would be easier than trying to figure out what's changed, and gives you a reset. Of course, depending on size of the db, this might not be doable either.
EDIT: Forget this. Error in code.
December 11, 2015 at 1:49 pm
GilaMonster (12/11/2015)
A copy-only log backup doesn't truncate the log, but it can't magically read portions of the log already truncated by the more frequent normal log backups.
Yes, my mistake. I was thinking on the lines of how copy only affects the differential base. Got my log and diff backups mixed up for a minute. I doubt their backup plan has considered these options however.
Steve Jones - SSC Editor (12/11/2015)
I'd build jobs to clear out another database, and then ETL in the standby database, table by table.
ETL is definitely an option but as that would require a resource, time and money and at this point I think it's not on the table! Well, at least more than my original solution.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply