Solved

Issue copying SQL database from 2005 to 2008r2

Posted on 2010-11-24
6
297 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:scorptec
6 Comments
 
LVL 8

Expert Comment

by:Som Tripathi
ID: 34210860
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
 
LVL 8

Expert Comment

by:Julianva
ID: 34211654
if any users are connected - disconnect all users from the source database before trying to copy it
0
 
LVL 3

Expert Comment

by:GSGDBA
ID: 34214129
HI,

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


CopyDB.doc
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:scorptec
ID: 34214763
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
 
LVL 8

Accepted Solution

by:
Julianva earned 500 total points
ID: 34215503
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
 

Author Comment

by:scorptec
ID: 34215576
Ok sweet, i will give it a go and let you know
thank you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now