|
|
Posted by Erland Sommarskog on 09/21/06 21:49
mcaglar@cs.ucf.edu (mcaglar@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|