Post reply

Server change

  • May 9, 2011 at 8:33 am

    #235985

    Hi,

    We were using a virtual server with sql server 2005 enterprise eidition on it. Its a development server.

    We have 5 dbs on it. Lately, its not working upto the mark as it goes very slow, So we are changing to a new server.

    So, now My task is to install sql server on it and copy all the dbs,objects,logins from the virtual sever.

    I have many queries:-

    So, after installing sql server, should I just restore the dbs with the backup files from virtual server?

    Wat about the logins at the server level, how can i import them?

    Will the master,model,msdb dbs work same if i just restore the .bak files of them?

    and also will all the logins at db level and at server level will hav same roles and permissions which they had on virtual server.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • May 9, 2011 at 8:40 am

    #1321752

    for migrating logins, there's a script directly from microsoft you'll want to search for:

    sp_help_revlogin

    there are different versions for SQL 2000 and sql 2005, maybe a different one for 2008/2008R2, which is why i'm not pasting it here without a bit of research;

    it basically copies the encrypted, binary string of the password into the resulting script to create the logins on the new server;

    that way no passwords need to be exposed or changed right away due to the migration.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 9, 2011 at 11:16 am

    #1321883

    Should the logins be transferred before or after the restoration of system and user dbs.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • May 9, 2011 at 11:20 am

    #1321887

    SKYBVI (5/9/2011)

    Should the logins be transferred before or after the restoration of system and user dbs.

    Regards,

    Sushant

    the order really will not make any difference, as long as both are completed before the first person tries to login;

    the important part is the sid in sys.server_principals being able to map to the the sid in sys.database_principals

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 9, 2011 at 1:59 pm

    #1321976

    When I try to restore the msdb db, it says exclusive access could not be obtained bcoz db is in use.

    When I try to restore the master db, it says it can be done only in single user mode..

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • May 9, 2011 at 2:06 pm

    #1321979

    there's no need to restore master on another server if you are migrating logins.

    you would only restore master on the same server if you were doing disaster recovery.

    simply script the logins and attach /restore each of your databases as needed on the new server.

    The same thing goes for the jobs that exist in the previous msdb database; all the jobs are server specific, so even if you got exclusive access and restored msdb, the jobs will all fail.

    script out the jobs and mail settings, edit the script to have the correct server name, and run the resulting scripts on the new server.

    There are a lot of script contributions to script out the jobs; just look in the scripts section here on SSC.

    Additionally, double check and make see if there were even any jobs on that old virtual server; you may not need to migrate anything at all.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 9, 2011 at 2:32 pm

    #1321988

    okk.thanks..

    Now when iam trying to import the logins by sp_help_revlogin

    I am getting (for almost every user) :-

    Supplied parameter sid is in use.

    I think it is because the logins at the db level have same sid whcih is being created by the output script.

    What should I do?

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • May 9, 2011 at 2:34 pm

    #1321992

    sounds like they have already beein imported; maybe you ran the script twice accidentally.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 9, 2011 at 2:39 pm

    #1321994

    ohh

    But now If i refresh it, i found all the logins are created,

    So is it fine, that they are already created

    or there will be error when the user tries to log in?

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply