Post reply

Kill all USer Sessions before starting to restore a Database

  • July 8, 2015 at 9:41 am

    #309813

    Comments posted to this topic are about the item Kill all USer Sessions before starting to restore a Database

    Thanks.

  • August 5, 2015 at 2:24 am

    #1817399

    USE [master]

    GO

    ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    RESTORE DATABASE MyDatabase FROM ...

  • August 5, 2015 at 4:35 am

    #1817426

    Is it worth mentioning that sysprocesses is a deprecated table?

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • August 5, 2015 at 6:30 am

    #1817472

    Thanks for the useful script. We do restores into testing environments quite often and run into the problem of having to find out who is in that environment and have them exit.

  • August 5, 2015 at 9:27 am

    #1817596

    Unfortunately, you have to go back before SQL 2005 for this to be a "useful" script, since ALTER DATABASE provided a vastly better way to do this.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 6, 2015 at 1:12 pm

    #1817962

    This will not kill the sessions that are running queries from another database on the same instance.

  • August 27, 2015 at 6:51 am

    #1822736

    Why not ?

    USE master

    go

    ALTER DATABASE <your database name> SET OFFLINE WITH ROLLBACK IMMEDIATE

    go

    ALTER DATABASE <your database name> SET ONLINE

    go

Viewing 7 posts - 1 through 6 (of 6 total)

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