Logistics - Transaction Log Backup

I rent a dedicated server at a hosting site for a database-driven web
application. I have been doing daily backups of the SQL 2K database (Simple
Recovery method) and then use another service to ship that backup off
somewhere else in the world (can only schedule a backup once a day) in
addition to the daily system backup by the hosting company. Recently, the
server crashed and took with it my RAID drives as they were unreadable
somehow. The data was retrieved but I lost a day's worth of data.
So, I would like to NOT lose a day's worth of data again, and move to a Full
Recovery method with daily full backups with transaction log backups
periodically throughout the day. My trouble is that since I don't have a
dedicated tape machine, or access to a network drive (somewhere far away
from my server), and don't know of any FTP programs (to where?) that would
move those logs off my server, I'm wondering what others do. Just saving
those logs throughout the day won't do any good if the server crashes two
minutes before the backup.
Thanks for any tips on the logistics of backup.For smaller database I do backup locally, then have another job step (or all inside the stored
procedure) that uses the "DOS" COPY command to copy that files over to another machine. If that
isn't an option, I do the backup though an UNC directly to the other machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Don Miller" <nospam@.nospam.com> wrote in message news:OxjxSf3cGHA.4900@.TK2MSFTNGP02.phx.gbl...
>I rent a dedicated server at a hosting site for a database-driven web
> application. I have been doing daily backups of the SQL 2K database (Simple
> Recovery method) and then use another service to ship that backup off
> somewhere else in the world (can only schedule a backup once a day) in
> addition to the daily system backup by the hosting company. Recently, the
> server crashed and took with it my RAID drives as they were unreadable
> somehow. The data was retrieved but I lost a day's worth of data.
> So, I would like to NOT lose a day's worth of data again, and move to a Full
> Recovery method with daily full backups with transaction log backups
> periodically throughout the day. My trouble is that since I don't have a
> dedicated tape machine, or access to a network drive (somewhere far away
> from my server), and don't know of any FTP programs (to where?) that would
> move those logs off my server, I'm wondering what others do. Just saving
> those logs throughout the day won't do any good if the server crashes two
> minutes before the backup.
> Thanks for any tips on the logistics of backup.
>|||Just out of curiosity, have you checked with your hosting company about
doing the tape backup for you? Most provide this with your monthly fee
if it's their server.
At any rate, you can write a DTS package to move your backups via FTP
to wherever you like.|||Yes, I've talked to the hosting company (who does the entire system backups
to tape for a monthly fee) and this request (can I use their tape as a
device for backing up log files) seemed to puzzle them.
In the interim today, I found a service called LiveVault that does DB
backups of the "open" .mdf and .ldf files every 15 minutes without
transaction logs or any intervention with "standard" SQL 2K backup methods.
The problem is that this service costs as much as the server per month.
"PSPDBA" <williambr@.state.pa.us> wrote in message
news:1147200884.116152.217410@.i39g2000cwa.googlegroups.com...
> Just out of curiosity, have you checked with your hosting company about
> doing the tape backup for you? Most provide this with your monthly fee
> if it's their server.
> At any rate, you can write a DTS package to move your backups via FTP
> to wherever you like.
>|||You probably wouldn't want to use a tape as a backup device. You are
much better off (quicker) to backup to disk, then transfer to tape.
You can probably just give them the directories you want backed up and
the schedule.
It all comes down to what your downtime costs you - figure that out and
you'll know which avenue is best.