Monday, November 2, 2020

Are you restoring database with Full Backup? Check below properties first..

Hello DBAs,

On weekend due to some reason data file of our database got corrupted so to resolve it we decided to restore latest healthy full backup followed by log backups. After we ran DBCC CHECKDB and did database health check everything was fine, so we went to sleep.

On next day Monday, users started complaining they cant see data in application after executing application utility. So application team approached to me with their problem. Then I got tensed and started doubting, did I restore wrong database backup? hence users cant see data.

But I checked and confirmed I restored correct database backup, then what was the issue? I tried my all DBA knowledge I have, but no luck. Finally application team raised case with application vendor and in their investigation they found cross-database chaining option is off for database. Then application team came to me and we enabled it and application started working.

I was in belief that when we restore database full backup from same instance all database configuration get set itself and no need to change any setting. But after I checked Microsoft docs and got to know I was wrong. So my knowledge is not sufficient we are not aware too many minute things in SQL server which could affect great.

Below are the two settings which I know and get disabled when you restore database from Full backup or when you detach - attach database files. So you need to enable them again

1) TRUSTWORTHY Database Property

2) cross-database chaining

These properties allows database users to access objects on across other databases in same instance.

You can verify settings before and after restore backup or detach attach files by checking columns is_trustworthy_on, is_db_chaining_on in sys.databases tables.

For more information of above two settings, refer below Microsoft documents.

https://docs.microsoft.com/en-us/sql/relational-databases/security/trustworthy-database-property?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/cross-db-ownership-chaining-server-configuration-option?view=sql-server-ver15

Please do comment for suggestion or if you know any other properties which we should check before restoring database full backup.







2 comments:

  1. I’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives indepth information. thanks for this nice article... Ann Arbor apartments

    ReplyDelete