Multiple Backup Chains

Thanks for answering my question, but perhaps I didn't explain my problem
very well. I would like to have two sets of backups simultaneously. One is
a daily full backup. The second is a weekly full backup with incremental
backups. As I understand it, differential backups are made based on the most
recent full backup. So the daily full backups will interfere with the backup
chain of a differential set. I don't have that much experience with
transaction log backups, but they also seem to rely on the complete backup
chain. In other words, if I make full backup A, log backup B, full backup C,
and then log backup D, can I restore A, B, and D after sending the files
(without C) to another computer? I may be doing it wrong, but I've had
trouble with this.

How do I implement that log shipping feature you mention on two untrusted
networks? Again, I would like to send a full backup to another server 10,000
miles away once a week. Every day, hour, 15 minutes, whatever, I would like
to make a log backup, send it to the other server, restore it, and have the
copy be continuously active for querying. Can a database be placed in
NORECOVERY mode after it has been in RECOVERY mode so that more logs can be
applied after the database has been queried? If so, how, or is there a way
to replay logs on a "live" read-only database? The closest I can come up
with is to have a second backup in NORECOVERY mode always available for the
incoming log so that a switchover can be made in a matter of seconds rather
than the amount of time it would take to restore the full backup and all
subsequent log backups. Thanks again for any assistance.


Erland Sommarskog wrote:
>> Is it possible to have multiple backup chains for a database? For
>> example, I would like to maintain daily full backups on a local network
>> drive, but the files are too big to FTP every day to another site. Thus
>> I would like to just FTP a full backup once a week along with
>> differential or log backups throughout the week.
>
>That sounds doable, but whatever you do, test your restore strategy.
>That is, simulate that you have a disaster, and test whether you are
>able to restore the database to a point in time.
>
>> Secondly, is there a way to continuously update a restored backup with
>> differential or log files and make the database active for read only
>> purposes? Replication is not an available option for me. It seems
>> natural that a restoring backup should be allowed to be queried.
>
>Certainly, that's a feature known as log shipping.
>

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-setup/201007/1
 >> Stay informed about: Multiple Backup Chains 

You can perform your daily backups using the COPY_ONLY parameter. Doing
this would allow you to restore from the weekly, the latest differential and
subsequent log files without having any of the daily backups.

I have not tried it, but I believe you can restore from a COPY_ONLY and
apply transaction logs - but this is something I would recommend that you
test fully before you rely on it.

In your situation, you are probably going to have to roll your own log
shipping. It's not difficult, but you are going to have to manage sending
the log files to the destination and scheduling a job to restore the log
files. When you restore the initial backup, you'd restore with NORECOVERY -
then restore the transaction log backups as needed up to the point in time
you want, and finally issue a restore WITH STANDBY. To apply more
transaction logs, you have to disconnect all users, restore the log files
and then put the database back into standby.

Jeff

"kingston via SQLMonster.com" wrote in message

> Thanks for answering my question, but perhaps I didn't explain my problem
> very well. I would like to have two sets of backups simultaneously. One
> is
> a daily full backup. The second is a weekly full backup with incremental
> backups. As I understand it, differential backups are made based on the
> most
> recent full backup. So the daily full backups will interfere with the
> backup
> chain of a differential set. I don't have that much experience with
> transaction log backups, but they also seem to rely on the complete backup
> chain. In other words, if I make full backup A, log backup B, full backup
> C,
> and then log backup D, can I restore A, B, and D after sending the files
> (without C) to another computer? I may be doing it wrong, but I've had
> trouble with this.
>
> How do I implement that log shipping feature you mention on two untrusted
> networks? Again, I would like to send a full backup to another server
> 10,000
> miles away once a week. Every day, hour, 15 minutes, whatever, I would
> like
> to make a log backup, send it to the other server, restore it, and have
> the
> copy be continuously active for querying. Can a database be placed in
> NORECOVERY mode after it has been in RECOVERY mode so that more logs can
> be
> applied after the database has been queried? If so, how, or is there a
> way
> to replay logs on a "live" read-only database? The closest I can come up
> with is to have a second backup in NORECOVERY mode always available for
> the
> incoming log so that a switchover can be made in a matter of seconds
> rather
> than the amount of time it would take to restore the full backup and all
> subsequent log backups. Thanks again for any assistance.
>
>
> Erland Sommarskog wrote:
>>> Is it possible to have multiple backup chains for a database? For
>>> example, I would like to maintain daily full backups on a local network
>>> drive, but the files are too big to FTP every day to another site. Thus
>>> I would like to just FTP a full backup once a week along with
>>> differential or log backups throughout the week.
>>
>>That sounds doable, but whatever you do, test your restore strategy.
>>That is, simulate that you have a disaster, and test whether you are
>>able to restore the database to a point in time.
>>
>>> Secondly, is there a way to continuously update a restored backup with
>>> differential or log files and make the database active for read only
>>> purposes? Replication is not an available option for me. It seems
>>> natural that a restoring backup should be allowed to be queried.
>>
>>Certainly, that's a feature known as log shipping.
>>
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-setup/201007/1
>
 >> Stay informed about: Multiple Backup Chains 

kingston via SQLMonster.com (u60998@uwe) writes:
> Thanks for answering my question, but perhaps I didn't explain my
> problem very well. I would like to have two sets of backups
> simultaneously. One is a daily full backup. The second is a weekly
> full backup with incremental backups. As I understand it, differential
> backups are made based on the most recent full backup. So the daily
> full backups will interfere with the backup chain of a differential set.
> I don't have that much experience with transaction log backups, but
> they also seem to rely on the complete backup chain. In other words, if
> I make full backup A, log backup B, full backup C, and then log backup
> D, can I restore A, B, and D after sending the files (without C) to
> another computer? I may be doing it wrong, but I've had trouble with
> this.

Before we go any further, let me ask: have you considered your restore
strategy?

If your database goes belly-up, how much data loss can you accept?
Is it OK to lose the last hour of data entry? The last day? The
last week?

If a user makes a fatal mistake, do you need to be able to restore the
database to the point just before the mistake?

And if a database dies, how long downtime can you accept?

Are you prepared to accept longer downtime in case of more fatal,
but less probable disasters like the entire data centre burning down?


I like to stress that these questions are by no means rethorical. For
many businesses it is perfectly OK to restore a backup which is one or
two days old.

But you need the answers to these questions to be able to design your
backup strategy.


With this in mind, let's look at the two options to make incremental
backups. For differential backups, it is indeed true that if you make
a full backup, the next differential backup will only include the changes
since that full backup. Unless, as Jeffery mentions, the backup was
taken with COPY_ONLY.

Log backups are different. Log backup only relate to each other. If
you take a full backup of the database everyday for a fortnight, and
then take a log backup, that backup will include everything that
happened in those two weeks. Now, I don't actually work much with
backup/restore as a admin, but I believe that you could restore any
of these backups and apply the log backup to them.

This is of course a weird example; a more regular procedure is to
take a full backup nightly, and then back up the transaction log
every 15 minutes.

Log backups are usually preferable over differential backups, since
only log backups can give you up-to-the-point recovery. Differential
backup may be more palatable from the sense that the restore operation
only includes two files, and not umpteen. But automating the log
restore is part of making your restore plan.

Finally, don't forget that restore strategies needs to be tested. You
have no use for those backups on the FTP site, if the FTP connection
regularly flips bits in the backup and corrupts them.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Multiple Backup Chains 

Thank you Jeff and Erland for your replies. It looks like COPY_ONLY is the
solution, except that the db is SQL2000K. My problem is twofold. My
company's IS group deals with full daily backups only; I understand the data
loss implications but there is no compromise. I would like to create a
reporting instance on another network (firewalled by the same IS group) but
daily restores are very untimely. I'll try again with log backups to
pinpoint why my past attempts did not work. Thanks again.


kingston wrote:
>Thanks for answering my question, but perhaps I didn't explain my problem
>very well. I would like to have two sets of backups simultaneously. One is
>a daily full backup. The second is a weekly full backup with incremental
>backups. As I understand it, differential backups are made based on the most
>recent full backup. So the daily full backups will interfere with the backup
>chain of a differential set. I don't have that much experience with
>transaction log backups, but they also seem to rely on the complete backup
>chain. In other words, if I make full backup A, log backup B, full backup C,
>and then log backup D, can I restore A, B, and D after sending the files
>(without C) to another computer? I may be doing it wrong, but I've had
>trouble with this.
>
>How do I implement that log shipping feature you mention on two untrusted
>networks? Again, I would like to send a full backup to another server 10,000
>miles away once a week. Every day, hour, 15 minutes, whatever, I would like
>to make a log backup, send it to the other server, restore it, and have the
>copy be continuously active for querying. Can a database be placed in
>NORECOVERY mode after it has been in RECOVERY mode so that more logs can be
>applied after the database has been queried? If so, how, or is there a way
>to replay logs on a "live" read-only database? The closest I can come up
>with is to have a second backup in NORECOVERY mode always available for the
>incoming log so that a switchover can be made in a matter of seconds rather
>than the amount of time it would take to restore the full backup and all
>subsequent log backups. Thanks again for any assistance.
>
>>> Is it possible to have multiple backup chains for a database? For
>>> example, I would like to maintain daily full backups on a local network
>[quoted text clipped - 12 lines]
>>
>>Certainly, that's a feature known as log shipping.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-setup/201007/1
 >> Stay informed about: Multiple Backup Chains 

kingston via SQLMonster.com (u60998@uwe) writes:
> Thank you Jeff and Erland for your replies. It looks like COPY_ONLY is
> the solution, except that the db is SQL2000K.

Moral: Always tell which solution of SQL Server you are using.

We discussed log shipping: here is a possibly important difference
between SQL 2000 and SQL 2005: In SQL 2000, it's only available in
Enterprise Edition; in SQL 2005, it's also available in Standard Edition.

Of course, no matter the edition, you can still do log shipping if
you roll your own.

> My problem is twofold. My company's IS group deals with full daily
> backups only; I understand the data loss implications but there is no
> compromise.

Do the IS group understand the loss implications?


--
Erland Sommarskog, SQL Server MVP, esquel.DeleteThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Multiple Backup Chains