Monday, January 24, 2011

RECOVERY MODEL, Recovery ... Recoverable?

On more than one occasion, I have inherited a bunch of pre-existing databases, and had to spend endless hours cleaning up the mess.  As soon as the servers become my responsibility, I start at the baseline -- standard maintenance and administration, and recovery requirements. 

What is the RECOVERY MODEL for each database?
  SELECT name [Database Name],
  recovery_model_desc [Recovery Model]
  FROM sys.databases
  WHERE database_id > 4

Backups... are they even being done?  (oooh, I could tell you some stories...)  This is a quick peek into msdb.dbo.backupsets, to return whatever you've got (FULL, DIFF, LOG), ordered by the backup date DESC.
  SELECT [Database],bkup.backup_finish_date   
      WHEN TYPE ='L' THEN 'LOG' END [Type], 
  CEILING(bkup.backup_size/1048576) [Size (MB)],
  USER_NAME [ByWhom]
  FROM master.dbo.sysdatabases sys 
  LEFT OUTER JOIN msdb.dbo.backupset bkup 
    ON bkup.database_name =  
  ORDER BY backup_finish_date DESC;

There are many, many more, but that is typically where I start.  Here are a couple more good ones.  Please check them out and let me know if you have any questions. 

No comments:

Post a Comment