Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
svm
Starting Member
2 Posts |
 Posted - 2009-07-07 : 16:09:53
|
| I am trying to copy a database (in-production) from Server01 to server02. Both are running Win2000sp4 with SQL2000 Server.The job is setup using SQL Enterprise Manager|DTS|Local PackagesI then, schedule the job to run daily. In the job history, I get "The job failed. The Job was invoked by User sa. The last step to run was step 1(job)." The step 1 errors out as:... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; Scripting objects for Transfer; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; Scripting Logins; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; Scripting Users; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; Scripting Users : 0 Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; 0 Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; Scripting Users : 5 Percent Completed; PercentComplete = 5; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; 8 Percent Completed; PercentComplete = 8; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjects... Process Exit Code 1. The step failed. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2009-07-08 : 22:44:56
|
| This doesn't really tell us much.... There are no error messages.FWIW I wouldn't do it like this anyway. If you don't want to use replication (the proper way to do this), I would create a script that backs up the source db and then restores the backup to the destination db.I've always found copying DB objects using DTS a pain in the arse.HTH,Tim |
 |
|
|
svm
Starting Member
2 Posts |
Posted - 2009-07-09 : 10:18:25
|
| Tim,Thanks for the reply. I am not a DBA (of any sorts). I work with Network systems. So, this is the only way I know.More importantly, this method was working until recently. I don't know what changed. Any help would be appreciated. |
|
|
|
EMarkM
Starting Member
16 Posts |
Posted - 2009-07-10 : 09:19:20
|
| +1 for backup and restore - DTS is woeful for this and, as you have seen, does not give decent errors when it collapses miserably.[Edit] bit more info for a beginner: best method for a lot of folk is to set up a scheduled SQL job with the following steps, scheduled to run after the daily full backup of the source DB has finished (check your times!):-1. Check backup file of source DB exists.2. Kill all SPIDs to destination DB.3. Restore destination DB from file.4. Set any permissions/read-only options, etc. |
|
|
|
|
|
|
|
|
Copy Database