[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Copying a Database

Posted on 2011-09-15
8
Medium Priority
?
1,558 Views
Last Modified: 2013-11-30
I'm trying to copy a database from a remote server. When going through the Copy Database Wizard, I'm getting a Execute SQL Server Agent Job error.  The error states " The job failed.  Check the event log on the destination server for details".  See below:

Performing operation

- Add log for package (Success)

- Add task for transferring database objects (Success)

- Create package (Success)

- Start SQL Server Agent Job (Success)

- Execute SQL Server Agent Job (Error)
      Messages
      * The job failed.  Check the event log on the destination server for details. (Copy Database Wizard)
      
I can't seem to find what causing this problem.  Am I using the correct approach?  I just need to copy this database to my local machine so I can use as a test database.  Thank in advance.
0
Comment
Question by:Tyecom
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36546237
Hi.

First, not all errors are a reflection that the database didn't copy, but I am sure you have actually checked at this point. For example, you may get an error on Logins as system may not recognize OLD-SERVER\UserName or you opted not to copy Logins, etc.

See if this helps to verify process you are using:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_7251-Database-Copying-Wizardry.html

As far as debugging, have you done what the error message says? i.e., what is in the Event Log on the destination server?

Post that and can see what is going on if you believe the steps you followed are correct after reviewing my article.

Regards,
Kevin

0
 
LVL 2

Expert Comment

by:John_Bon
ID: 36547137
It may be easier and faster to:
1. create a backup on the remote server.
2. Create a Mapped Drive to the Remote Server Database Backup location.
3. Copy the backup file from the remote server to the destination server.
4. Restore the backup on the destination server.
0
 
LVL 2

Assisted Solution

by:John_Bon
John_Bon earned 800 total points
ID: 36547174
If the above is not an option, you may alternatively, use the Script Database Wizard.
Generate  Scripts - Schema and Data
1.  Right Click on Database, Select Tasks  >, Click on Generate Scripts
2.  In the Set Scripting Option Tab, Click on the Advanced Button.
3.  Advanced Scripting Options, Types of data to script, Select Schema AND data.
GenerateDatabaseScripts-SchemaAn.png
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 2

Expert Comment

by:Akeener
ID: 36548285
In our enviroment we do a backup of the DB, copy the backup file to the Test Server then Restore the DB. If you need, on the Test Server create a DB, then restore the backup file to the DB you jsut created setting the Restore to overwrite.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36549127
We do restores for development also, but I saw this "copy a database from a remote server" so thought that may not be an option, but -- more importantly -- Copy Database should work as I have used that to copy as well and was very quick. It brought over other objects, so was useful when I did use it.
0
 
LVL 2

Expert Comment

by:Akeener
ID: 36549732
I find the Copy feature to be a little problematic. But different results are to be expected.
0
 
LVL 5

Accepted Solution

by:
VENKAT KOKULLA earned 1200 total points
ID: 36585288
Please got through the below points might it will help out:
1)      First go to the path where that particular Database files exist (MDF and LDF). Stop the SQL services.
2)      Then copy the Database files (MDF and LDF) to particular tape drive or a shared path of the destination server where you would like to restore the database.
3)      After copying the Database files (MDF and LDF) to the destination server then check the file properties as they have given proper permissions  or not.
4)      If everything went fine, then attach the files to the SQL instance which you would like to have the database
5)      After attaching the Database files (MDF and LDF) please provide the proper path for the files before clicking on the OK button.
6)      Check the space on the drive while attaching with having proper permissions.
7)      After restoring the DB completely check the DB options as they are per your requirement or not.
0
 

Author Closing Comment

by:Tyecom
ID: 36952402
Following the above instructions corrected my problem.  I also had to make sure I was a database sysadmin.  Thank you very much for your help!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

590 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