December 2, 2002 at 5:16 am
Hi folks
I am newbie for SQL server 2000 and I hope you guys help me in this..
I have sql server which running main server database and all the users access to it.
I have development one on my personal pc. both are on network so i have configure this main database on my pc.
Now, I want to transfer complete database from main sql server to mine one and schedule this job every day before user start to use the application (main database).
At the moment I am updating the database via restore option from daily backup database which is bit awkward as you guys know. I consider bcp, DTS methods to do this but I am not sure that which one should be robust and safe to use.
please advice me relevant issues involve with it and how can do it...
looking forward to receive some advices from you guys..
thanks in advance..
swarn
SqlIndia
December 2, 2002 at 5:27 am
I think using dts is the easist solution. With the copy database wizzard you can create dts package that does this for you. This package can be scheduled to run every evening.
December 2, 2002 at 5:49 am
Well a bit awkward ???? why and how ????
Just Schedule a Full backup Every night with the SQL Server Agent.
Add a step to copy the file to your machine.. XP_CMDSHELL ' XCOPY bla bla'
and start a scheduled restore on the other machine, what's wrong with that....
And why is it awkward ??????
(You could set a trace option to be able to restore directly from a network share) but this simple trick should work nice !.
I have pretty bad experiences with DTS because the error reporting is very bad.... You just get a Failed message and you don't know where it failed !.
December 2, 2002 at 9:55 am
I used to be on a weekly release cycle (new build released every Wed). I scheduled a refresh from Prod to QA every Mon, 4:00am, using scripting in a DTS package that got the production backup file (.bak) from the backup server (where it had been pushed using my pushftp method :http://www.sqlservercentral.com/columnists/sjones/pushftp.asp). It then restored the file and fixed the users. After the first restore where I manually fixed logins, this worked flawlessly. I even had a script run that fixed data (changed email, reset flags for testing, etc).
I've found the copy database task in DTS to be flaky and bcp cumbersome. In 8 years of refreshing dbs, nothing works better than backup/restore (IMHO).
Steve Jones
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply