locked
How to retrieve the logins info from the .bak files RRS feed

  • Question

  • Hi,

    I have the master and msdb backup files of a broken server, as well as the .bak files of all other non system databases.
    Now, in an online sql server, how do I retrieve the logins information from those .bak files?
    Thanks for helpl

    Jason

    Monday, October 5, 2015 2:17 AM

Answers

All replies

  • Hi Jason,

    Try restoring these backups to a dummy instance of SQL Server. See the links below

    http://mssqltrek.com/2011/07/19/restoring-master-and-msdb-databases-system-databases/

    https://msdn.microsoft.com/en-us/library/ms190679.aspx

    Once they are restored. You can use the link below on getting all the login information using sp_help_revlogin stored proc..

    https://support.microsoft.com/en-us/kb/918992

    Hope this helps

    Thanks

    Bhanu

    • Proposed as answer by Charlie Liao Monday, October 5, 2015 3:29 AM
    • Marked as answer by JasonHuang8888 Wednesday, October 7, 2015 8:40 AM
    Monday, October 5, 2015 3:23 AM
  • Make sure you read this too

    http://dba.stackexchange.com/questions/104181/prevent-logins-loss-when-restoring-a-database


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by JasonHuang8888 Wednesday, October 7, 2015 8:40 AM
    Monday, October 5, 2015 6:40 AM
  • Hi,

    I have the master and msdb backup files of a broken server, as well as the .bak files of all other non system databases.
    Now, in an online sql server, how do I retrieve the logins information from those .bak files?
    Thanks for helpl

    Jason


    By restoring copy of Master database. Use this link to restore master database. After master is restored restore other system database and then user databases.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by JasonHuang8888 Wednesday, October 7, 2015 8:40 AM
    Monday, October 5, 2015 11:40 AM
  • Thank you guys for your help!
    Actually, I just need the logins information (login/pwd) of the broken sql server.
    And I don't want to use the broken server's master to replace to online server,
    because the online server has some logins other than the broken server.

    If I restore the master.bak to some newly created database like TaiMaster,
    is it possible to retrieve the logins from the TaiMaster?

    Thanks for help again!

    Jason

    Tuesday, October 6, 2015 1:23 AM
  • Hi Jason,

    I haven't tried this so far. A same question was asked in the link below and the suggestions are that we can do it.

    http://serverfault.com/questions/415615/can-i-restore-master-to-a-different-name-as-a-user-database

    You can give it a go.

    Remember to run the steps in the link below against the new master DB to get the login info

    https://support.microsoft.com/en-us/kb/918992

    Thanks

    Bhanu

    • Marked as answer by JasonHuang8888 Wednesday, October 7, 2015 8:40 AM
    Tuesday, October 6, 2015 8:32 PM
  • Hi Jason,

    To add more to my above comment, I have just tried to restore the master database on to the same server with a different name and it works with no issue at all. Use the Syntax below for DB Restore. Please don't forget to edit the T-SQL statement below to your requirements.

    USE [master]
    RESTORE DATABASE [MyBackup] FROM  DISK = N'C:\backup\master.bak' WITH  FILE = 1,  MOVE N'master' TO N'C:\backup\master_new.mdf', 
    MOVE N'mastlog' TO N'C:\backup\mastlognew.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
    GO

    Once the DB is in place run the steps against the new master DB as per this link . If you observe the scripts in this link this will start with USE MASTER. Replace this with the new master DB name and run the statements. Once this is done, you can execute the SP_help_revlogin stored proc on this new db to generate the login information as shown below

    Hope this helps

    Thanks

    Bhanu




    • Edited by bhanu_nz Tuesday, October 6, 2015 9:00 PM
    • Marked as answer by JasonHuang8888 Wednesday, October 7, 2015 8:41 AM
    Tuesday, October 6, 2015 8:53 PM