Solved

Issue copying SQL database from 2005 to 2008r2

Posted on 2010-11-24
6
299 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
cannot connect to sqlserver 8 27
get column names from table in vb.net 8 28
Help creating a spatial object in SQL Server 4 21
SSRS Enable Remote Errors 4 25
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

776 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