April 9, 2012 at 9:35 am
Folks
Is there any script someone has written which would take care of following
database is in SIMPLE recovery
and is being backed up daily. If next day it gets changed to FUll, I should be able to find out from some tables and be able to run full backup followed by a tran log backup.
I have the process to run log backups and it identifies if DB is in FULL or SIMPLE. I just dont know where to look for the change to the recovery model. Is there a table/view which stores such history.
Simply knowing recovery model is not helpful here.. I have to know if the database showing "FULL" was in "SIMPLE" earlier
April 9, 2012 at 9:55 am
If you want a running history, I'd use a SQL Job and pull data from sys.databases on a daily basis into a custom table. Then you can query that table as needed.
SELECT name, recovery_model_desc
FROM sys.databases
_________________________________
seth delconte
http://sqlkeys.com
April 9, 2012 at 9:56 am
As far as I know there is no table in SQL Server that keeps a history of what recovery model a database has been in. You can find the current recovery model from the sys.databases view, but it sounds like that's not what you're looking for.
When the recovery model of a database is changed, an entry is made into the SQL Server error log. You may be able to set up a process to scan the error log for such entries and produce a history from there.
Another option would be to setup a job to query sys.databases on a recurring basis and keep track of recovery models in a table.
Does this answer your question? Hope it helps!
Bob
_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008
Blog: The Outer Join[/url]
Twitter: @SQLBob
April 9, 2012 at 9:59 am
Thanks for all the answers. I was hoping to find such a magic table 🙂
I am already collecting recovery model for all databases in environments daily. That opton could be used. I like the idea of going in to error log as well.
Thanks for quick replies
April 9, 2012 at 9:59 am
The table msdb.dbo.backupset has a column that tells you the recovery model of the database. You could query previous backups of the database to determine what recovery model the database was using the last time it was backed up.
April 9, 2012 at 10:01 am
will try that as well . Thanks !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply