Backup Process Question

Nov 29, 2009 at 1:22 AM
Edited Nov 29, 2009 at 1:43 AM

Hi Ed,

Firstly - great work on sharing this with the community - it has proven a great basis for our sql 2008 rollout !

I've been looking through the generic maintenance scripts you've included with FineBuild, and it appears you've previously used the BackupIndex table to store various backup details on a per database level, but it was removed a couple of years ago from the main spBackupDatabase procedure, and only remains in the spAdHocBackup.

What was the main reason this was removed ?  Were there performance/process issues, or did you decide it was overkill to store all the regular backups in there ?

Also, FYI - There's an issue in sp_DeleteOldBackups if the regional settings on the computer cause the file sizes to include delimeters (eg. commas).  
The result is the @filepath starts with the tail end of the backup file size, thus becoming an invalid path, yet the job doesn't fail because the DEL command simply doesn't find any files to remove.  

The fix is to include '/-C' in the DIR command, which turns off thousand separators.



Nov 29, 2009 at 2:04 AM

Right - slight furfy in my initial post.  Looks like the thousand separators is not the issue after all.

Here's the value of @filelist just before parsing it for the date  '22/11/2009  09:05 PM           3,223,120 Test_db_200911222105.BAK'

When determining the file path, the AM/PM is throwing things out by 3 characters when attempting to get the filename, resulting in a filename of '20 Test_db_200911222105.bak'.

I don't want to have to modify all our server times from our Australian standard, so is there any risk in parsing the date from the filename?  

The filename is determined at the time of creation, and the dir command takes a '/B' switch to only return the filename, making the parsing a bit safer too.





Dec 17, 2009 at 9:09 AM


Thanks for telling me about the bug in spDeleteOldBackups.  I'll fix it for the next release of FineBuild (which will also include full support for cluster installs).  In the meantime if you are happy changing the code so it works for you then go ahead.

The BackupIndex table was only intended to hold details of ad-hoc backups, not details of all backups.  Ther is a system table in msdb that holds all backup details.  I changed the code in order to simplify the spBackupDatabase proc when I added support for what was at that time the free SQLBackup product.

PS Sorry for the delay in responding, I have been on holiday.