how to get a mirrored db online and mirroring again without a restore? RRS feed

  • Question


    We have a number of db's mirroring between production and DR.   I want to introduce an automated procedure for synchronising Logins between production and DR servers.  However, when creating a Login, if there are user mappings involved, the user db in question has to be online.  The only real problem in this is that once I bring a DR db online, when I've finished syncronising the Logins, I need to get the DR db back into 'norecovery' mode so I can start mirroring that db again WITHOUT doing an actual database restore (just impractical for us to consider).   Is there any way to put a db into 'norecovery' mode without actually restoring the database?



    Wednesday, October 1, 2008 4:31 PM

All replies

  • Maybe I'm missing something, but why do you need to use user mappings? Just create the logins on DR using the appropiate SID (same SID as in production) and the user in the mirrored database will be automagically mapped.
    Thursday, October 2, 2008 6:32 AM

    Maybe you're right but when I tried creating the logins, I got an error because the user db wasn't online.   That may have been because the default database was the user db that was offline.   If that's the case, I could just change the default db to Master.   However, I vaguely remember that Master as the default db for user Logins isn't good practice?   I guess it would be ok for DR though. 


    As for SIDs, why would I have to worry about them?   In the past, I've just created a Login on a server and when a db is restored to that server it always seems to sort out the db user mappings in any case.



    Thursday, October 2, 2008 9:48 AM
  • I got it, I didn't figure you are using the default database option for those logins. It seems silly to me that the database has to be online at the moment the login is created, but that's the way things are. You could make default database tempdb, but if you have applications that rely on the connection to go to the right database, of course they will break untill yo change the default database back to the right one.
    The SID issue is only for SQL logins, if your logins are Windows logins, they get the right SID from the Active Directory.
    Saturday, October 4, 2008 7:31 AM