BackUp Log ...

hi
I'm trying to understand how "backup log/restore log" works
taking some examples from MSDN
http://msdn2.microsoft.com/en-us/library/ms187495.aspx
but finally, don't understand
this is my test
--
insert 'one' in table "test" >> then backup DB
insert 'two' in table "test" >> then backup LOG
insert 'three' in table "test" >> then backup LOG
thinking that :
- full DB backup contains the row ( 'one' )
- the first LOG backup contains the row ( 'two' )
- and second LOG backup contains the row ( 'three' )
but after the last RESTORE LOG ... with RECOVERY
just the 'one' row is restored
thanks
hope you can guide me
--
this is the CODE i'm trying:
--
USE master
INSERT into test.dbo.clients values ( 'one' )
BACKUP DATABASE "test" to "bkp" with format
INSERT into test.dbo.clients values ( 'two' )
BACKUP LOG "test" to "bkp"
INSERT into test.dbo.clients values ( 'three' )
BACKUP LOG "test" to "bkp"
-- backup log tail // start restauration
BACKUP DATABASE "test" to "bkp" with recovery
RESTORE DATABASE "test" from "bkp" with norecovery
RESTORE LOG "test" from "bkp" with norecovery
RESTORE LOG "test" from "bkp" with norecovery
RESTORE LOG "test" from "bkp" with NOrecovery
--
atte,
HernánIs the code you posted the exact code you executed? Seems strange as you didn't specify which file
from the backup device to restore. Check out the FILE option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:%23A71BtIlGHA.1664@.TK2MSFTNGP03.phx.gbl...
> hi
> I'm trying to understand how "backup log/restore log" works
> taking some examples from MSDN
> http://msdn2.microsoft.com/en-us/library/ms187495.aspx
> but finally, don't understand
> this is my test
> --
> insert 'one' in table "test" >> then backup DB
> insert 'two' in table "test" >> then backup LOG
> insert 'three' in table "test" >> then backup LOG
> thinking that :
> - full DB backup contains the row ( 'one' )
> - the first LOG backup contains the row ( 'two' )
> - and second LOG backup contains the row ( 'three' )
> but after the last RESTORE LOG ... with RECOVERY
> just the 'one' row is restored
> thanks
> hope you can guide me
> --
> this is the CODE i'm trying:
> --
> USE master
> INSERT into test.dbo.clients values ( 'one' )
> BACKUP DATABASE "test" to "bkp" with format
> INSERT into test.dbo.clients values ( 'two' )
> BACKUP LOG "test" to "bkp"
> INSERT into test.dbo.clients values ( 'three' )
> BACKUP LOG "test" to "bkp"
> -- backup log tail // start restauration
> BACKUP DATABASE "test" to "bkp" with recovery
> RESTORE DATABASE "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with NOrecovery
>
> --
> atte,
> Hernán
>|||yes, thanks
i forgot FILE keyword
atte,
Hernán
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió
en el mensaje news:uy6OnkJlGHA.3588@.TK2MSFTNGP02.phx.gbl...
| Is the code you posted the exact code you executed? Seems strange as you
didn't specify which file
| from the backup device to restore. Check out the FILE option of the
RESTORE command.
|
| --
| Tibor Karaszi, SQL Server MVP
| http://www.karaszi.com/sqlserver/default.asp
| http://www.solidqualitylearning.com/
|
|
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:%23A71BtIlGHA.1664@.TK2MSFTNGP03.phx.gbl...
| > hi
| > I'm trying to understand how "backup log/restore log" works
| > taking some examples from MSDN
| > http://msdn2.microsoft.com/en-us/library/ms187495.aspx
| > but finally, don't understand
| >
| > this is my test
| > --
| > insert 'one' in table "test" >> then backup DB
| > insert 'two' in table "test" >> then backup LOG
| > insert 'three' in table "test" >> then backup LOG
| >
| > thinking that :
| > - full DB backup contains the row ( 'one' )
| > - the first LOG backup contains the row ( 'two' )
| > - and second LOG backup contains the row ( 'three' )
| >
| > but after the last RESTORE LOG ... with RECOVERY
| > just the 'one' row is restored
| >
| > thanks
| > hope you can guide me
| >
| > --
| > this is the CODE i'm trying:
| > --
| >
| > USE master
| >
| > INSERT into test.dbo.clients values ( 'one' )
| > BACKUP DATABASE "test" to "bkp" with format
| >
| > INSERT into test.dbo.clients values ( 'two' )
| > BACKUP LOG "test" to "bkp"
| >
| > INSERT into test.dbo.clients values ( 'three' )
| > BACKUP LOG "test" to "bkp"
| >
| > -- backup log tail // start restauration
| >
| > BACKUP DATABASE "test" to "bkp" with recovery
| >
| > RESTORE DATABASE "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with NOrecovery
| >
| >
| > --
| > atte,
| > Hernán
| >
| >
||||bajopalabra wrote:
> yes, thanks
> i forgot FILE keyword
>
...you also have to restore the last logfile with RECOVERY and not
NORECOVERY. Otherwise your database will expect more logfiles to be applied.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||yes, i knew that
(just a transcription mistake)
atte,
Hernán
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> escribió en el mensaje
news:%23HU3RRRlGHA.1832@.TK2MSFTNGP04.phx.gbl...
| bajopalabra wrote:
| > yes, thanks
| > i forgot FILE keyword
| >
| >
| ...you also have to restore the last logfile with RECOVERY and not
| NORECOVERY. Otherwise your database will expect more logfiles to be
applied.
|
|
| --
| Regards
| Steen Schlüter Persson
| Databaseadministrator / Systemadministrator