Post reply

Determining the logging mode with a script

  • July 25, 2006 at 8:42 am

    #61429

    Hello,

    I would like to check all of the databases to verify they are in full logging mode. Does anyone know of a script that does this? I can run Perl or VBScript. I would like to have this so that I can run it at regular intervals and monitor for any changes.

    Thanks,

    Brian

  • July 25, 2006 at 9:32 am

    #651371

    Brian,

    Here's how to find the recovery model for a particular database (named 'NSF').  SP_HELPDB uses this to find database properties.  Maybe you could modify it to work with sp_MSforeachdb to get the info for every database.

    select convert(sysname,DatabasePropertyEx('NSF','Recovery'))

    Greg

    Greg

  • July 25, 2006 at 11:50 am

    #651434

    To expand you can do this to get all on a server.

     

    sp_msforeachdb 'select ''?'' dbname, convert(sysname,DatabasePropertyEx(''?'',''Recovery'')) recovery'

  • July 25, 2006 at 2:00 pm

    #651463

    Great. This is a good start. I will work with some of the scripts I already have and see what I can come up with. When I get it done I will post what I have. Basically what I want to do with this is input from a text file a list of servers and output servers and databases that are in simple mode.

    Thanks,

    Brian

  • July 25, 2006 at 2:45 pm

    #651478

    It is easier to ask the server sysadmins to submit you the list of logging modes then to give you the access to their servers to run this query.

    Regards,Yelena Varsha

  • July 25, 2006 at 10:52 pm

    #651527

    And here's a simplification of the sp_msforeachdb version...

    select name, DatabasePropertyEx(name,'Recovery') as recovery from sysdatabases

  • July 26, 2006 at 7:52 am

    #651623

    I am the admin and DBA. I have about 30 servers to check. Since I can't block the local Admin group on most servers I need to validate the modes are not changed monthly. I would rather run this using VBScript or Perl to hit all of the servers with SQL/MSDE instances on them. I already have a list of servers with instances that is updated monthly by scanning all server in our AD. This list could be used to test all DB's on the server and output a list that have simple recovery mode. That is really the reason for creating this script.

    Brian

  • July 28, 2006 at 7:00 pm

    #652221

    you may want to design your own DBA database where you would store all info about other servers/databases/failed jobs/space... We are using a linked servers approach to collect all that info

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply