October 1, 2003 at 12:25 pm
Every few weeks we need to copy our production database (call it PROD_DB ) on Server#1 and overlay our test database with current data (call it TEST_DB) on Server#2.
We've been restoring from a SQL Agent tape backup to the test database ..... works fine, but I'm dependant on the Network Admin to do the restore, which limits my timeframe. I'm curious about the "Transfer Database" option available in DTS. I tried it on a tiny test database and it worked fine. What would happen if I tried it on my 100 Gig database going across servers ? Would it take 10 hours ? The tape restore takes almost 2 hours and has to be done when tha network admin is here (9-5). If the DTS were viable, I could schedule it for 2 am.
I don't have much space on the target server ( 130 G total, database uses 100 G), so I can't restore using any of the methods requiring twice the space of the database. Does Transfer database require extra space ?
Any thoughts ?
(we're SQL 2000, sp2)
October 1, 2003 at 4:05 pm
Personally I wouldn't allow you to use DTS against a production server if I were your DBA. Having said that, I would say that the current solution is a very good one. Another possibility would be to have your production database use Snapshot or Transactional replication to create a snapshot of the database that you could then apply on a weekly basis. This of course would require some overhead on the production database and is not the easiest the maintain.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 1, 2003 at 4:12 pm
Gary,
Just curious...what are your reasons for not using DTS against a production box? Or are you only referring to using the DTS Transfer Database function?
Thanks,
Michael Weiss
Michael Weiss
October 1, 2003 at 5:52 pm
DTS Allows two way transfers and one small mouse click could easily destroy the database. Thus on my systems I don't allow something like that if I can help it. I also don't do it myself even though I am the DBA. The only things to hit my Production server are the apps designed for it and scripts I have had tested by testing and then verified myself on my own machine. Also since I have both Merge and Snapshot replication on my production box I don't want to have to rebuild it unless ABSOLUTELY nessecary! 🙂 I'm dreading the day I have to upgrade the server!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply