restaurar backups de Log...

bajopalabra,

BACKUP DATABASE "test" to "bkp" with recovery

La opcion "with recovery" aplica solo cuando haces backup de log de
trasacciones

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

Los cuatro backups los haces hacia un mismo device. por lo que ese device
contendra 4 sets de backup. Eso lo puedes ver usando el comando:

RESTORE HEADERONLY FROM bkp
go

Por lo que cuando haces el "restore", debes decirle a SQL Server cual de los
backups vas a restaurar. Si no especificas, cojera el primero en la lista. Es
como si especificaras "with file = 1" en el restore de la db y "with file =
2" en el restore del log.

A continuacion te pongo un ejemplo para que puedas ver lo que sucede con
cada backup.

use master
go

create database test
go

use test
go

create table dbo.test (
c1 varchar(15)
)
go

INSERT into dbo.test values ( 'one' )
go

BACKUP DATABASE "test" to "bkp" with init
go

INSERT into dbo.test values ( 'two' )
go

BACKUP LOG "test" to "bkp"
go

INSERT into dbo.test values ( 'three' )
go

BACKUP LOG "test" to "bkp"
go

use master
go

alter database test
set single_user
with no_wait
go

BACKUP log "test" to "bkp" with norecovery
go

RESTORE HEADERONLY FROM bkp
go

RESTORE DATABASE "test" from "bkp" with file = 1, standby =
'c:\temp\undo_file'
go

use test
go

select * from dbo.test
go

use master
go

RESTORE LOG "test" from "bkp" with file = 2, standby = 'c:\temp\undo_file'
go

use test
go

select * from dbo.test
go

use master
go

RESTORE LOG "test" from "bkp" with file = 3, standby = 'c:\temp\undo_file'
go

use test
go

select * from dbo.test
go

use master
go

RESTORE LOG "test" from "bkp" with file = 4, recovery
go

use test
go

select * from test
go

use master
go

alter database test
set single_user
with rollback immediate
go

drop database test
go


AMB


"bajopalabra" wrote:

hola
estoy tratando de entender cómo funcionan
los "backup log/restore log"
tomando algunos ejemplos de MSDN
http://msdn2.microsoft.com/en-us/li...87495.aspx
pero finalmente no comprendo

esta es mi prueba
insert 'one' en tabla "test" >> y hago backup DB
insert 'two' en tabla "test" >> y hago backup LOG
insert 'three' en tabla "test" >> y hago backup LOG

pensando que :
- el backup full de la DB contiene la fila ( 'one' )
- el primer backup del LOG contiene la fila ( 'two' )
- el segundo backup del LOG contiene la fila ( 'three' )

pero después del hacer lo último para restaurar
RESTORE LOG with RECOVERY
solo queda la fila 'one'

gracias

este es el código que uso


USE master

INSERT into test.dbo.test values ( 'one' )
BACKUP DATABASE "test" to "bkp" with format

INSERT into test.dbo.test values ( 'two' )
BACKUP LOG "test" to "bkp"

INSERT into test.dbo.test values ( 'three' )
BACKUP LOG "test" to "bkp"


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