Post reply

Setting Database READ ONLY/OFFLINE Using ALTER ... SET?

  • May 25, 2004 at 1:20 pm

    #161929

    Hi,

    MSSQL2000 w/SP3 using Query Analyzer

    Can the ALTER DATABASE command be used against MSSQL7 databases? I can't seem to use the ALTER command agaianst MSSQL 7 databases regarding READ_ONLY and OFFLINE using the SET command  re: not recognized.

    I can use sp_dboption, but my codes in the "status" column in sysdatabases for identifying READ ONLY or OFFLINE databases don't equal the BOL description (512; 1024 - Jan 2004 version). Any suggestions?

    Many thanks. Jeff

  • May 25, 2004 at 5:43 pm

    #507774

    No, SQL2000 spoilt us with its ALTER...SET...WITH ROLLBACK IMMEDIATE|AFTER

    In SQL 7 you have to KILL everyone using the database and then do the sp_dboption.

    BOL 7.0 confirms that the sysdatabases.status column is 512 for offline and 1024 for read only.  However, I'd suggest you identify the state with databaseproperty.  (eg. if databaseproperty('mydb', 'isoffline') = 1 or databaseproperty('mydb', 'isreadonly') = 1 ...)

     


    Cheers,
    - Mark

Viewing 2 posts - 1 through 1 (of 1 total)

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