Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: Is restore or Detach/Attach better?

    Hello, everyone:

    I want to move a database instance (about 50 user databases) to another server, both are running SQL Server 2000. Which method is better, retore from backup files or detach/attach? Some papers said restore amybe cause incorrect login and password transfer. Is it true?

    Thanks

    ZYT

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    All databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.

  3. #3
    Join Date
    Mar 2003
    Posts
    223
    MCrowley:

    Thanks for the reply. Can DTS transfer login with SID? What I am concerning is to transfer login from old server to new one.

    Thanks

    ZY

    Quote Originally Posted by MCrowley
    All databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Google "sp_help_revlogin" for some help to transfer sql accounts

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    when the db is restored run (from the restored db)

    If you do not bring logins over and create them manually

    -- to view broken users
    sp_change_users_login 'report' -- will give you list of "broken" users.

    you then run sp_change_users_login auto_fix, 'username' for each user:

    I just use this little sql script after restoring a db (make db current):

    set nocount on
    declare @v_dbuser varchar(255)
    declare @sql nvarchar(255)
    declare c1 cursor for
    select a.name from sysusers a, master..syslogins b
    where a.name = b.name
    open c1
    fetch c1 into @v_dbuser
    while (@@FETCH_STATUS <> -1)
    BEGIN
    print 'Fixing User ' + @v_dbuser
    set @sql = 'sp_change_users_login ''auto_fix'',' + @v_dbuser
    exec sp_executesql @sql
    fetch c1 into @v_dbuser
    END
    CLOSE C1
    DEALLOCATE C1

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Nice little script! I have always done that manually...now next time we create or sync a dev database, I'll whip out your little script and be the instant envy of my peers!

    Yeah, I know...I need a new set of peers.

    One could probably also just add a "check every database" loop outside of this one and handle all the databases on a new server at once...though I suppose that is a pretty infrequent occurrence and probably not a justifiable expenditure of effort relative to the creation of a script to do it.
    Last edited by TallCowboy0614; 11-07-07 at 17:34.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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