Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    19

    Wink Unanswered: System Table that Holds Recovery Mode in SQL Server 2000

    Hi All,

    Can any one tell me which table holds recovery mode details in SQL Server 2000,
    or
    how to query to get recovery mode information in query analyzer for sql server 2000

  2. #2
    Join Date
    Jun 2004
    Posts
    19
    Thanks in Advance.

  3. #3
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    There might be an easier way of doing this but here is one solution:

    This is using sp_helpdb:

    Code:
    --TESTED ON SQL SERVER 2000
    CREATE TABLE #temp (
    	[name] nvarchar(255),
    	db_size nvarchar(255),
    	owner nvarchar(255),
    	dbid nvarchar(255),
    	created nvarchar(255),
    	status nvarchar(255),
    	compatibility_level nvarchar(255))
    
    INSERT INTO #temp EXEC master..sp_helpdb
    
    SELECT 
    	[name] AS DBNAME, 
    	SUBSTRING(status, (CHARINDEX('Recovery=', status)+9), (CHARINDEX(',', status, (CHARINDEX('Recovery=', status)+9)))-(CHARINDEX('Recovery=', status)+9)) AS RECOVERY_MODE
    FROM #temp
    ORDER BY [name]
    
    DROP TABLE #temp

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You can use the DATABASEPROPERTYEX function to determine the recovery model:
    DATABASEPROPERTYEX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •