To find archive log

[mystry]

Hi,

how do you find if the archive log option is set in MS SQL.
In oracle, we can use the set of code:


select log_mode from v$database into :'workspace name'

The Parameter has values ‘NOARCHIVELOG’ (switched off’) and ‘ARCHIVELOG’ (logging is on)


do we have a similar statement For the MS SQL???

[rmiao]

There is no archive log option in ms sql, it calls db recovery model (full, bulk-logged or simple). You can find it in enterprise manager -> db properties -> options tab, or with 'select DATABASEPROPERTYEX('db_name', 'recovery')' in query analyzer. And you can change db recovery model in em or with 'alter database db_name set recovery ...'.

[skhanal]

Noarchive log is similar to simple recovery model.
Full recovery mode keeps the transaction log but you have to run transaction log backup to truncate it otherwise transaction log grows (if you have auto extend set). There is no separate redo log and archive log in sql server it's only transaction log.