Post reply

Restore database

  • July 16, 2008 at 2:23 am

    #191012

    Hi anyone,

    Does restore database will affect/remove its dts package?

    Thank you.

  • July 16, 2008 at 4:09 am

    #843649

    Hi, as far as I know when you restore a database the dts packages would also be restored corresponding to the database.

    🙂

  • July 16, 2008 at 5:50 am

    #843699

    Hi,

    I hope it depends on type of DTS using.


    - Pradyothana     http://www.msqlserver.com

  • July 16, 2008 at 8:29 am

    #843808

    DTS packages are not stored in any of the user databases. They're stored in MSDB and backed up and restored along with that database, not the user databases.

    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

  • July 16, 2008 at 1:52 pm

    #844043

    I am in the process of moving around some databases now, and this article that I have been referring to a lot lately seems to indicate that there is a separate process involved in moving DTS packages:

    http://support.microsoft.com/kb/314546/en-us

  • July 16, 2008 at 11:48 pm

    #844152

    [font="Verdana"]The article gives you the right direction.

    Either you can save the DTS packages as legacy packages and import in the destination server or else open the package in designer and in the servername type the destination server name.

    Refer the below article too

    http://www.sqldts.com/204.aspx%5B/font%5D

    Regards..Vidhya Sagar
    SQL-Articles

  • July 17, 2008 at 12:43 am

    #844169

    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 12:58 am

    #844173

    Hi,

    You can iclude it in the script to kill the SPIDs connected to the database and keep database in sigle user mode to avoid any new connections and then perform restore and take it off from single user mode once the restore is complete.

    [font="Verdana"]Renuka__[/font]

  • July 17, 2008 at 1:04 am

    #844176

    Hi,

    ok thats good idea, but how do you identify the spid against the database and kill it?

    Do you have any script for that?

    thanks

    🙂

  • July 17, 2008 at 3:14 am

    #844214

    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 18, 2008 at 2:30 pm

    #845257

    rinu philip (7/17/2008)

    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,

    😛

    You can add this a a job step prior to the restore. It will kill and rollback all SPID's.

    ALTER DATABASE BIN

    SET SINGLE_USER

    WITH ROLLBACK IMMEDIATE

  • July 19, 2008 at 10:35 pm

    #845373

    Setting database in single user mode, will this kill all the spids? I think we need to put an additional step, to bring back the database in normal mode,once the restore has been done.

    what do you think??:)

  • July 19, 2008 at 11:58 pm

    #845378

    Hi,

    Setting up database in single user mode does not kill SPIDs. But, if there are any active connections to the database exist, then it would not allow you to set the database in single user mode until all those active connections are terminated. Once it is set to single user mode, no other connections are allowed.

    sp_dboption 'DB Name','single user','True'

    GO

    Once the restore is done,

    sp_dboption 'DB Name','single user','False'

    GO

    [font="Verdana"]Renuka__[/font]

  • July 20, 2008 at 1:25 am

    #845381

    Hi, So to terminate those active connections we need to kill those SPIDs? is it?

    thanks

  • July 21, 2008 at 2:55 am

    #845605

    Hi,

    Do you know how to recover back the DTS package by using MSDB Backup Table?

    I mistakenly restore database without save the DTS package.

    Thank you.

Viewing 15 posts - 1 through 15 (of 16 total)

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