Post reply

database single user mode or multi user mode

  • May 20, 2011 at 10:17 am

    #250506

    Today I tried to restore a database, I happened to have a couple of questions about this to clarify.

    1. For both sql 2005, or/and sql 2008, does it still need no other user connection to database in order to restore?

    2. Today I tried first to restore a database, I first setup it to single user mode in ssms, then I did a restore from a backup file that comes from production server, then it restores successfully and it changed itself back to multi user mode, I thought I should either change it to multi user mode in ssms or using sql: alter database db-name set MULTI_USER

    But I didn't do that, it changed by itself, why is that?

    Thank you

  • May 20, 2011 at 10:23 am

    #1327817

    sqlfriends (5/20/2011)

    Today I tried to restore a database, I happened to have a couple of questions about this to clarify.

    1. For both sql 2005, or/and sql 2008, does it still need no other user connection to database in order to restore?

    2. Today I tried first to restore a database, I first setup it to single user mode in ssms, then I did a restore from a backup file that comes from production server, then it restores successfully and it changed itself back to multi user mode, I thought I should either change it to multi user mode in ssms or using sql: alter database db-name set MULTI_USER

    But I didn't do that, it changed by itself, why is that?

    Thank you

    The database can have no user connections when you restore it.

    A easy way to do that is to set it offline to kick everyone out and prevent them from re-connecting.

    alter database [MyDatabase] set offline with rollback immediate

    When you restore a database, it goes to the mode it was in at the time of the last backup you applied.

  • May 20, 2011 at 10:38 am

    #1327824

    Thanks.

    when you say, When you restore a database, it goes to the mode it was in at the time of the last backup you applied.

    Does this mean: if the backup copy of the database is in multi -user mode, the restored db from it will be in multi_user mode too.

  • May 20, 2011 at 12:51 pm

    #1327912

    Yes. The restored database is exactly the same as the database was at time of backup. That's structure, data, settings, everything.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 20, 2011 at 3:08 pm

    #1328000

    Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

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