Post reply

Kill sessions before restore command

  • March 12, 2009 at 9:49 am

    #219050

    Can you tell me if or what should I add to the following script to make sure the script kills all the sessions (if any) on the database before beginning to run the following restore command?

    Thank you

    RESTORE DATABASE [test_db]

    FROM DISK = N'\\sharedrive_backup_200903120215.bak'

    WITH FILE = 1, KEEP_REPLICATION,

    NOUNLOAD, REPLACE, STATS = 10

    GO

  • March 12, 2009 at 9:54 am

    #958458

    ALTER DATABASE [Test_db]

    SET OFFLINE

    WITH ROLLBACK IMMEDIATE

    Kicks everyone out and takes the database offline. Then you can restore over without worrying if there's anyone connected.

    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

  • March 12, 2009 at 9:59 am

    #958466

    Thanks Gila, I DID IT! Now I remember you or someone else had already helped me with another script and had the set off command

    Thanks:)

  • March 12, 2009 at 11:48 am

    #958556

    this proc might come in handy: sp_kill

    usage is simply sp_kill YOURDBNAME

    kills all the sessions connected to the database you identify:

    [font="Courier New"]--enhanced 02/04/2005 to also list hostname

    CREATE PROCEDURE sp_Kill

    @DBNAME VARCHAR(30)

    --Stored procedure to Delete SQL Process

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @SPID INT

    DECLARE @STR NVARCHAR(50)

    DECLARE @HOSTNAME NVARCHAR(50)

    DECLARE @DBID INT

    CREATE TABLE #TMPLOG (

       SPID INT,

       ECID INT,

       STATUS VARCHAR(50),

       LOGINAME VARCHAR(255),

       HOSTNAME VARCHAR(50),

       BLK INT,

       DBNAME VARCHAR(30),

       CMD VARCHAR(100) ,

       RID INT,

    )

    SELECT @DBID=DB_ID(@DBNAME)

    IF @DBID IS NULL

    PRINT 'No database exists with the name ' +   @DBNAME + ', Check the Spelling of the db.'

    INSERT INTO #TMPLOG EXEC sp_WHO

    IF @@ERROR <> 0 GOTO Error_Handle

    DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG

      WHERE DBNAME LIKE @DBNAME

    OPEN CURPROCESSID

    FETCH NEXT FROM CURPROCESSID INTO @SPID

    SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID

    IF @SPID IS NOT NULL

      PRINT 'Spid Process Kill List For database: ' + @dbName

    ELSE

      PRINT 'NO Processes Exist to be killed on database ' + @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

      IF @SPID = @@SPID

        BEGIN

          PRINT 'Cannot kill your own SPID, skipping ' + CONVERT(VARCHAR,@spid) + ' - ' + @HOSTNAME

        END

      ELSE

        BEGIN

          SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)

          EXEC sp_EXECUTESQL @STR

          PRINT CONVERT(VARCHAR,@spid) + ' - ' + @HOSTNAME

        END

      IF @@ERROR <> 0 GOTO ERROR_HANDLE

      FETCH NEXT FROM CURPROCESSID INTO @SPID

    END

    Error_Handle:

    IF @@ERROR <> 0 PRINT 'Error killing process - ' +  CONVERT(VARCHAR,@spid) + ' - ' + @HOSTNAME

    DROP TABLE #tmpLog

    SET NOCOUNT OFF

    END

              

          

          

    [/font]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 13, 2009 at 7:28 am

    #959083

    Thank you so much Lowell, do we call this procedure before restore command in another SSIS step or cna we just incorporate it in the same?

    thanks a lot for the help

  • March 13, 2009 at 7:37 am

    #959094

    eashoori (3/13/2009)

    Thank you so much Lowell, do we call this procedure before restore command in another SSIS step or cna we just incorporate it in the same?

    thanks a lot for the help

    Well it's just a tool; i call it whenever i want everyone off of a database, and damn the consequences (If Accounting calls and says they were in the middle of Payroll, and got kicked off, so noone gets a paycheck this week, well...there may be consequences...use it when necessary.)

    In your case, I think you want it as a step just before the restore, so i would call it just prior to that, so you can add it to your process, but it's handy to have for other purposes in the future.

    stick it in the master database, obviously.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 16, 2009 at 6:51 pm

    #960718

    eashoori (3/12/2009)

    Can you tell me if or what should I add to the following script to make sure the script kills all the sessions (if any) on the database before beginning to run the following restore command?

    Thank you

    RESTORE DATABASE [test_db]

    FROM DISK = N'\\sharedrive_backup_200903120215.bak'

    WITH FILE = 1, KEEP_REPLICATION,

    NOUNLOAD, REPLACE, STATS = 10

    GO

    Why don't you put your database in single user mode? Does that not work ?

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

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