Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    6

    Unanswered: SQL server migration

    hi,
    We have machine where SQL 2005 server is installed.Atleast 3 web applications are running on it with data size 22GB in all. We need to migrate all data/tables/groups/users from this server to another server.On target server, SQL server 2005 will be installed.After that this migration activity needs to be carried out.what are options available to migrate to target server?
    Microsoft migration tool? Any already existing stored procedure etc?which is preferred solution in such case?

    Thanks for help
    PD

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    backup from the source and restore to the target is the safest and cleanest method.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2008
    Posts
    6
    Hi.
    Thanks for reply.So do you sugget backup/restore procedure.I am thinking of following steps:
    1)Create users/groups/roles(they are few so I can create manually).
    2)perform backup
    3)On target perform restore.

    Regards
    PD

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    that should be fine.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    if these are sql authenticated logins, you may have orphan users you will need to fix.

    Lookup sp_change_users_login

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to reverse your steps. First, do your backup/restore, and then generate login scripts on target servers using references below, and run resulting scripts on the target.

    For SQL2K use http://www.databasejournal.com/img/sp_help_revlogin.sql

    For SQL2K/SQL2K5 - http://support.microsoft.com/kb/246133
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by tomh53
    if these are sql authenticated logins, you may have orphan users you will need to fix.

    Lookup sp_change_users_login
    Here's a little script I use (run in each db following restore), may have to adjust for SQL2005 (sysusers, syslogins):

    -- fix Users
    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




Posting Permissions

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