Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Check db for all database with output text file using osql

Answered Check db for all database with output text file using osql

  • Saturday, January 12, 2013 1:31 PM
     
     
    Sign In to Vote
    0

    HI all,

    we are using sql2000.we want to create a job to run checkdb on weekends.

    Could some one guide me on script that needs to be in step.

    I want to generate out put file on the check db  command status.

    please help

    regards,


    -


    • Edited by sqldbarocks Saturday, January 12, 2013 1:36 PM chnge in sub line
    •  
     

All Replies

  • Saturday, January 12, 2013 3:30 PM
    Moderator
     
     
    Sign In to Vote
    0

    The following article explains how to do it.

    Quote from the article:

    osql -E -Q"DBCC CHECKDB (master) WITH ALL_ERRORMSGS, NO_INFOMSGS" -oC:\outputfile.txt

    http://www.sqlskills.com/blogs/paul/how-to-get-all-the-corruption-messages-from-checkdb/

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Saturday, January 12, 2013 6:13 PM
     
     
    Sign In to Vote
    0

    Thanks for giving answer(its as usaul anyways)----great ppl for great blog

    With the above query , i was trying to automate with job

    Job step looks like"

    Set Nocount on
    Declare @Server_Name varchar(65)
    Declare @Database_Name varchar(65)
    Declare Cur_DB Cursor For
            Select name from master.dbo.sysdatabases
    Declare @osql varchar(2000)
    select @Server_Name = @@ServerName
    Open Cur_DB
    Fetch Next from Cur_DB into @Database_Name
    While @@Fetch_status=0
        Begin
            Set @osql= osql -E -Q "DBCC CHECKDB (['+@Database_Name+']) WITH ALL_ERRORMSGS, NO_INFOMSGS" -oC:\'+Database_name+'outputfile.txt
            
            EXEC (@osql) --Execute the osql statement
            Fetch Next from Cur_DB into @Database_Name
        End
    Close Cur_DB

    but I am end up with some erros. Can some rectify this for me

    regards,

    -

     
  • Saturday, January 12, 2013 7:09 PM
     
     Answered
    Sign In to Vote
    0

    You have confused idioms. OSQL is not a T-SQL command, it's a comnad-line tool, so you would use xp_cmdshell to execute it. On top of that you don't have the string delimiters correctly.

    However, there is no reason to make a loopback connection. You can run the job like this:

    Set Nocount on
    Declare @Database_Name sysname
    Declare Cur_DB Cursor STATIC LOCAL For
            Select name from master.dbo.sysdatabases
    Declare @sql nvarchar(4000)
    Open Cur_DB
    While 1 = 1
    Begin
       FETCH Cur_DB INTO @Database_Name
       IF @@fetch_status <> 0
          BREAK
       PRINT @Database_Name
       Set @sql= 'DBCC CHECKDB( ' + quotename(@Database_Name) +') WITH ALL_ERRORMSGS, NO_INFOMSGS'
       EXEC (@sql)
    End
    DEALLOCATE Cur_DB

    Very important: when you set up the job, make sure that you add an action when the job step fails, for instance an alert of some sort. Hopefully this job will run without any error messages. But if it produces errors, there is a corrupted database to take care.

    And don't forget to take your backups!

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, January 14, 2013 5:11 PM
     
     
    Sign In to Vote
    0

    Many thanks for corrected one. That works, but I am looking for a solution that we should be alerted on corrupted databases .

    with the above one, job shows success even for corrupted db's. how we would be aleted for corrupted dbs?

    Have this addressed please..........

    -