Issue copying SQL database from 2005 to 2008r2

Hi Everyone,
i am in the process of moving one of my sql databases to my new server.
My database is currently on SQL 2005 and i want to move it to my new one that is on a different domain and is running SQL 2008 R2.
Every time i use the copy database feature i keep getting this error just before finish.

SQL Server Scheduled Job 'CDW_HERCULES_ARES_MSSQLSERVERR2_12_1' (0xA813AF26FFBAFB4D96E295E0CEAB595E) - Status: Failed - Invoked on: 2010-11-25 14:57:14 - Message: The job failed.  The Job was invoked by User SCORPTEC\Administrator.  The last step to run was step 1 (CDW_HERCULES_ARES_MSSQLSERVERR2_12_1_Step).

any help would me much appreciated
Who is Participating?
JulianvaConnect With a Mentor Commented:
Is that a domain account or a SQL account? If its an sql account you would need to fix the orphaned user, meaning that there will be a user in the database but not have a login. you can fix this by

sp_change_users_login  'report' - will give you orphaned users
sp_change_users_login  'report'  'auto_fix'  accountname  - account name that the report will give you

If its a domain account you dont have to do this just create the login.

Som TripathiDatabase AdministratorCommented:
Take a Full backup of database and then restore on target server with restore statements (use move to if required).
You can detach database on 2005 server and then copy files to new server and attach those files.
if any users are connected - disconnect all users from the source database before trying to copy it
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.


Check proxy account of SQL Server agent.
Please see the attachment for more details.

scorptecAuthor Commented:
thank you for your reply,
Will the login info on that database move as well?
We had that account setup a while ago and it was a courier company. they never provided the login information that account is using, thats why its important for use to keep that login info.
scorptecAuthor Commented:
Ok sweet, i will give it a go and let you know
thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.