Solved

Copying a Database

Posted on 2011-09-15
8
1,331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 60

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 200 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 60

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 300 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 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