I needed a way to shrink the backups in SQL Server 2005 (native compression doesn't exist until the 2008 version). 7zip compresses better than most anything, it's free (even for commercial use), its stable, and it's small. Powershell is my favorite tool builder. This is the result:

#set alias to 7zip - adjust path as necessary
set-alias sevenZip "c:\program files\7-zip\7z.exe"

#list of database filenames
$dbdirectories = "db1","db2","db3","db4"

$dbbackupDrive = "x:\"                              # this is where SQL Server stores its backups
$dbzdestination = "y:\7zipped_backups\"             # this is where we will store the 7z version

#Backups location
cd "$dbzdestination"                                # change directory 

#create some file masks based on date

$ybu = get-date (Get-Date).addDays(-1) -format "yyyyMMdd"    # this is the filename mask of yesterday's backups
$yz = get-date (Get-Date).addDays(-1) -format "yyyy.MM.dd"   # this is the filename mask of the zip destination
$yzr = get-date (Get-Date).addDays(-2) -format "yyyy.MM.dd"  # these are the day old 7zips (unused yet)
                                                             # I may want to remove older 7zips after copied offsite
                                                             
#$exclude1 = "-x!*.dif"                             # unused but could be appended to command 
#$exclude2 = "-x!*.7z"                              # unused but could be appended to command 

foreach ($dbdirectory in $dbdirectories)            # looping each db name in our list,
{  

    $backuppath = $dbbackupDrive + $dbdirectory + "\"            #build the path to the backup sources
    $backupfiles = $dbdirectory + "_backup_" + $ybu + "*.*"       #build the filename mask
    $input = $backuppath + $backupfiles                          #full path to source files (SQL Server's files)

    $outputfile = $dbdirectory + "_backup_" + $yz + ".7z"        #build the 7z filename (7zip file)

    cd "$dbzdestination$dbdirectory"               #change the path to the backup destination
    
    # command line switch descriptions (see http://dotnetperls.com/7-zip-examples)
    #   -t7z = 7zip type
    #   -v10m = split to 10 meg files
    #   -mmt = multithreading
    
    "7zip a -t7z -v10m -mmt=off $outputfile $input"               # echo out some progress    
    
    sevenZip a -t7z -v10m -mmt=off "$outputfile" "$input"         # 7zip them up

}

# we're all done, let's cleanup
Remove-Item Alias:sevenZip

Last night, my SSRS server failed on the first access attempt after Multiple Windows patches were installed by WSUS overnight (SQL Server 2005 EE, Windows 2003 SP2). This is not a critical server for me so it doesn't get tested before patches are applied. As such, it gets all patches immediately without testing and, in fact, is a test for such patches for me. Production servers have to be approved by hand after my test servers pass.

Starting off in the Event Viewer > System, I'd see 5 W3SVC warning [a] and followed by a W3SVC failure [b]. Evidently, the rapid fail mechanism in IIS6 was shutting me down.

• [a] 5 times: Event ID 1009; Source W3SVC; A process serving application pool 'DefaultAppPool' terminated unexpectedly. The process id was 'xxxx'. The process exit code was '0xffffffff'.
• [b] 1 time: Event ID 1002; Source W3SVC; Application pool 'xxxxxx' is being automatically disabled due to a series of failures in the process(es) serving that application pool.

Restarting application pools would just duplicate the same series of events.

Looking in the Event Viewer > System, I examined which patches were applied overnight

• KB974318
• KB973904
• KB971737
• KB973917
• KB974392
• KB976325-IE8
• KB955759

KB973917 is 'a non-security update that implements Extended Protection for Authentication in Internet Information Services (IIS).'

After reviewing each knowledge base article, I located a known issue for KB973917 that described my issue perfectly: 'Internet Information Services 6.0 may not function correctly after installing KB973917'. The solution is to reinstall SP2 for your Windows 2003 server. Per Microsoft, 'Reinstalling the KB973917 update should not be necessary'.

If you want to enable the features this update provides, view here: 'http://www.microsoft.com/technet/security/advisory/973811.mspx'

To Microsoft's credit, this is the first patch to cause a problem for me in over 3 years.

If you are importing to a remote SQL server, you may recieve a 0xC0202071 error code. While this works locally on a SQL Server, the only way I've found to get around this for remote servers is to replace the destination of 'SQL Server Destination' with 'OLE DB Destination'.

No cause yet determined and BIDS truncates the error description so it's not very helpful either. 

If you need to iterate over each table, sp_MSForEachTable will do the trick.  I recently needed to mark each table as replicated to that I could change the log (not a production server).  This did the trick: