1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

How to overwrite a database with a restore without manually changing DB state?

Discussion in 'Microsoft SQL Server' started by foobar2devnull, Apr 29, 2011.

  1. foobar2devnull

    foobar2devnull New Member

    Joined:
    Nov 30, 2010
    Messages:
    97
    Likes Received:
    1
    Occupation:
    System and Backup Administrator
    Location:
    Belgium
    TSM Server 6.2.2.0 | TSM Clients 6.2.2.0 | TDP MS SQL 5.5.4.2

    Dear,

    Is there a way to restore over an existing database without first having to set the database to "single user mode" in the MS SQL Server Management Studio? By changing the state of the database, all users get disconnected and the database can be overwritten.

    I can restore a DB to another location, but I can't figure out how to overwrite a database with a restore without first having to change the state of the database manually. Shouldn't the TDP do that for me?

    Thanks for your help!
     
  2.  
  3. zsoltesz

    zsoltesz New Member

    Joined:
    Feb 26, 2008
    Messages:
    107
    Likes Received:
    4
    Location:
    Hungary
    Or, before the restore you can kill the sessions that connected to the database.
     
  4. foobar2devnull

    foobar2devnull New Member

    Joined:
    Nov 30, 2010
    Messages:
    97
    Likes Received:
    1
    Occupation:
    System and Backup Administrator
    Location:
    Belgium
    Hi,

    Thanks for your answer. Putting the DB in single user mode does in effect, kill all connections to the DB. I was just wondering why the TDP does not do this? Are you saying that before restoring a DB one should manually kill all connections to it from the SQL Manager?
     
  5. foobar2devnull

    foobar2devnull New Member

    Joined:
    Nov 30, 2010
    Messages:
    97
    Likes Received:
    1
    Occupation:
    System and Backup Administrator
    Location:
    Belgium
    Could someone confirm this for me? Do I really have to manually kill all connections to the DB before overwriting it? The TDP will not do this even though there is an option to overwrite?

    Any feedback is welcome! :)
     
  6. Ash777

    Ash777 New Member

    Joined:
    Nov 19, 2009
    Messages:
    14
    Likes Received:
    0
    I also have to kill connections to the DB with the SQL management studio when restoring over an existing database, Otherwise restore fails with database in use.
    I doubt the TDP does this.
     
  7. Tyrese

    Tyrese New Member

    Joined:
    Oct 1, 2011
    Messages:
    5
    Likes Received:
    0
    I am wondering this myself. Does anyone know? Do I really have to change the state of the database manually? If someone can help me figure this out I would really appreciate it. I really need to restore a database for a payroll utility that I am developing. Thank you very much.
     
    Last edited: Oct 30, 2011

Share This Page