0

Going through https://www.sqlshack.com/understanding-database-recovery-models/ I see that there are three types of recovery models:

  • SIMPLE
  • FULL
  • BULK_Logged

But for the table msdb.dbo.backupset I can see the recovery_model column can be null. In what case would this take a NULL value. How to create a backup such that it falls into this case.

2
  • I don't know why the value might be NULL but I'm curious what a T-SQL RESTORE HEADERONLY from that backup returns for RecoveryModel. – Dan Guzman Apr 29 at 10:10
  • Unfortunately can't, customer environment, old issue. – Sonali Gupta Apr 29 at 11:45
0

Yes, it is nullable and you can run update statement on these tables. There is system view called sys.databases(Keeps attributes of database) which contains one row for each databases, you can't update it. If you do so, you will get error as below:

Msg 259, Level 16, State 1, Line 3
Ad hoc updates to system catalogs are not allowed.

So, only way to set recovery model is by running alter database and its also mentioned on the link you have provided. These options are driven by drop-down at recovery model for each database and there you have only three options i.e. Full, Simple and Bulk-logged.

This change can be done either by UI or through below command:

ALTER DATABASE DB_NAME SET RECOVERY SIMPLE  -- You may change last keyword.

There is no blank option which could set the value as null in sys.databases view.

recovery_model in table backupset can take only three values as mentioned at Official documentation:

Recovery model for the database:

FULL

BULK-LOGGED

SIMPLE

I hope this answers your question.

3
  • I have a db dump for backupset that has recovery_model entries as NULL. With above findings it seems we cannot have recovery of a db as NULL and hence when we take backup, recovery_model will be the same as RECOVERY in backupset table. Would like to know how to achieve this, its certainly possible looking at the dump. – Sonali Gupta Apr 29 at 9:10
  • Is it possible that someone updated it manually? Did you try restoring it on some other database server or with some different database name on the same server? Possibly, you will get a clue. Because recovery model can only have these three options. – Learning_DBAdmin Apr 29 at 9:18
  • Doesn't seem manually done. Cannot restore the said backup as its customer env plus a very old issue. But thanks for the inputs. – Sonali Gupta Apr 29 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.