Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: SQL 2000 to SQL 2008 R2

    Hi there,

    I have one old sql 2000 server and I backup the database and restore it to SQL 2008 R2 database. I can see all the tables and users but I do not know, how to move database user and logon's. Any help will be greatly appreciated. I am not a databse person.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are other answers, but the simplest solution would be:
    1. Remove the SQL 2008 R2 installation from the new server
    2. Install SQL 2000 on the new server.
    3. Restore ALL of the databases, including master and msdb.
    4. Re-install SQL 2008 R2 as an upgrade.
    This will get all of your databases, logins, and users onto the new server with as little fuss, muss, and bother as possible.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    On SQL 2000 run this query:
    Code:
    select 'create login [' + name + '] with password = ', + password + ' hashed, default_database = ' + dbname + ', sid = ', sid
    from syslogins
    Take one of the lines, and run the create login statement on the SQL 2008 R2 box. Test to make sure that the login is still valid (has the right password, can access the right database, etc.). Passwords in SQL 2000 were case insensitive, so you will likely have to reset the passwords in SQL 2008 R2, unless you used all lower case, or all upper case when the SQL 2000 logins were created.

  4. #4
    Join Date
    Sep 2013
    Posts
    1

    SQL 2000 to SQL 2008 R2

    You might want to take a look at the article that covers that problem: Migrate a SQL Server database to a newer version of SQL Server

    Some alternative solutions are explained, as well as how to use ApexSQL Diff and ApexSQL Data Diff to accomplish this.

    Disclaimer: I'm working for ApexSQL as a Technical Support Engineer

Posting Permissions

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