Copy SQL 2000 Database

  • Hi,

    I have a hosted SQL Server 2000 database which I copy locally as a backup.

    Originally I was using Enterprise Manager and I imported the entire database and saved the import as a DTS package which I could then run whenever I liked.

    Now with my new PC on which I have installed SQL Serer 2005 I am trying to accomplish the same thing however everything seems different.

    1) I tried to import the database but the import option only seems to allow tables and views now ignoring the rest of the database.

    2) I then tried to copy the database but the copy wizard failed when it tried to list the databased on my hosted server because my login only has access to my own database and the wizard seems to be trying to pull some information about all databases across.

    Has anyone got any ideas about how I can effectively take a local copy of my hosted 2000 database using the SQL Server 2005 management tools?

    Thanks,

    Dale

  • You should be able to detach it from 2000 and reattach on 2005.

    Alternatively, you can make a backup in 2000 and restore from the backup on 2005. Then you'll have it in both places.

    ---------------------------------------
    elsasoft.org

  • Hi - Due to the fact that it is hosted I do not have access to do either of those things.

  • Well, we don't know what access you have.

    How about the import wizard?

  • As already mentioned the import wizard *appears* to only allow me to import tables & views.

  • Hi.

    Well. Generate the Sql script of your database including tables,views,indexes, PK, FK, stored procedures, and what are the things you need. Run it in SQL Server  2005 Management Studio. Then import all data to your database using dts. Hope it helps

    Prema

     

  • With copy database wizard you can

    "Pick a source and destination server.

    Select one or more databases to move or copy.

    Specify the file location for the databases.

    Create logins on the destination server.

    Copy supporting objects, jobs, user-defined stored procedures, and error messages.

    Schedule when to move or copy the databases. "

    Seems to me your missing the supporting objects etc. step. Remember you can't copy the master or msdb databases, so you're 'just' getting the database.

    Check out 'Using the Copy Database Wizard ' in BOL.

  • Thanks for your help, again as already mentioned in the original post when I try and use the copy wizard it fails because it tries to list the available databases on the hosting server and my login doesn't have sufficient access for it to complete that step. If only it would copy the database that I clicked copy on rather than trying to list all of them!

  • Any way to automate those 2 steps? And where are the DTS options in 2005?

  • The SSIS Import wizard in 2005 is only for tables and views.  Why don't you just migrate your 2000 DTS to 2005 and use the exisiting package?

  • I'd love to - how do I do that?

  • "hen I try and use the copy wizard it fails because it tries to list the available databases on the hosting server and my login doesn't have sufficient access"

    Right, if you don't have access to any of the other databases, there's no way you can get objects from master or msdb.

    If I were you I'd request a backup from your hosting company - you are paying them after all - to get a starting point. From then on you'll only need to update the data anyway in most cases.

  • The package migration wizard is a starting point, you can also try the Execute DTS 2000 Package task, but there's no gaurantee it'll work as some functions have no mirror in integration services.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply