Post reply

Update Script to handle databases where Inactive

  • September 4, 2013 at 3:08 am

    #279420

    Hi,

    I am looking to find script that works for updates to handle databases which have been Inactive.

    basically if databases are offline that will go in active and if we apply new enhancements that needs come to active.

    If any one can help will be Appreciate.

    Fella

    @@@Attitude Builds The Trust@@@

  • September 4, 2013 at 4:03 am

    #1647227

    Something like this?

    -- This uses a temp table, so if there's any chance

    -- your session will close before you're finished,

    -- use a permanent table instead.

    -- You'll need to make it slightly more sophisticated

    -- if you want it to run without intervention, but I'm

    -- sure you can work that out!

    SELECT name INTO #DBs

    FROM sys.databases

    WHERE state_desc = 'OFFLINE'

    SELECT 'ALTER DATABASE ' + name + ' SET ONLINE'

    FROM #DBs

    -- Now execute the results

    -- Now do your enhancement

    SELECT 'ALTER DATABASE ' + name + ' SET OFFLINE'

    FROM #DBs

    -- Now execute the results again

    DROP TABLE #DBs

    John

  • September 4, 2013 at 4:14 am

    #1647232

    Thanks John,

    Will check now your query....!

    Fella

  • September 4, 2013 at 7:55 am

    #1647320

    HI John,

    This is not what i am expecting, actually ,my purpose is we have own monitoring tool and making some updates everytime. this time we added logshipping functionality to return how many databases are logshipped. we have large no of servers, so my script is fine for 2005 and above versions, but i applied 2000 server, it bring me back databases in that server over writting. it means what ever original databases are gone into inactive and newly creating same databases again. my option looking at i dont want to be those databases inactive. because old database name and id are same. so i am expecting script changes to updates in my procedure....?

    so i think U my point?

    if you can help will be great?

    cheers

  • September 4, 2013 at 9:30 am

    #1647378

    If you're using SQL Server 2000, it's more difficult. You have to use sysdatabases instead of sys.databases. Read the documentation about that and you'll see that the status column is a bitmask of the various statuses that the database can have. You'll need to look up the value for OFFLINE and use the "&" operator to find out which databases have that status. Give it a go, and post back if there's anything in particular that you don't understand.

    John

  • September 4, 2013 at 10:07 am

    #1647393

    Cheers Mate.

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

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