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

• Jeff-67583

  SSCertifiable

  Points: 5625

  More actions

  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

• mccork

  SSC-Insane

  Points: 22288

  More actions

  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