database and log backup

We are using SQL 2K with sp4. I like someone to verify what I am doing is
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore on
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
Wingman
Let me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup once, and then skip the
database backups performed on the production server and only restore the log backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups. Just be careful with
setting the production database to simple recovery or explicitly empty the log without backing up,
as that will break the log backups. This will of course affect #2 as well as #3, but since you are
periodically restoring the database backups on #2 it will self-repair within 24 hours.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:

> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman