April 18, 2011 at 1:39 pm
Hello,
I receive hourly transaction log backups from an offsite production database located on a SQL Server 2008 server that I need to restore to our SQL Server 2008 R2 server for reporting purposes. Setting up automatic log shipping is not an option so I am in the process of setting up a process via SSIS to pull the tlog backups down from our FTP server and restore them throughout the day on an hourly basis.
To my knowledge the only way to restore the transaction logs to our R2 server is to restore the database with RESTORE WITH NO RECOVERY so that I can restore the subsequent tlog backups throughout the day. (RESTORE WITH STANDBY is not an option due to the differences in SQL Server version).
RESTORE WITH NORECOVERY leaves the db non operational though. What am I missing here? Any suggestions or advice are greatly appreciated.
Thank you,
D
April 18, 2011 at 1:46 pm
There's no way to restore logs, leave the DB operational but still be able to restore more logs. Standby is normally used for read-only access, but as you noticed that only works when the primary and secondary versions are the same.
What's this for? If just for reporting, have you considered replication?
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
April 18, 2011 at 2:16 pm
Thanks for the reply Gail.
I need to check into whether replication is an option with this vendor. What type would you recommend based on my previous description of our needs? I would think transactional would be too much, so I would assume merge would be the route we want to go...
D
April 18, 2011 at 2:26 pm
Transactional. Snapshot if it's a small DB, some or many tables have no pk and yesterday's data is good enough.
Merge is for 2-way replication, it's an absolute nightmare to admin and if something breaks it's easier often to re-setup than to fix.
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
April 18, 2011 at 6:03 pm
Transactional Replication is best for your senerio
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 10:13 am
Thanks all, I went ahead an installed an instance of 2008 to solve this one as it turned out to be the most painless option.
D
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply