February 6, 2012 at 4:16 am
Hi,
First, a general question: What strategy do you normally use for creating a nightly-updated copy of a database on another server for use in reporting? Log shipping? Snapshots? What is recommended?
Now, in reality, I need to do it for a SQL 2008 R2 database but only have SQL 2008 on the reporting server :/ I know that not much is available in this scenario, but are there other software solutions or techniques that I can use?
Thanks,
Craig
February 6, 2012 at 5:05 am
i think , Logshipping is best option for you night create database copy and for reports.
and specifying question
How much data of databases? and How much data is changing?
February 6, 2012 at 6:47 am
as always, it depends 😉
- Can your need be solved using backup ( of prod ) and restore ( to report env ) ?
( one should be able doing this just by scheduling a PIT restore based on the current backup set, wright ? 😉 )
- If the db size is to large to be ported over night, you could provide a read-only copy based on a (no)restored full backup and apply log restores using the STANDBY parameter.
Check BOL "RESTORE (Transact-SQL) "
- If you only need partial stuff, the classic replication alternative can be considered according to your needs.
- if near time ( not requested in OP ) would be requested, you could even implement db-mirroring with ad db snapshot (read-only of course) on a more frequent basis.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 6, 2012 at 6:50 am
v_nikonor (2/6/2012)
i think , Logshipping is best option for you night create database copy and for reports.and specifying question
How much data of databases? and How much data is changing?
~5Gb data, with new tables created daily, and updates across around a dozen tables each day.
Log shipping can't happen for SQL 2008 R2 > SQL 2008, right?
February 6, 2012 at 6:58 am
5 Gig database?
Nighly?
The answer is: Full Backup and Restore.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 6, 2012 at 7:10 am
PaulB-TheOneAndOnly (2/6/2012)
5 Gig database?Nighly?
The answer is: Full Backup and Restore.
As Paul said, write a stored procedure that will restore the database on reporting server by using production database backup. Scheduled it in a job.
February 6, 2012 at 7:10 am
ALZDBA (2/6/2012)
as always, it depends 😉- Can your need be solved using backup ( of prod ) and restore ( to report env ) ?
( one should be able doing this just by scheduling a PIT restore based on the current backup set, wright ? 😉 )
No because I can't restore to SQL 2008 from SQL 2008 R2 :/
- If the db size is to large to be ported over night, you could provide a read-only copy based on a (no)restored full backup and apply log restores using the STANDBY parameter.
Check BOL "RESTORE (Transact-SQL) "
- If you only need partial stuff, the classic replication alternative can be considered according to your needs.
I don't need immediate updates, nightly will do, plus I don't want the schema modifications and admin that comes with replication.
- if near time ( not requested in OP ) would be requested, you could even implement db-mirroring with ad db snapshot (read-only of course) on a more frequent basis.
Will mirroring work R2 > 2008?
Thanks,
Craig
February 6, 2012 at 7:18 am
Please don’t complicate the things. For your requirements FULL Backup & Restore is most suited option.
PS how far is Source and Target database Servers? Do you have any bandwidth constraints?
February 6, 2012 at 7:22 am
PaulB-TheOneAndOnly (2/6/2012)
5 Gig database?Nighly?
The answer is: Full Backup and Restore.
Yes 5Gb nightly (x 2 actually).
Cannot restore a 2008 R2 database to 2008.
February 6, 2012 at 7:22 am
Dev (2/6/2012)...how far is Source and Target database Servers? Do you have any bandwidth constraints?
Database is 5 Gig in size! who cares about bandwith? you can even write the dump file to a pen-drive, attach it to the leg of a pigeon and send the bird to the remote site 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 6, 2012 at 7:30 am
Dev (2/6/2012)
Please don’t complicate the things. For your requirements FULL Backup & Restore is most suited option.PS how far is Source and Target database Servers? Do you have any bandwidth constraints?
Two VMs on different hosts on the same LAN.
February 6, 2012 at 7:32 am
Physical distance plays a vital role in data transfer. Little Birdie might take 5 days to reach a destination 5K miles from the source database server.
February 6, 2012 at 7:32 am
Dev (2/6/2012)
Please don’t complicate the things. For your requirements FULL Backup & Restore is most suited option.
But that won't work as I need to go R2 > 2008.
February 6, 2012 at 7:34 am
Dev (2/6/2012)
Physical distance plays a vital role in data transfer. Little Birdie might take 5 days to reach a destination 5K miles from the source database server.
If 5K miles away then put the bird on a FedEx box and ship it for next morning delivery! 😀
You know what I meant... you can transfer 5 Gig even over a dial-up connection.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 6, 2012 at 7:35 am
What about copy database task? 5 GB database on 2 VMs on LAN, it shouldn’t be a problem.
Try it with wizard & save it as SSIS package, schedule & run later.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply