I have a Highly pressured Production Environment with Database name DB1. And I want to run a weekly DBCC CHECKDB process on one of the databases there.
(You can understand,I can't do it on my production environment,Performance IMPACT).
So, I thought to use one of my Log-Shipping process.
I am have a Log-Shipping process, from DB1(Primary) to DB1_LS Database(Secondary) which locate in a distant server (DWH server).
Question:
Is there a way that I can take a full backup of the Secondary database DB1_LS?
I want to be able, to restore it and run the DBCC CHECKDB on it (COPY-ONLY BACKUP ?)
Best Answer
I believe you cannot take a native backup of the databases if they are in Standby/Read Only mode.
So backing up the secondary database would be not be a good go.
Also, if LS DB is in "Read only mode" you can run DBCC CHECKDB , it will complete but it would not get into the boot page and hence not the check you expected it to be. Moreover even if it reports or finds an error you would not be able to run a repair statement with DBCC CHECK.
What you can do is, take backup of the DB1 during off peak hours over a network to another test/dev machine with appropriate resources to restore the backup.
From the restored backup, you can perform the DBCC CHECKDB on the restored database.
Also, please read this excellent article by Aaron Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts for best practises on it.