Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Copying Database from One Server to Another

    We recently got a new SQL Server 2000. I'm not really a SQL/Network admin but I was tasked to migrate some of our databases in the SQLSVR7 to SQLSVR2K.

    I tried using DTS EXPORT but getting errors. Is there a better way to do this?

    Any info would be appreciated.

  2. #2
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    11
    Doing an sp_detach_db on your SQL7 box followed by an sp_attach_db on your SQL2000 box is undoubtedly the easiest & quickest way to migrate your DB, but you will end up with orphaned users in your SQL2000 DB if there were an users in the DB (other than the dbo user, although even that can get orphaned). The orphaned users can be fixed with sp_change_users_login.

    Another easy way to do it is by backing up your SQL7 DB and then restoring that backup file to a DB on your SQL2000 box. But, once again, will probably result in orphaned users again.

    The reason these two methods work is because both the restore & the sp_attach_db will "convert" the DB format, if necessary, as they go (this will only work for SQL7 to SQL2000 boxes, SQL65 upwards has to go through a DTS package). At least from memory that's how it works (it's been over 3 years since I did it).

    DTS is another quite reasonable option - all the DB upgrade wizard does is create a DTS package and run it. The DTS package (once again from a 3+ year old memory) just has a single "copy database" object, or something like that. Personally, I'm not a great DTS fan - I'm more of a T-SQL purist.

    Hope that helps.
    mike hodgson
    database administrator
    mallesons stephen jaques
    http://www.mallesons.com

    'It is a far, far better thing that I do, than I have ever done; it is a far, far better rest that I go to than I have ever known.'

  3. #3
    Join Date
    Jul 2004
    Posts
    191
    You may have to script all of the database, and bcp in/out the data.

    This is very timely.

    As for the DTS did you click Copy Objects and data betweens SQL Server database?

    Lystra

  4. #4
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by elminster
    Another easy way to do it is by backing up your SQL7 DB and then restoring that backup file to a DB on your SQL2000 box. But, once again, will probably result in orphaned users again.
    I tried the RESTORE DATABASE option. I copied a complete backup from the SQL7 into a directory of the SQL2K. When I initiate the restore, I get a "Device activation error" (pls see attachment) and is asking me to use "WITH MOVE".

    TIF
    Attached Thumbnails Attached Thumbnails RestoreError.gif  

  5. #5
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by milan1115
    You may have to script all of the database, and bcp in/out the data.

    This is very timely.

    As for the DTS did you click Copy Objects and data betweens SQL Server database?

    Lystra

    TFYI

    What do you mean by "script all of the database"? How can I do that?

    Yes, I have that option on a the DTS. It gets an error message pointing to another database (with no owner when I run the sp_helpdp) and the DTS is never completed. I get all the tables but I'm missing the views and all sprocs.

  6. #6
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    11
    If you have a skim through SQL Books Online you'll see how to do the restore properly (with the MOVE & REPLACE options). To help you out a bit I just whipped up this SQL batch for you to explain what to do. (I haven't actually executed this batch so it might contain syntax errors (typos) but I can't see any just glancing at it.)

    Code:
    -- Create the DB
      create database MySQL2000DB
      on
      	(
      	name = 'MySQL2000DB_Data',
      	filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Data.mdf',
      	maxsize = 100MB,
      	filegrowth = 25MB
      	)
      log on
      	(
      	name = 'MySQL2000DB_Log',
      	filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Log.ldf',
      	maxsize = 50MB,
      	filegrowth = 10MB
      	);
      
      -- Restore from the SQL7 backup
      restore database MySQL2000DB from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\MySQL7DB.BAK' with
      	replace,
      	move 'MySQL7DB_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Data.mdf',
      	move 'MySQL7DB_Log'  to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Log.ldf',
      	recovery;
      
      -- Change the logical file names
      use MySQL2000DB;
      alter database MySQL2000DB modify file (name = 'MySQL7DB_Data', newname = 'MySQL2000DB_Data');
      alter database MySQL2000DB modify file (name = 'MySQL7DB_Log',  newname = 'MySQL2000DB_Log');
      
      -- Fix up the orpaned users as reported from sp_change_users_login
      exec sp_change_users_login 'report';
      go
    After you get the list of orphaned users you need to run
    Code:
    exec sp_change_users_login 'auto_fix', '<username>'
    in the MySQL2000DB for each of those users listed when you ran sp_change_users_login 'report' where the '<username>' is the bit you change for each user (not sure how obvious that was to you).

    Cheers,
    mike hodgson
    database administrator
    mallesons stephen jaques
    http://www.mallesons.com

    'It is a far, far better thing that I do, than I have ever done; it is a far, far better rest that I go to than I have ever known.'

  7. #7
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Thumbs up

    Thanks for the script. Although I was not successful when I run it, it did point me to the right direction that resolved my problem.

    So basically, I used the RESTORE DATABASE tool of the SQL Enterprise Manager. However, I was missing the part where I need to change the path within the physical file name.

    Thanks again!
    Attached Thumbnails Attached Thumbnails RESTORE.jpg  

Posting Permissions

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