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
scorptecAsked:
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
then
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.

0
 
Som TripathiDatabase AdministratorCommented:
Take a Full backup of database and then restore on target server with restore statements (use move to if required).
-OR-
You can detach database on 2005 server and then copy files to new server and attach those files.
0
 
JulianvaCommented:
if any users are connected - disconnect all users from the source database before trying to copy it
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
GSGDBACommented:
HI,

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


CopyDB.doc
0
 
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.
0
 
scorptecAuthor Commented:
Ok sweet, i will give it a go and let you know
thank you
0
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.