Home / server / Questions / 46849
In Process
MGOwen
Asked: 2009-07-28 23:43:09 +0800 CST

SQL Server 2000: Database copy wizard - Claims files already exist (they don't) or there is not enough disk space (there is)

  • 772

I am trying to copy some SQL Server Agent Jobs from one server to another (both using SQL Server 2000).

I found out how to create a script that creates them all, but these jobs seem to have dependencies, such as DTS packages, and I need to copy everything (and don't really know what I'm doing, let's be honest).

So I tried the built in Database Copy Wizard (in Enterprise Manager, remember that?). But as you can see in the screenshots linked below, it claims the destination .mdf and .ldf files already exist (they don't), or there is not enough disk space (there is).

Screenshot 1

See how next to both rows, there is a green tick in status? But then, further down, there is a red cross. The screen is confusing: is there an error or not?

If you click on Modify (sorry, silly rules about links when you have low rep):

http:// img16.imageshack.us/img16/9439/cdwerror2.png

Once again, I have all green ticks, but then those nasty red crosses down the bottom. Which is it saying? Or is it saying both?

So I continued anyway... and after it's done, the following failures are reported on screen:

CDW Logins Task Step: fails, but this is OK - it just couldn't transfer the administrator login (I think that's not a problem, becasue it's that machine's local windows admin logon).

CDW MSDB Task Step: fails, objects scripted OK, but can't be transferred, e.g.:

Transfer MSBD  Update_Daily_Person_Set (SQL SERVER AGENT JOB)

 Step Error Source: Microsoft Data Transformation Services (DTS) Package
 Step Error Description:Unspecified error

 Step Error code: 80004005
 Step Error Help File:sqldts80.hlp
 Step Error Help Context ID:1100

I'm not sure where to find sqldts80.hlp (anyone know?) and googling for the 80004005 SQL Server error reveals that it could be any one of about a hundred different things (literally) depending on the rest of the error message (none was given in this case).

CDW Databases Task Step:

The actual copying of the .mdf files failed. One DB wouldn't attempt to copy because it had active connections - I assume I can just disconnect those to solve that part.

Thanks for reading this far!

I tried allowing "Everyone" write permissions on the destination folder, no change. Any other ideas?

sql-server database

1 Answers

  1. I've never used the copy database wizard to copy a system database, but it's possible that it won't work for that. I know that task has been flaky for me and I've rarely used it.

    Are you moving servers? msdb has system information, and if you don't intend to replace all that info on the new server, I wouldn't necessarily transfer it.

    Moving DTS packages is a pain, but you can open them and save them to a new server (save as), or you can use something like this http://www.sqldts.com/204.aspx

    For jobs, those are simple script files, and there aren't likely many dependencies on logins for them. If there are, sp_help_revlogin will move those to a new server through a script.

    I know it seems complicated, but it's not that bad, and the manual time is probably lost here in researching the issue.

    The the transfer, you have a destination folder, but there is a path stored in the logical files, and it's possible that it matches up with the new server. Are the installs default? Same paths for msdb.mdf on both serers?

    • 2