Solved

Copying a Database

Posted on 2011-09-15
8
1,298 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now