Point in time recovery with differential backup

Consider this scenerio in SQL 2000...
I have a full backup of a DB taken at 6AM on Sunday. After the full
backup - a T-Log backup is taken every hour. At 6PM a process is run where
a differential backup is taken and the T-Log backups between 6AM and 6PM are
deleted. Should I still be able to do a point in time recovery to a time
like 3PM?
When trying to invoke a point in time recovery from SQL EM - I get a message
indicating that the earliest time allowed for me to specify is 6PM (the time
of the differential backup).
I am thinking that perhaps SQL Server does not have the ability to recover
to a point in time in the differential backup... like it's all or nothing.
Is this true or am I most likely doing something wrong?
Thanks in advance.
Hi,
No, you cant do a point in recovery if your trasnaction log backup files are
deleted.
Otherwise you should have done the belwo steps:-
1. Restore the full backup with NORECOVERY to a new database
2. Restore the subsequent transaction log backups till 02 PM with NORECOVERY
3. Restore the 3PM traansaction log backup with STOPAT option and WITH
RECOVERY.
THis will do a POINT-IN-TIME recovery in the new database.
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:es9xcqAWEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Consider this scenerio in SQL 2000...
> I have a full backup of a DB taken at 6AM on Sunday. After the full
> backup - a T-Log backup is taken every hour. At 6PM a process is run
where
> a differential backup is taken and the T-Log backups between 6AM and 6PM
are
> deleted. Should I still be able to do a point in time recovery to a time
> like 3PM?
> When trying to invoke a point in time recovery from SQL EM - I get a
message
> indicating that the earliest time allowed for me to specify is 6PM (the
time
> of the differential backup).
> I am thinking that perhaps SQL Server does not have the ability to recover
> to a point in time in the differential backup... like it's all or nothing.
> Is this true or am I most likely doing something wrong?
> Thanks in advance.
>

Point in time recovery with differential backup

Consider this scenerio in SQL 2000...
I have a full backup of a DB taken at 6AM on Sunday. After the full
backup - a T-Log backup is taken every hour. At 6PM a process is run where
a differential backup is taken and the T-Log backups between 6AM and 6PM are
deleted. Should I still be able to do a point in time recovery to a time
like 3PM?
When trying to invoke a point in time recovery from SQL EM - I get a message
indicating that the earliest time allowed for me to specify is 6PM (the time
of the differential backup).
I am thinking that perhaps SQL Server does not have the ability to recover
to a point in time in the differential backup... like it's all or nothing.
Is this true or am I most likely doing something wrong?
Thanks in advance.Hi,
No, you cant do a point in recovery if your trasnaction log backup files are
deleted.
Otherwise you should have done the belwo steps:-
1. Restore the full backup with NORECOVERY to a new database
2. Restore the subsequent transaction log backups till 02 PM with NORECOVERY
3. Restore the 3PM traansaction log backup with STOPAT option and WITH
RECOVERY.
THis will do a POINT-IN-TIME recovery in the new database.
--
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:es9xcqAWEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Consider this scenerio in SQL 2000...
> I have a full backup of a DB taken at 6AM on Sunday. After the full
> backup - a T-Log backup is taken every hour. At 6PM a process is run
where
> a differential backup is taken and the T-Log backups between 6AM and 6PM
are
> deleted. Should I still be able to do a point in time recovery to a time
> like 3PM?
> When trying to invoke a point in time recovery from SQL EM - I get a
message
> indicating that the earliest time allowed for me to specify is 6PM (the
time
> of the differential backup).
> I am thinking that perhaps SQL Server does not have the ability to recover
> to a point in time in the differential backup... like it's all or nothing.
> Is this true or am I most likely doing something wrong?
> Thanks in advance.
>

Point in time recovery with differential backup

Consider this scenerio in SQL 2000...
I have a full backup of a DB taken at 6AM on Sunday. After the full
backup - a T-Log backup is taken every hour. At 6PM a process is run where
a differential backup is taken and the T-Log backups between 6AM and 6PM are
deleted. Should I still be able to do a point in time recovery to a time
like 3PM?
When trying to invoke a point in time recovery from SQL EM - I get a message
indicating that the earliest time allowed for me to specify is 6PM (the time
of the differential backup).
I am thinking that perhaps SQL Server does not have the ability to recover
to a point in time in the differential backup... like it's all or nothing.
Is this true or am I most likely doing something wrong?
Thanks in advance.Hi,
No, you cant do a point in recovery if your trasnaction log backup files are
deleted.
Otherwise you should have done the belwo steps:-
1. Restore the full backup with NORECOVERY to a new database
2. Restore the subsequent transaction log backups till 02 PM with NORECOVERY
3. Restore the 3PM traansaction log backup with STOPAT option and WITH
RECOVERY.
THis will do a POINT-IN-TIME recovery in the new database.
--
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:es9xcqAWEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Consider this scenerio in SQL 2000...
> I have a full backup of a DB taken at 6AM on Sunday. After the full
> backup - a T-Log backup is taken every hour. At 6PM a process is run
where
> a differential backup is taken and the T-Log backups between 6AM and 6PM
are
> deleted. Should I still be able to do a point in time recovery to a time
> like 3PM?
> When trying to invoke a point in time recovery from SQL EM - I get a
message
> indicating that the earliest time allowed for me to specify is 6PM (the
time
> of the differential backup).
> I am thinking that perhaps SQL Server does not have the ability to recover
> to a point in time in the differential backup... like it's all or nothing.
> Is this true or am I most likely doing something wrong?
> Thanks in advance.
>sql

Please helpLog file will not reduce in SQL 2000 database

My Database plan is as follows:

Full Database backup every night, Full recovery mode.
T-Log backups , every 4 hours.

both are appended to media,

there are no active transactions,

but Log file will not shrink or reduce in size.

what would you suggest.

Thanks,Pls refer the below links,
http://support.microsoft.com/kb/110139 and http://support.microsoft.com/kb/873235
if you need to shrink the tran log you can perform as below,

Backup log DATABASENAME with truncate_only and then shrink the log file using DBCC shrinkfile command refer BOL for the syntax ! but it is not recommended to shrink file often

Thanxx
Deepak
|||

I guess you mean the physical LDF file does not reduce in size? While a backup of the log file truncates the contents of the log, it will not give that space back to the operating system. This is by design. In almost all cases, there is no point in shrinking the physical file every time it is backed up as it will almost certainly need to grow again in the near future.

To actually increase the size of the file, SQL Server has to request space from the OS and this is quite a "costly" operation in terms of resources. Therefore, you want to minimise the amount of times the log file has to grow and the easiest way of doing this is to keep it at its "optimum" size.ie your log file should be sized according to how large it will typically grow.

If your Transcation Log is huge (maybe due to a one off reindex or data purge) then you can physically shrink it by issuing a DBCC SHRINKFILE statement. Check Books Online for details.

HTH!