Prev: SQL2K Agent Job Steps
Next: A Question about SQL Server 2008 Installation
From: Patrick on 8 Mar 2010 02:45
We are running daily database backup and transaction log backup every hour.

We would like to know if we make a full backup mid way the day (Like:
12:20pm), can we still be able to restore point in time to a time before
that full backup (Like: restore from last night backup and transaction
log backup to 11:15am) ?

Thanks
From: Tibor Karaszi on 8 Mar 2010 03:43
Nothing. A full backup do not break the log backup chain. Or, to phrase it
differently, a full backup doesn't truncate the log.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Patrick" <Patrick(a)discussions.microsoft.com> wrote in message
news:#JtkOOpvKHA.1796(a)TK2MSFTNGP02.phx.gbl...
> We are running daily database backup and transaction log backup every
> hour.
>
> We would like to know if we make a full backup mid way the day (Like:
> 12:20pm), can we still be able to restore point in time to a time before
> that full backup (Like: restore from last night backup and transaction log
> backup to 11:15am) ?
>
> Thanks

From: Uri Dimant on 8 Mar 2010 03:59
Yep ,sure
CREATE DATABASE test

ALTER DATABASE test SET RECOVERY FULL



USE test

GO

CREATE TABLE T (c INT)

INSERT INTO T VALUES (1)

INSERT INTO T VALUES (2)

BACKUP DATABASE Test TO DISK ='C:\Temp\test.bak'

INSERT INTO T VALUES (3)

INSERT INTO T VALUES (4)

BACKUP LOG Test TO DISK ='C:\Temp\LOG.bak' WITH INIT ---file=1



INSERT INTO T VALUES (5)

INSERT INTO T VALUES (6)

BACKUP LOG Test TO DISK ='C:\Temp\LOG.bak' WITH NOINIT ----file=2



---Now we did full backup in the middle

BACKUP DATABASE Test TO DISK ='C:\Temp\test_copy.bak'

use master

---backup log file first

BACKUP LOG Test TO DISK ='C:\Temp\LOG.bak' WITH NOINIT---file =3

RESTORE DATABASE test FROM disk = 'c:\Temp\test.bak' WITH FILE = 1,
norecovery

RESTORE LOG test FROM disk = 'C:\Temp\LOG.bak' WITH FILE = 1, recovery

USE Test

GO

---see only 4 rows (file=1)

SELECT * FROM t



Note

Yopu won be able to restore at point of time if your BACKUP in the middle
has the same name and issued WITH INIT option







"Patrick" <Patrick(a)discussions.microsoft.com> wrote in message
news:%23JtkOOpvKHA.1796(a)TK2MSFTNGP02.phx.gbl...
> We are running daily database backup and transaction log backup every
> hour.
>
> We would like to know if we make a full backup mid way the day (Like:
> 12:20pm), can we still be able to restore point in time to a time before
> that full backup (Like: restore from last night backup and transaction log
> backup to 11:15am) ?
>
> Thanks


From: Gerry Hickman on 9 Mar 2010 11:40
"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:uaIQ2spvKHA.812(a)TK2MSFTNGP06.phx.gbl...
> Nothing. A full backup do not break the log backup chain. Or, to phrase it
> differently, a full backup doesn't truncate the log.

There's also a potentially useful feature of SQL Server 2005 called
"Copy-only backup". This allows you to create a backup for a special purpose
without affecting the usual backup sequence.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "Patrick" <Patrick(a)discussions.microsoft.com> wrote in message
> news:#JtkOOpvKHA.1796(a)TK2MSFTNGP02.phx.gbl...
>> We are running daily database backup and transaction log backup every
>> hour.
>>
>> We would like to know if we make a full backup mid way the day (Like:
>> 12:20pm), can we still be able to restore point in time to a time before
>> that full backup (Like: restore from last night backup and transaction
>> log backup to 11:15am) ?
>>
>> Thanks
>

From: Jay Konigsberg on 9 Mar 2010 11:59
Also, if you restore a transaction log with records dating before the date
of the full backup, SQL Server will happily (and silently) skip over any
transaction log records dated before the backup.

--
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:uaIQ2spvKHA.812(a)TK2MSFTNGP06.phx.gbl...
> Nothing. A full backup do not break the log backup chain. Or, to phrase it
> differently, a full backup doesn't truncate the log.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "Patrick" <Patrick(a)discussions.microsoft.com> wrote in message
> news:#JtkOOpvKHA.1796(a)TK2MSFTNGP02.phx.gbl...
>> We are running daily database backup and transaction log backup every
>> hour.
>>
>> We would like to know if we make a full backup mid way the day (Like:
>> 12:20pm), can we still be able to restore point in time to a time before
>> that full backup (Like: restore from last night backup and transaction
>> log backup to 11:15am) ?
>>
>> Thanks
>


 |  Next  |  Last
Pages: 1 2
Prev: SQL2K Agent Job Steps
Next: A Question about SQL Server 2008 Installation