Re: Restore master?

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/20/05

  • Next message: FRR: "Re: Restore master?" 
    Date: Thu, 20 Jan 2005 17:44:59 -0500

    The Logins would come with Master but the users are stored in each db. You
    may need to run sp_change_users_login on each one . Have a look here:

    http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
    Passwords Between SQL Servers
    http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
    Restore
    http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
    users
    http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
    Errors After Restoring Dump
    http://www.support.microsoft.com/?id=240872 How to Resolve Permission
    Issues When a Database Is Moved Between SQL
    http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
    http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
    to a New Location with Detach/Attach
    http://support.microsoft.com/?id=221465 Using WITH MOVE in a
    Restore
    Servers
    http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
    Restoring a .mdf
    http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
    for SQL Server 

    -- 
Andrew J. Kelly  SQL MVP
"FRR" <FRR@discussions.microsoft.com> wrote in message 
news:AE94C44C-4F74-4547-85BB-76569BC0571A@microsoft.com...
>I restored the master, then the production db, but the logins did not come
> over.  I beleive all the db users are orphaned (about 200).  I didn't get 
> any
> error message, etc...  What do I do now?  The os and service packs are
> identical (server 2k3 and sql 2000).  Thank you.
>
> "Andrew J. Kelly" wrote:
>
>> Depends on how much info you have in Master that you need.  Personally I
>> prefer to add what I need to the new master instead of restoring the old 
>> as
>> there tends to be less chance you will get junk carried over. Plus if you
>> have different service packs you have to be careful.
>>
>>
>> -- 
>> Andrew J. Kelly  SQL MVP
>>
>>
>> "FRR" <FRR@discussions.microsoft.com> wrote in message
>> news:B775DEAD-BDD1-41B9-81FD-D2A684FA00F0@microsoft.com...
>> > Is there any advantage to restoring the master to a backup server, or 
>> > can
>> > I
>> > just bring over the logins/passwords?
>>
>>
>>
  • Next message: FRR: "Re: Restore master?"

    Relevant Pages

    • Re: User already exists in the database error
      ... I suggest you create the logins *with the correct SID* before restoring. ... I need to have the users already created on the SQL server yes? ...
      (microsoft.public.sqlserver.server)
    • Re: Logins between SQL Databases on diff. servers.
      ... If they are NT logins and on the same domain you shouldn't have to do ... Errors After Restoring Dump ... for SQL Server ... > So how can I move the logins first with its passwords and SID? ...
      (microsoft.public.sqlserver.security)
    • Re: Restoring Master and msdb databases
      ... note that you need to stop the SQL Server Agent service when restoring the MSDB database ... The SQL Server needs to be in Single-User mode, not just the master ...
      (microsoft.public.sqlserver.setup)
    • Re: Disaster Recovery
      ... Errors After Restoring Dump ... for SQL Server ... > After i restored the master, msdb and user databases, do i have to drop ...
      (microsoft.public.sqlserver.setup)
    • Re: Master database must never be the default database for any login
      ... Is it possible I configure SQL Server to set a default database other than ... MASTER to any logins created automatically? ... You can change for existing logins, ...
      (microsoft.public.sqlserver.security)
    • Quantcast