Like the idea - bug fixes and suggestions

Jan 17, 2010 at 8:38 PM

I like the idea of this: Log shipping is just basically some TSQL commands under that fancy GUI, anyway, so why not just run some TSQL as a batch job under the Windows scheduler? I have attempted to use this program. I ran into a couple of bugs and also have some further comments. I'm listing them in chronological order:

  1. Why bother backing up the database to a temporary file? Just backup to the final destination and delete the file if the backup fails. I can see how  this could be a workaround for permissions (the backup program has access the SQL Server process does not), but you eventually will need to take control of file permissions anyway if you are going to try to copy files across the network to your backup server, so why not just tackle permissions problems from the start and make sure the SQL Server process can write to your backup folder? I run both SQL Server and the backup program under a dedicated account named "sqlserver".
  2. Transaction files must be applied in order, but you don't sort the names of the transaction files. Maybe on Windows. Directory.GetFiles always returns files sorted by file name, but my samba server does not. Luckily, the fix was a simple Array.Sort(files);
  3. The algorithm used to calculate the name of the log file doesn't work correctly. In fact, it seems to be hard coded for a file name length of 9 characters - a very strange assumption. But this overlooks the larger question of why bother to use the "move" option at all?
  4. Restoring the last full database backup and all the transactions is great if you actually have to restore a database from scratch. However, that's a lot of thrashing if all you need to do is apply the most recent transaction log or two. I'm thinking of changing the program so that, the restore program has an incremental option that just applies transaction logs and moves applied transaction logs into another folder so it can skip them next time. I suppose this will end up replicating the function of log shipping, but that's really what I though this program did.