SQLCMD DBCC checkdb output file with date stamp

Question

Post reply

SQLCMD DBCC checkdb output file with date stamp

RayMan007

SSChasing Mays

Points: 639
More actions
July 8, 2015 at 9:13 am

#309681

I would like to know if it is possible to date stamp the output file for the DBCC integrity check?
The following cmd works fine however I tried different ways to date stamp the output file with no luck.
sqlcmd -U backup -P Password -S Server -Q"DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS" -o"G:\LOGS\DBCCResults.txt"

Viewing 6 posts - 1 through 5 (of 5 total)

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

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

I'd use powershell to do this:-

$date = get-date -format ddMMyyyy_hhmmss

$filename = "DBCCResults_" + $date + ".txt"

sqlcmd -U backup -P Password -S Server -Q"DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS" -o"G:\LOGS\$filename"

RayMan007 SSChasing Mays Points: 639 More actions · Answer 2

Unfortunately I can't use powershell, I am trying to set this up to run as a bat file. Would you know if it is possible to do this with a *.bat file?

Lowell SSC Guru Points: 323518 More actions · Answer 3

Ray check out this script from my friend Gianluca Sartori; he's posted it several times here on SSC;

he basically takes advantage of how DBCC supports WITH TABLERESULTS, and stuffs the results into a table for review, emailing any errors.

-- Build a SQL string

SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '

it's a nice script, IMHO.

http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

Lowell (7/8/2015)
Ray check out this script from my friend Gianluca Sartori; he's posted it several times here on SSC;
he basically takes advantage of how DBCC supports WITH TABLERESULTS, and stuffs the results into a table for review, emailing any errors.
-- Build a SQL string
SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '
it's a nice script, IMHO.
http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/

+1

This is a quite handy approach. One thing to be aware of if you have some instances on 2012 and some on earlier versions is that 2012 added some columns to the TABLERESULTS of CHECKDB, so you'll want to account for version when you're creating the table into which those results will be inserted.

If memory serves that's all taken care of in Gianluca's code, but I figured it was worth an up-front mention, since MS might choose to change it randomly in the future 🙂

Cheers!

JimmyJones SSCrazy Points: 2178 More actions · Answer 5

ray santalis-466762 (7/8/2015)
Unfortunately I can't use powershell, I am trying to set this up to run as a bat file. Would you know if it is possible to do this with a *.bat file?

If you want to use a batch file, you can 'build' your own timestamp, depending on the format of your %date%:

First example:

echo %date%

Thu 07/09/2015

echo %date:~10,4%%date:~4,2%%date:~7,2%.txt

20150709.txt

Second example:

echo %date%

2015.07.09.

echo %date:~0,4%%date:~5,2%%date:~8,2%.txt

20150709.txt

As you can see, the numbers are representing %date:~From,NumberOfnextChars%, so if you figure out what is your date format on your system and build the necessary command, you can just simply put it in your output files name.

Hope this helps.

PS.: If you prefer having time as well , you can use the same logic with %time%

EDIT: Included a real output example.

sqlcmd -S. -Q"SELECT @@SERVERNAME" -o "G:\LOGS\DBCCResults_%date:~10,4%%date:~4,2%%date:~7,2%.txt"

Filename: DBCCResults_20150709.txt

________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."