Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transfer SQL DB problems (SQL2000 SP3)

    I have an Access adp application that I'm having trouble transferring to another machine. The target machine is running the same version/patches of SQL Server as the development machine. I'm using the Transfer Database tool from Access XP's Tools menu. The transfer fails at 99% complete, while the process is copying extended properties. Microsoft can verify problems with the "wizard" but so far offers no solution. Has anyone run into this before and managed a solution?

    Thanks,

    Keith

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Transfer SQL DB problems (SQL2000 SP3)

    Are you using the SQL Server 2000 Desktop Engine that comes with Office XP, or do you have a full developer or production version of SQL Server on you second PC? If you have the latter, the Enterprise Manager has the full Import/Export facility that can be used to move SQL Server databases from one server to another. About the only other option is to do a SQL Backup and then do a restore to the new PC.
    Wendell

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer SQL DB problems (SQL2000 SP3)

    I have SQL Server 2k Developer Ed on the development machine and SQL Server 2k Std Ed on the Windows 2k Server. Unfortunately DTS (Enterprise Manager) requires a domain to function properly, and my client doesn't have a domain. Microsoft has indicated that the backup/restore when moving between servers can cause problems. I trust their claim, but some folks seem to have done this without incident.

    Keith

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Transfer SQL DB problems (SQL2000 SP3)

    Actually, the problems with restores usually focus on user definitions when you are using Integrated Security with SQL Server. But I am surprised that you can't make DTS work without having an NT domain controller defined - actually in a network there are lots of good reasons for running a domain, not the least of which is Integrated Security. But you should be able to do a backup to a device that is defined as a shared resource that can be accessed from both your development machine and your server, and then do a restore. However you may not want to use the wizard to do the backup, as you will need to change some things for the restore. Also since this is a newly developed database, you might want to consider just rebuilding the structure and the views and stored procedures by using the scripting facility, and simply build a new SQL Server database for production. (That assumes that you don't want to use any of your test data as live data in the production database.)
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •