Solved

Copying a Database

Posted on 2011-09-15
8
1,322 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 59

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 59

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot table 2 42
sql update 2 34
Many to one in one row 2 35
Split string into 3 separate fields 5 18
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

680 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