Point in Time Backup (impossible for some points?)

[mcaglar]

Hello,

I am using SQL Server 2000 with SP4. I have a database with two full
backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
PM. Is there a possible way to do a point in time restore to 4:30 PM,
that is between two full backups?

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM. Is the transaction log
truncated at each full backup? If so, even if you take transactional
log backup every ten minutes, and full backups every once in a while,
there will be some point in time which cannot be recovered to, namely
the time between a transactional log backup and a full backup. Take a
log backup at 4:50, and full backup at 5:00 and you can never recover
to 4:55, can you?

Any insight on the topic will be appreciated,

Regards,

M. Baris Caglar

[Erland Sommarskog]

mc*****@cs.ucf.edu (mc*****@cs.ucf.edu) writes:

I am using SQL Server 2000 with SP4. I have a database with two full
backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
PM. Is there a possible way to do a point in time restore to 4:30 PM,
that is between two full backups?

Yes, restore the backup from 16:00 with NORECOVERY and then the
transaction log with the STOPAT option. Check the exact syntax in
Books Online.

This presumes that the log chain was never broken. That is the most
previous T-log backup of any kind must have been taken before 16:00.
SQL Server will tell you if this is the ase.

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM.

Don't really know what you mean, but if you are using some GUI, I
don't really know what happens. I prefer to use T-SQL commands.

Is the transaction log truncated at each full backup?

No. BACKUP DATABASE backs up the database, and all it does with the
log is to write a log record.

But if the database was taken as part of a job, that job may include a
backup of the transaction log as well. At worst, it includs a backup
with any of the options TRUNCATE_ONLY of NO_LOG which just throws
the logs away, without saving them anywhere.

There are tables in msdb where you can see at which points various sorts
of backups were taken. I don't use these tables very often myself, so
I can't give you an exact query to run.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mcaglar]

I had find the exact same solution at a different thread in this group
and it worked, but thank you for your response. Interestingly,
Enterprise manager does not allow to perform such action. I wonder if
this was a bug or a design issue. Does anyone know if this peoblem is
fixed on SQL Server 2005?

Baris

Erland Sommarskog wrote:

mc*****@cs.ucf.edu (mc*****@cs.ucf.edu) writes:

I am using SQL Server 2000 with SP4. I have a database with two full
backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
PM. Is there a possible way to do a point in time restore to 4:30 PM,
that is between two full backups?

Yes, restore the backup from 16:00 with NORECOVERY and then the
transaction log with the STOPAT option. Check the exact syntax in
Books Online.

This presumes that the log chain was never broken. That is the most
previous T-log backup of any kind must have been taken before 16:00.
SQL Server will tell you if this is the ase.

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM.

Don't really know what you mean, but if you are using some GUI, I
don't really know what happens. I prefer to use T-SQL commands.

Is the transaction log truncated at each full backup?

No. BACKUP DATABASE backs up the database, and all it does with the
log is to write a log record.

But if the database was taken as part of a job, that job may include a
backup of the transaction log as well. At worst, it includs a backup
with any of the options TRUNCATE_ONLY of NO_LOG which just throws
the logs away, without saving them anywhere.

There are tables in msdb where you can see at which points various sorts
of backups were taken. I don't use these tables very often myself, so
I can't give you an exact query to run.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

mc*****@cs.ucf.edu (mc*****@cs.ucf.edu) writes:

I had find the exact same solution at a different thread in this group
and it worked, but thank you for your response. Interestingly,
Enterprise manager does not allow to perform such action. I wonder if
this was a bug or a design issue. Does anyone know if this peoblem is
fixed on SQL Server 2005?

Enterprise Manager is not included in SQL 2005, neither is Query Analyzer.
Both tools have been superceded by SQL Server Management Studio. Whether
the GUI dialogs in Mgmt Studio would make this operation available to you
I don't know. In any case, the GUI are just wrappers on the T-SQL commands,
and you can always use T-SQL when the GUI does not expose a certain piece
of functionality.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx