Do sequences of transactions logs are broken when a restore occur ?

Hi,
Do sequences of transactions logs are broken when a restore occur ?
Thank you
danny> Do sequences of transactions logs are broken when a restore occur ?
I am not sure if I understand the question. When you do the restore db and
log over an existing database, you overwrite the data files and the log
files. Sequence is constant and not broken in the restored database, but of
course in the original one you could have had different LSN. It is
overwritten, so why care?
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked th=is
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (with=out
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout l=osing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also cor=rupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the=
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and r=estore
all transactions log (sequentially) from june 07 untill now without losin=g data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to =June
28 even though the operator has restored the database (from June 14) on J=une 21
? In others words, do sequences of transactions logs has been broken on J=une 21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a =E9crit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db =and
> log over an existing database, you overwrite the data files and the log=
> files. Sequence is constant and not broken in the restored database, bu=t of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||Danny, now I see what you mean. When you issue the recovery process (i.e.
put the database in operational mode after last restore), SQL Server
restarts LSN (Log Sequence Number) from different number from the last
backup. Thus your log backups from
Fri. June 20 Transaction log backup
and
Sun. june 22 Transaction log backup
are not connected anymore- you have a hole in LSN's. So it is not possible
to restore the 2nd log backup mentioned after the 1st mentioned.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Danny Presse" <danny.presse@.sympatico.ca> wrote in message
news:3F0037BF.200A1DBD@.sympatico.ca...
Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked this
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (without
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout
losing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also
corrupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and
restore
all transactions log (sequentially) from june 07 untill now without losing
data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to
June
28 even though the operator has restored the database (from June 14) on June
21
? In others words, do sequences of transactions logs has been broken on June
21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a écrit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db and
> log over an existing database, you overwrite the data files and the log
> files. Sequence is constant and not broken in the restored database, but
of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.orgsql