September 23, 2008 at 7:01 am
Hi all,
I have been trying to copy a database to a new location (a new server) running the same version of sql server (sql server 2005). I tried a couple of times, but I am not getting the desired result.
In order to perfrom the above task, I used the "wizard to move or copy" database. After following all the procedures and finishing, I got the following:
Add log for package (success)
Add task for transferring database objects (success)
Create package (success)
Start SQL server agent job (success)
Execute SQL server agent job (Error).
Note: It wasn't even my intention to execute sql server agent job at this stage.
And everything fails and I did not get any new database in the destination sql server instance. Refreshing the server as well as the database did not help at all.
I would be very grateful if anyone could give me a hint so that I get going further.
Thanks indeed for your help
Niyala
September 23, 2008 at 7:10 am
What is the error you are getting?
There are other ways to move databases as well that may be better for you;
If you can afford down-time then a dettach/restore of your database will work, or you can perform a back up and restore of your database to the new server.
September 23, 2008 at 7:12 am
to copy a db from one server to another, I typically will do one of two things depending on time, size restrictions etc. The easiest way, if you can have a bit of down time, would be to detach the db from server 1 and copy the file via windows explorer or whatnot and the attach the db on the destination server.
I you can't have downtime (i.e. you're taking a Production db and moving it to a dev server) just take a backup of the database on server 1 and copy the backup file to serer 2 and do a restore of the database on server 2. This will keep your production db up while you move a copy of it to server 2.
-Luke.
September 23, 2008 at 7:25 am
Thanks. If fact I am trying to avoid downtime. That is why I chose the wizard. Further, I can schedule the wizard.
The error report I found in the error log follows:
Package "CDW_SourceServer_DestinationServer" failed. For more information check
http://go.microsoft.com/wlink/events.asp
I hope this information helps to figure out where the problems lie.
Thanks
Niyala
September 23, 2008 at 7:43 am
1) Do you have the appropriate permissions for both the source and destination servers?
2) since you don't want down time and you want to schedule it, why not create a job with 2 steps... a) backup the source DB to a drive on the destination server. b) restore the backup to the destination server.
Also, can you give us a bit more background on your situation? Are you populating a development server with data? Are you moving a production db from one machine to another to get it off older hardware? Is this for Disaster Recovery Purposes? answers to these questions could very much help us to point you in the right direction to the best answer for your situation.
Your error message doesn't really help us much, are there any other errors in the event log or the SQL Log on either the source or dest servers?
-Luke.
September 23, 2008 at 8:05 am
1) Do you have the appropriate permissions for both the source and destination servers?
Yes of course. I have permissions to both. I am a member of
sysadmin, therefore, the question of permission is out of problem list.
2) since you don't want down time and you want to schedule it, why not create a job with 2 steps... a) backup the source DB to a drive on the destination server. b) restore the backup to the destination server.
Well, I will have to try this, probably this can offer an alternative solution but not very optimum for me.
Also, can you give us a bit more background on your situation? Are you populating a development server with data? Are you moving a production db from one machine to another to get it off older hardware? Is this for Disaster Recovery Purposes? answers to these questions could very much help us to point you in the right direction to the best answer for your situation.
I am interested in moving the db from an older server, which will retire after successful migration, to a new server, which will take over the job. So I am moving the data from an old to a new machine.
Unfortunately the error message is not informative and that is all I could fish out of the logerror message.
Really thanks for your concern and time
Niyala
September 23, 2008 at 8:20 am
ok, so you're attempting to Migration your production data from one server to another with 0 downtime? If you either a) use the Copy DB Wizard or b) a backup and restore type procedure, what do you do with the records that have changed on the source before you cut everything over to the dest?
For this reason and again depending on the size, I'd suggest the backup restore procedure WITH NO Recovery, and then to minimize downtime, stop user access to the db on server 1 and do a transaction log backup, restore it to the new prod server WITH Recovery and you should be back up and running without losing any data. And your downtime would be minimized.
I understand you want to schedule this, but I'd be fairly leary of moving a database without being in the building, particularly if I needed to keep downtime to a minimum.
Additionally, You could look at doing something like setting up DB Mirroring and then breaking the mirror. Although I'd test this very thoroughly before doing it. Log shipping could be another option for you.
-Luke.
September 23, 2008 at 8:41 am
Many thanks indeed! I will work on your suggestions and see what suits most to my situation.
Your suggestions are very good and will help me work on the detalis.
Niyala
September 23, 2008 at 8:49 am
Backup / restore is dirt simple and there is no downtime. I'm not sure why that would not be optimum.
The copy db wizard is flaky, and I do not recommend it. If you had success for all the items, is the server setup? Are your jobs/packages there?
September 24, 2008 at 5:15 am
Thanks a lot. Backup and restore works well to do the job. However, why the copy database's last step (start sql server agent job) fails keeps me busy. Is there a package missing that's needed to do this task? I know that SQL was setup properly.
Regards
Niyala
September 24, 2008 at 7:28 am
I am required to migrate my database on SQL 2005 from a remote server to the local server. I am going the backup restore way as I did in the past. It is mere simple and there is no downtime too.
Hope this helps..
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 24, 2008 at 7:58 am
Thanks a lot. Your information was very helpful.
Regards
Niyala
September 24, 2008 at 10:04 am
We have moved Production a few times
Backup-and-Restore: you can almost have no down time as we just copy the new records into the NEW server after
Detach-copy-Attach: down time for sure, but no consistency issue
September 24, 2008 at 1:33 pm
Not quite sure if this is what is happening but....
I would assume the reason it is failing is because a particular job that is trying to be moved is failing. Possibly because a login doesn't exist on the server or in a step of a job a default database is selected that doesn't exist or something of that nature.
If possible you should skip the part where it trys to migrate the jobs. Then you could manually script the jobs out and execute them.
September 25, 2008 at 12:25 am
Thanks a lot. In fact I intended to skip the part which executes the job, but I could not see options that enable to exclude. All I did was the default procedure where only the logins were selected.
Would you be able to give me a hint on how to exclude the job so I script later on?
Thanks for your help
Niyala
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply