Restore database to a certain point of time.

I will make it simpler to look...

I have DB1 - as backup for day 1
LOg1 as backup of logs

T1 T2 T3 T4 T5 ...some transaction on day 2

Now i backup again
DB2
Log2

I want to restore the database till the point of transaction T3 say. I
know the time or i assume a certain time.
Is this possible ....i tried several options but hand in between for
some reason or the other. How can i achieve my solution. Is there some
extra parameter i will require or what...i am wondering now that it is
not at all possible. Please help.
RVG
If possible guys can you please mail me the sloution on
raj_chins@.rediffmail.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rajesh Garg <raj_chins@.rediffmail.com> wrote in message news:<3f4c2fb2$0$62083$75868355@.news.frii.net>...
> I will make it simpler to look...
> I have DB1 - as backup for day 1
> LOg1 as backup of logs
> T1 T2 T3 T4 T5 ...some transaction on day 2
> Now i backup again
> DB2
> Log2
> I want to restore the database till the point of transaction T3 say. I
> know the time or i assume a certain time.
> Is this possible ....i tried several options but hand in between for
> some reason or the other. How can i achieve my solution. Is there some
> extra parameter i will require or what...i am wondering now that it is
> not at all possible. Please help.
> RVG
> If possible guys can you please mail me the sloution on
> raj_chins@.rediffmail.com
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Your description is a little confusing for two reasons - first, you
don't say what time each event is happening at, and second, it's not
clear if you are backing up your log regularly or only once each day.
My guess from your description is that you are doing this:

Day 1

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Let's say it's now 23h30 on day 2, and you want to recover your
database to 11h00 on day 2. If my assumptions are correct, then you
cannot recover to a point in time because you have no transaction log
backups during Day 2. To recover to a point in time, you first recover
a full backup, then recover your log backups, one after another. But
in your scenario you have no series of transaction backups. At the end
of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
(full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
(full backup + log backup).

To recover to a point in time, you need at least one transaction log
backup after 11h00:

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

13h00 - BACKUP LOG...

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Now you can recover to 11h00: restore day 1 22h00 full + day 1 22h30
log + day 2 13h00 log, recovering only transactions before 11h00.

I may have misunderstood what your situation is - if so, then please
post a clear description of exactly what you are doing (in a similar
way to my example above). In the meantime, I suggest you review the
Books Online backup/restore section, in particular "Using Recovery
Models" and "Backup and Restore Operations".

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0308270354.789e8248@.posting.google.c om...
> Your description is a little confusing for two reasons - first, you
> don't say what time each event is happening at, and second, it's not
> clear if you are backing up your log regularly or only once each day.
> My guess from your description is that you are doing this:
> Day 1
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Day 2
> 09h00 - COMMIT txn1
> 10h00 - COMMIT txn2
> 11h00 - COMMIT txn3
> 12h00 - COMMIT txn4
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Let's say it's now 23h30 on day 2, and you want to recover your
> database to 11h00 on day 2. If my assumptions are correct, then you
> cannot recover to a point in time because you have no transaction log
> backups during Day 2. To recover to a point in time, you first recover
> a full backup, then recover your log backups, one after another. But
> in your scenario you have no series of transaction backups. At the end
> of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
> (full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
> (full backup + log backup).
> To recover to a point in time, you need at least one transaction log
> backup after 11h00:

This is not true (see my reply to Rajesh), a full backup does not truncate
the transaction log, so in your example it is possible to restore from the
first full backup and then use the two transaction logs, completely ignoring
the second full backup.

Ian.

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
--
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACEYou are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>