[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Copying a Database

Posted on 2011-09-15
8
Medium Priority
?
1,436 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

656 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