| Author |
Topic |
|
ipirmohamed
Starting Member
1 Post |
 Posted - 2007-04-20 : 16:00:31
|
| am trying to copy a database fomr one server to another.. i keep getting errors and i dont know what i am doing wrong.. am using the sql management object method... first i was getting errors about sql server agent being disabled.. so i enabled it on both the servers... now i am getting the following error on the last step:===================================Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Copy Database Wizard)------------------------------Program Location: at Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.CreateAgentJob() at Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.CreatePackage()===================================heres the report:Performing operation...- Add log for package (Success)- Add task for transferring database objects (Success)- Create package (Success)- Start SQL Server Agent Job (Stopped)- Execute SQL Server Agent Job (Stopped)can anyone help... thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-20 : 16:09:50
|
| Just use backup/restore. Don't use the copy database wizard to copy databases. Backup the database on the source, copy the file over to the destination, restore on the destination. You'll get everything that is stored in that database.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
JB Kid
Starting Member
2 Posts |
Posted - 2008-12-18 : 14:24:55
|
| I am having the same problem so I tried to backup, copy the backup file to the destination server and restore. But when I try to restore, I get an error. The database is not on the destination server so I'm creating a new one when I restore. This is the error:sqlerror: directory lookup for the file "d:\sql7data\dealers.mdf" failed with the operating system error 2(The system cannot find the file specified.)This directory doesn't exist on the destination server so I added it. Then the next error was the same but that it couldn't find the log file. However, the log file is on a drive letter that doesn't exist on the destination server.I didn't think that should matter if I'm creating a new database. I tried to create the database first then restore to it, but that didn't work either saying it was restoring to a different database.I'm just trying to copy some databases from one sql server to another. I didn't think it would be this hard.My next attempt was going to be detaching the mdf & ldf files. Copying them to the destination server. Then reattaching. But, the source files are live and I can't detach in the middle of the day.Any ideas? Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JB Kid
Starting Member
2 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-06-02 : 10:02:30
|
| Tara: Why do you suggest to use backup/restore instead of the database copy wizard? I have to copy about a dozen databases and it's going to take a long time to run all the backups, copy them to the other machine, and then restoring them. Does the database copy wizard just not work correctly? It seemed like you could copy every part (logins, stored procedures, etc) of the database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-06-14 : 12:10:36
|
| It actually works through job and account running under it needs to have full permission in both servers. |
|
|
|
|
using Copy Database Wizard