Post reply

Restore Database

  • July 17, 2008 at 12:35 am

    #154538

    Hi,

    I was about to automate a restore process though a job to be scheduled on a daily basis.

    But I have a doubt in that, the job would be running every morining 7am. What if any user is connected to the database at that time? The restore wouldnt take place!

    Any method that could be included in the job so that no users are present during the restore? Any method to kill the spids prior the restore takes??

    thanks,

    🙂

  • July 17, 2008 at 1:12 am

    #844180

    You can kill all the processess using that database, can check this by using sys.processess system table.

    Is it the development server or test server ?

  • July 17, 2008 at 1:25 am

    #844186

    Hi,

    thanks for the prompt response!

    Its a test server, and the users are testing the data. So on a daily basis we need to restore.

    From sysprocess? ok...but we just need to kill only the user connections against the database..not any sql process?

    any script for that?

    🙂

  • July 17, 2008 at 3:15 am

    #844216

    Thanks think I got it 🙂

    Here’s a script, for killing a SPIDs against a particular database, here am using BIN as the database.

    We have to change the name of the database accordingly.

    This script can be used when we need to check for blockings on a database and helpful, when we have to restore a database especially when the database is in use while restore.

    ---------------------------------------------------------------

    USE Master

    GO

    SET NOCOUNT ON

    -- 1 - Variable Declaration

    DECLARE @DBID int

    DECLARE @CMD1 varchar(8000)

    DECLARE @spidNumber int

    DECLARE @SpidListLoop int

    DECLARE @SpidListTable table

    (UIDSpidList int IDENTITY (1,1),

    SpidNumber int)

    -- 2 - Populate @SpidListTable with the spid information

    INSERT INTO @SpidListTable (SpidNumber)

    select p.spid from master..sysprocesses p,master..sysdatabases d where

    p.dbid = d.dbid and d.name like 'BIN%'

    ORDER BY p.spid DESC

    -- 3b - Determine the highest UIDSpidList to loop through the records

    SELECT @SpidListLoop = MAX(UIDSpidList) FROM @SpidListTable

    -- 3c - While condition for looping through the spid records

    WHILE @SpidListLoop > 0

    BEGIN

    -- 3d - Capture spids location

    SELECT @spidNumber = spidnumber

    FROM @spidListTable

    WHERE UIDspidList = @SpidListLoop

    -- 3e - String together the KILL statement

    SELECT @CMD1 = 'KILL ' + CAST(@spidNumber AS varchar(5))

    -- 3f - Execute the final string to KILL the spids

    -- SELECT @CMD1

    EXEC (@CMD1)

    -- 3g - Descend through the spid list

    SELECT @SpidListLoop = @SpidListLoop - 1

    END

    SET NOCOUNT OFF

    GO

  • July 17, 2008 at 4:29 am

    #844251

    [font="Verdana"]You can refer the below script too.

    Kill all DB Connections/SPIDS[/font]

    Regards..Vidhya Sagar
    SQL-Articles

  • July 17, 2008 at 4:33 am

    #844257

    thanks! Let me me have a look

  • July 17, 2008 at 6:51 am

    #844335

    thanks guys I got the job automated!!:)

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

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