Post reply

Database Does Not Show Offline with SQL

  • July 2, 2009 at 12:08 pm

    #202018

    Hi Folls

    I am moving my NorthWind database to a new location via SQL command line

    and after the ALTER DATABASE commands and before the physical move the sql still shows the database on-line, but when i check via sql server management studio, it does show it off-line

    what am i missing ?

    Code:

    ----------

    USE MASTER

    GO

    ALTER DATABASE NorthWind MODIFY FILE (NAME = Northwind, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\SampleDB\Data\NorthWind.mdf')

    GO

    ALTER DATABASE NorthWind MODIFY FILE (NAME = Northwind_log, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\SampleDB\Data\NorthWind.ldf')

    GO

    ALTER DATABASE NorthWind SET OFFLINE;

    GO

    select

    name logical_name,

    state_desc status,

    physical_name current_location

    from

    master.sys.master_files

    where

    database_id = db_id ('northwind');

    GO

    ----------

    Output:

    logical_name tatus current_location

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

    Northwind ONLINE E:\Microsoft SQL Server\MSSQL.1\MSSQL\SampleDB\Data\NorthWind.mdf

    Northwind_log ONLINE E:\Microsoft SQL Server\MSSQL.1\MSSQL\SampleDB\Data\NorthWind.ldf

    Thanks for the assist

    Jim

  • July 2, 2009 at 12:28 pm

    #1019392

    it may need some time to actually take it offline.

    It will no longer accept new connections/commands for that db.

    But it may need to complete the offload, taking some time.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 2, 2009 at 1:10 pm

    #1019412

    Once you moved the files (mdf, ldf) from Location1 to Location2, Stop and restart the instance of SQL Server. make it DB OFFLINE.

    Refresh the Management studion and verify the changes.

    Regards

    SRI

  • July 2, 2009 at 1:11 pm

    #1019413

    Hi SSCarpal Tunnel

    well i am not sure what to think

    i have had it off-line for over an hour and when i chk via the sql it still shows on-line

    Thanks

    Jim

  • July 2, 2009 at 1:18 pm

    #1019419

    Hi SRI

    I have not got that far yet

    I wanted to see the database in an offline state via sql and i do not

    It is off-line in the GUI

    I do not understand why there is a difference between the two

    Thanks

    Jim

  • July 2, 2009 at 1:26 pm

    #1019430

    s007reddy (7/2/2009)

    Once you moved the files (mdf, ldf) from Location1 to Location2, Stop and restart the instance of SQL Server. make it DB OFFLINE.

    Refresh the Management studion and verify the changes.

    Regards

    SRI

    You do not need to stop and restart the instance. Depending on the size of the database (if it's not "vanilla"), as has already been statedby ALZDBA, it make take some time. However, if you can copy/move the actual mdf/ldf files and reattach, it is offline.

    -- You can't be late until you show up.

  • July 2, 2009 at 2:34 pm

    #1019471

    Thanks tosscrosby

    I am trying not to use "attach" amd "detach" as from what i have been reading, these are soon to be deprecated

    so i am using "ALTER DATABASE" commands

    it's just that even though the database is OFFLINE, when you run the sql it still says ONLINE

    that is the quirk

    Thanks

    Jim

  • July 3, 2009 at 12:51 am

    #1019631

    That is strange indeed.

    Double check if you are trying to move the correct db-files. (they can have any name with no "link" to the db itself)

    Check if there are still connections to the db.

    (sp_who may help out)

    Whenever I put a db offline, I always first switch it to "restricted user" using "with rollback immeditate", meaning non-do users are being kicked out.

    After that I put it offline, which usually happens fairly fast.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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