none
SQL SERVER 2005 Restore System Database on a server with different name?

    Question

  • Plan: migrate a current SQL 2005 SP3 server from Windows 2003 machine to a new Windows 2008 R2 machine

    1. first create windows 2008 R2 and install SQL 2005 SP3, due to both machine on line, new machine has to be a different name

    2. Backup system databases on current server,

    3. Restore system database on new server(different name) will this be an issue?

    4. back up all user databases and Shutdown current server

    5. Redirect LUN to new server

    6. Rename new server to current server name

    7. reattach all user databases on new server or has to restore from backup ?

    will this work?

    Friday, February 10, 2012 5:18 PM

Answers

  • Hi George,

    I think the plan is OK. You need to test it before you use it in production environment.

    Please refer:

    Considerations for Backing Up and Restoring System Databases: http://msdn.microsoft.com/en-us/library/ms190190(v=sql.90).aspx.

    How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008:  http://support.microsoft.com/kb/918992.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by George Zhou Tuesday, February 14, 2012 3:59 PM
    Monday, February 13, 2012 8:55 AM
    Moderator
  • I am sorry your management likes to apply wrong solutions.  Recovering from a database server crash is very different from an intentional migration. 

    As for password transfer, they transfer in hashed format so they are compatible across machines and versions.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    • Marked as answer by George Zhou Tuesday, February 14, 2012 3:59 PM
    Monday, February 13, 2012 7:57 PM
    Moderator

All replies

  • I almost never migrate system databases.  First, you can only restore to the exact same build as the backup.

    When I migrate servers, I have a checklist to move all the non-database objects.  I script the database moves, sometimes using log shipping if there are time and distance limitations.

    Finally I redirect clients to the new server.

    Here is a blog post on redirecting SQL connections.

    http://weblogs.sqlteam.com/geoffh/archive/2011/11/29/a-rose-by-any-other-name.aspx


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Friday, February 10, 2012 5:52 PM
    Moderator
  • thanks, Redirect clients is not an option in our case as management team doesn't agree, the option they give is what you should do when the server is crashed.

    when you script database move, will the login script created from old server works well on new server?(different OS), I am not sure about the password encrypted will be the same.

    Friday, February 10, 2012 6:48 PM
  • Hi George,

    I think the plan is OK. You need to test it before you use it in production environment.

    Please refer:

    Considerations for Backing Up and Restoring System Databases: http://msdn.microsoft.com/en-us/library/ms190190(v=sql.90).aspx.

    How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008:  http://support.microsoft.com/kb/918992.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by George Zhou Tuesday, February 14, 2012 3:59 PM
    Monday, February 13, 2012 8:55 AM
    Moderator
  • I am sorry your management likes to apply wrong solutions.  Recovering from a database server crash is very different from an intentional migration. 

    As for password transfer, they transfer in hashed format so they are compatible across machines and versions.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    • Marked as answer by George Zhou Tuesday, February 14, 2012 3:59 PM
    Monday, February 13, 2012 7:57 PM
    Moderator