Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Friday, 26 June 2009

The new loop.  I used to always default to using a vbs script to loop through and delete files, now I use forfiles, a one liner, and much easier to look at, just like the pic below!  Who knew looping could be so much fun.

Deleteing backups, log management...tiresome tasks.  Recently learned a new trick from SQL Puma for deleting files using a utility from the windows resource kit.  A program called forfiles.exe.  Much easier to use than deleting with a VBS Script (see my posts on Archive Logs and SQL Logs).  Powershell will eventually make even forfiles obsolete, but it is not on each of our servers and the windows support team is reluctant to put it there :( ForFiles is on every one of our windows 2003 servers so it's good to go.

To delete all backups older than 6 days:

forfiles /p "i:\backups\full" /m "*.bak" /c "cmd /c del /Q @path" /d -6

Delete all files in the standard TEMP folders and all their subfolders after 9 days:

forfiles -p"%SYSTEMROOT%\TEMP" -s -c"cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" -d-9
forfiles /p "%TEMP%" /s /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -9

Delete all *.TMP files in the system root (like C:\WINDOWS) that are older than 9 days:

forfiles /p "%SYSTEMROOT%" /m *.TMP /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -9

Delete old IIS log files:

forfiles /p "%SYSTEMROOT%\system32\Logfiles\HttpErr" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99
forfiles /p "%SYSTEMROOT%\system32\Logfiles\W3Svc1" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99
forfiles /p "%SYSTEMROOT%\system32\Logfiles\SmtpSvc1" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99

Dealing with spaces in filenames:

Forfiles -p R:\MyFiles -s -m *.* -d -365 -c "Cmd /C Echo 0x22@Path\@File0x22"

Friday, 26 June 2009 08:51:10 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Wednesday, 24 June 2009

This script copies jobs from one msdb database to another, useful for when SQL Server is re-installed, and the DBA Forgot to script out the sql agent jobs; but you still have the old msdb mdf and ldf. 

Simply attach the database as msdb_old.

It is also possible to use this via a linked server and copy some sqlagent jobs from one server to another; this script is specific to sql server 2005. 

One item I had to adjust is that some of the jobs appeared "greyed" out afterwards and I had to open each job and adjust the target server to "local". 

But it was still quicker than re-creating all the jobs.  (I was unable to restore the previous version of the msdb database because it was at a lower service pack level than what the new server was built at - had I been smart as the new server was built there would have been a restore of the msdb database completed when the new server was at the same level, but I missed that step and was then left with the task of recreating all the jobs, argh).

I'd be cautious using this script if there were a lot of reporting subscription jobs (as there would have to be some synchronizations with the report database and website), but in this particular case all the jobs were custom backups, tlogs, exports, imports and other purely tsql and ssis related tasks.

http://www.lifeasbob.com/Code/ScriptVault.aspx?script_id=80 

 

Wednesday, 24 June 2009 09:43:24 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server |  SQL Agent#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
DTA - Failed to initialize MSDB dat...
SQL Server Security, not where it n...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
May, 2017 (2)
April, 2017 (1)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll