April 23, 2013 at 9:50 am
Hello - I have a db on one server, and I'd like to copy it over to a different server every night to use as a backup/reporting instance. These are both SQL Server 2008 R2. (Not sure what else is relevant information.)
Anyway, if there is something that details the steps to do this, I would be happy to go there and read that - does anyone have any recommendations? I did search the forums, but what I saw wasn't ss2008, so...thought I'd better ask.
Thank you in advance for any guidance/advice/knowledge-sharing --
Donna
p.s., you can take it as given that my "dba skills" are somewhat short of novice. 🙁 I get the security stuff (somewhat), but the manipulation/creation, etc....pretty much zip. Hence the cluelessness.
April 23, 2013 at 11:58 am
Hi Donna.
One option is to create a SSIS package and use a Transfer Task such as "Transfer Database Task" or "Transfer SQL Server Objects Task". We use the latter because with the former will cause some downtime with the source database (at least in SQL2005 it does). If downtime is not an issue I would lean towards the "Transfer Database Task". This can be scheduled to run in the SQLAgent.
John
April 23, 2013 at 11:58 am
Depends on your requirements what might be the best approach, but from the looks of it, you could script a backup from server a and restore with replace and move of data files to server b every night. The backup and restore scripts are pretty easy to setup. The only challenge may arise if server 1 and server 2 sit on different storage or are in different domains. Then you need to figure out a mechanism of moving the backup files to the secondary server.
Log shipping may also be an option if you need data more real time than what a nightly backup can offer.
Replication would also work well if you only needed a subset of the data.
More information can be found by looking through books online.
http://msdn.microsoft.com/en-us/library/ms130214%28v=sql.105%29.aspx
April 23, 2013 at 12:02 pm
Thank you both - I'll take a gander at searching these. I also found, in BOL, using a copy db wizard, and it says you can schedule the job to run later, so I'm thinking maybe you can schedule it to run every night. Any thoughts on that option?
Thanks again -- "the accidental DBA" -- Donna
April 23, 2013 at 12:03 pm
Ooo, very handy link, thanks!
April 23, 2013 at 12:06 pm
One big factor is concerning how fresh the data needs to be.
My preferences would be
1. Replication
2. Backup Restore via script on a nightly basis
3. Service Broker could be configured to do it as well
4. AlwaysOn (only listed as pref #4 because it requires SQL 2012. Otherwise it would be my #1).
5. LogShipping
6. Snapshot
Another alternative that kinda goes with option #2 is to do LUN snapshots.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 23, 2013 at 12:09 pm
the copy wizard will create an SSIS package with a Transfer Database Task. Because of the nature of the Transfer Database Task, there will be some downtime on the source database. An easy way to create this, just make sure your source database can be unavailable for some time. you will need to test it to know how long.
John
April 23, 2013 at 12:31 pm
Ah, good to know! We would run this in the dead of night, but of course, we'll have to sit down and just try it, so...not dead of night for that. Dead of Saturday, maybe. 🙂
So I'm thinking that this wizard will work kind of like the import task in ssms, where it will create the package, but then you can save/manipulate the ssis package, which would be handy. Usually once I see something built, I can go from there...it's the initial build that has me boggled. Not to mention the semi-complete ignorance. Heh-heh.
Thanks, peeps!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply