Live backups

Maury
You will find that most sites use the native SQL Server backup to back the
databases up to disk and then use something like Legato or Backup exec to
copy them from disk to offsite storage.
There are several reasons for doing it this way. Certainly most of the
backup programs did not work very well with SQL Server in older versions. It
was always a bit hit of miss if you could actually restore your backups. Mos
t
of the main tools do know have SQL Server speciffic modules now, which means
they are better at backing up SQL Server.
Using the native SQL Server backup means no version problem issues, which
can be useful.
By using the SQL Server backup to disk you have an easily accessable version
of the backup. This can be useful if you need to restore from it or want to
copy it to another server for support purposes.
The bottom line is that most people use native SQL Server backup to disk and
then a third party tool, because in most cases it is the best way to do it.
Regards
John
"Maury Markowitz" wrote:

> I consider the documentation on this topic somewhat confusing, so I figure
d
> I'd ask here.
> If I use any one of the common network backup utilities, and tell it to
> "live backup" my database and log, will this work as a backup?
> Right now I'm doing a full backup every night, but we DO have such softwar
e
> installed that's backing up the rest of our systems live onto offsite
> storage. I consider this to be much "safer", and if we can use this on our
> SQL Server as well, I would rest better.
> Maury"John Bandettini" wrote:

> There are several reasons for doing it this way. Certainly most of the
> backup programs did not work very well with SQL Server in older versions.
Can you be more specific on this particular issue? We're currently running
SS2k, is this one of the "older versions" you mention? Do you know what sort
of problems should be expected in these cases?
Technically it seems there shouldn't be a problem. Writes to the logs should
be flushed through immediately, which is what I would suspect COULD cause a
problem.
Maury|||Maury Markowitz wrote:
> "John Bandettini" wrote:
> Technically it seems there shouldn't be a problem. Writes to the logs shou
ld
> be flushed through immediately, which is what I would suspect COULD cause
a
> problem.
John's point, and the general consensus, is that it is FAR safer to
rely on the built-in native SQL Server backup utility to create the
database backups, than on a third-party tool. Use the SQL BACKUP
command to dump the database and/or t-logs to disk, then backup those
disk files using your favorite backup tool. Far more flexible, far
more reliable, you'll save yourself much grief doing it this way.|||"Tracy McKibben" wrote:
> John's point, and the general consensus, is that it is FAR safer to
> rely on the built-in native SQL Server backup utility to create the
> database backups, than on a third-party tool. Use the SQL BACKUP
But the problem with this approach is that it only gets done "every so
often". A failure is more likely to occur during the day (high activity) tha
t
at night when the backups run, so the delay between a nightly backup and the
failure represents a real risk for loss of data.
Let's approach this another way: is there a product that bills itself as a
suitable real-time backup system for SS 2k?
Maury|||Maury Markowitz wrote:
> But the problem with this approach is that it only gets done "every so
> often". A failure is more likely to occur during the day (high activity) t
hat
> at night when the backups run, so the delay between a nightly backup and t
he
> failure represents a real risk for loss of data.
> Let's approach this another way: is there a product that bills itself as a
> suitable real-time backup system for SS 2k?
What you're saying is true if you're only running FULL database
backups. You also have to ability to do transaction log backups, at
any interval you'd like. The transaction log is a record of every
change that occurs within the database. If you backup the transaction
log at regular intervals, let's say 15 minutes, you'll never lose more
than 15 minutes of data should a failure occur.
For instance, on my production servers, full database backups are done
nightly. After that, a transaction log backup is done every 5 minutes.
We also log-ship those backups to a standby database. Should a
database failure occur, we are virtually guaranteed not to lose more
than 5 minutes worth of data.|||Tracy McKibben wrote:
> What you're saying is true if you're only running FULL database
> backups. You also have to ability to do transaction log backups, at
> any interval you'd like. The transaction log is a record of every
> change that occurs within the database. If you backup the transaction
> log at regular intervals, let's say 15 minutes, you'll never lose more
> than 15 minutes of data should a failure occur.
> For instance, on my production servers, full database backups are done
> nightly. After that, a transaction log backup is done every 5 minutes.
> We also log-ship those backups to a standby database. Should a
> database failure occur, we are virtually guaranteed not to lose more
> than 5 minutes worth of data.
Also, your database must be in "Full" or "Bulk-Logged" recovery mode to
do transaction log backups.|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
>
> But the problem with this approach is that it only gets done "every so
> often". A failure is more likely to occur during the day (high activity) t
hat
> at night when the backups run, so the delay between a nightly backup and t
he
> failure represents a real risk for loss of data.
> Let's approach this another way: is there a product that bills itself as a
> suitable real-time backup system for SS 2k?
> Maury
Hi Maury
No backup systems I'm aware of is more "real-time" than what you can do
with SQL servers native backup.
The procedure is to e.g. backup your database once every night, and then
run transaction log backups every so and so often (e.g. every 15
minutes). By doing it this way you will always (at least) be able to
restore your database to the state it was in 15 minutes before it got
damaged. Depending on what has gone wrong, you'll actually in many case
be able to take a backup of you actual logfile and then also apply this
log backup with the STOP AT option that will stop the restore at the
time just before things went wrong.
In my opinion this is the most reliable and safe backup method and I'm
not aware of anything that can do it better.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||I consider the documentation on this topic somewhat confusing, so I figured
I'd ask here.
If I use any one of the common network backup utilities, and tell it to
"live backup" my database and log, will this work as a backup?
Right now I'm doing a full backup every night, but we DO have such software
installed that's backing up the rest of our systems live onto offsite
storage. I consider this to be much "safer", and if we can use this on our
SQL Server as well, I would rest better.
Maury|||Maury
You will find that most sites use the native SQL Server backup to back the
databases up to disk and then use something like Legato or Backup exec to
copy them from disk to offsite storage.
There are several reasons for doing it this way. Certainly most of the
backup programs did not work very well with SQL Server in older versions. It
was always a bit hit of miss if you could actually restore your backups. Mos
t
of the main tools do know have SQL Server speciffic modules now, which means
they are better at backing up SQL Server.
Using the native SQL Server backup means no version problem issues, which
can be useful.
By using the SQL Server backup to disk you have an easily accessable version
of the backup. This can be useful if you need to restore from it or want to
copy it to another server for support purposes.
The bottom line is that most people use native SQL Server backup to disk and
then a third party tool, because in most cases it is the best way to do it.
Regards
John
"Maury Markowitz" wrote:

> I consider the documentation on this topic somewhat confusing, so I figure
d
> I'd ask here.
> If I use any one of the common network backup utilities, and tell it to
> "live backup" my database and log, will this work as a backup?
> Right now I'm doing a full backup every night, but we DO have such softwar
e
> installed that's backing up the rest of our systems live onto offsite
> storage. I consider this to be much "safer", and if we can use this on our
> SQL Server as well, I would rest better.
> Maury|||"John Bandettini" wrote:

> There are several reasons for doing it this way. Certainly most of the
> backup programs did not work very well with SQL Server in older versions.
Can you be more specific on this particular issue? We're currently running
SS2k, is this one of the "older versions" you mention? Do you know what sort
of problems should be expected in these cases?
Technically it seems there shouldn't be a problem. Writes to the logs should
be flushed through immediately, which is what I would suspect COULD cause a
problem.
Maury