December 6, 2013 at 12:21 am
I am using SQL Server 2008 R2.
goal: I want to create a copy database and rename it in the same server.
I right click on a database > task > copy database ....
now at select destination server screen ...I choose same server as source...and then I click Finish
I get this error
"SQL Server Agent does not appear to be running on the destination server. If SQL Server Agent is not running on the destination server , copy database wizard will not function properly. Do you want to continue ?"
How do I fix this error ?
December 6, 2013 at 12:32 am
Easier way. Take a backup, copy the backup to the destination, restore the backup.
The Copy Database option can be a little finicky at times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2013 at 12:43 am
GilaMonster (12/6/2013)
Easier way. Take a backup, copy the backup to the destination, restore the backup.The Copy Database option can be a little finicky at times.
I am worried because .....
source db and destination db will remain in the same server......Is there any possibility of damage to source db if I do this ?
When do I rename new db name ?
Also .. will I able to use same credentials as source db to access new copy database ?
December 6, 2013 at 12:46 am
spectra (12/6/2013)
source db and destination db will remain in the same server......Is there any possibility of damage to source db if I do this ?
No
When do I rename new db name ?
When you restore
Also .. will I able to use same credentials as source db to access new copy database ?
Yes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2013 at 1:43 am
Thanks. This was very much helpful.
I have one more question . ....... If I want to refresh data , what is the easy way to do this ?
Do I need to drop the database and copy database again ?
OR
there is some other simpler way out for data refresh using backuped data ?
December 6, 2013 at 2:09 am
SQL Server Agent does not appear to be running on the destination server. If SQL Server Agent is not running on the destination server , copy database wizard will not function properly. Do you want to continue ?"
copy database works based on Agent job so start the agent in destination server and re-run the copy database steps.
December 6, 2013 at 2:21 am
spectra (12/6/2013)
Do I need to drop the database and copy database again ?
That's the easiest
there is some other simpler way out for data refresh using backuped data ?
There are ways to sync data, but they're not easy and most require a lot of manual configuration.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2013 at 7:54 am
I have to move data from a production copy of a database to a development copy. I do this by using an Import Data task and literally move all the data from the production db into the dev db. The wizard is quite easy to set this up. Best of all you can save the process as an SSIS package to be utilized at a later date. It may not be the best way, but it is a way and depending on the size of the database doesn't really take a lot of time to set up or run.
I will agree with Gail that the FASTEST way is to backup/restore the database. This is fine as long as the copy needs to be exactly the same as the production db.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply