473,569 Members | 2,610 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
ra*******@redif fmail.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
2 4392
Rajesh Garg <ra*******@redi ffmail.com> wrote in message news:<3f******* *************** *@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
ra*******@redif fmail.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
Jul 20 '05 #2
"Simon Hayes" <sq*@hayes.ch > wrote in message
news:60******** *************** ***@posting.goo gle.com...
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.
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4084
by: A.M. de Jong | last post by:
In the hereunder written message I talk about point in time restore. It is now based upon the fact that there are no hardware problems or what so ever. I just would like to roll back to a situation of some time (minutes, hours or what ever) ago. Used to the ingres database a point in time restore can take place UP to any, any, any time...
3
9101
by: Tim Morrison | last post by:
MSDE2000 I have an application in which I am running a TSQL command of BACKUP DATABASE and RESTORE DATABASE for the backup and restore commands for my application. For testing purposes, i did the following: 1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK. 2) Deleted the database completely. 3) Ran a RESTORE DATABASE on the...
1
1927
by: Andre | last post by:
Hi, I have a database (or better: used to have) and backup consisting of - the initial (complete) database - all log files since then (or so I thought) After making a data entry error I wrote the log to the backup and tried a point of time restore. Unfortunately that failed with the message "The log in this backup set begins at LSN xxx,...
0
2063
by: barbara | last post by:
I am using this procedure from net for getting the restore script. It lists the latest full backup file name and logs after that point. Is there any way to modify this script to take either date or time as parameter and give the files for restore? I need this if I need to restore the data up to last week/upto some point of time. CREATE ...
5
1750
by: Pagus | last post by:
I have full backup of database at 13:00 and another full backup at 17:00. I've made backup of transaction log at 17:05 When I try to restore database to state at 15:10 (point in time) , the dialogue in Enterprise Manager says that only time after 17:05 is valid. It seems to me that I've done something wrong at 17:05 while taking
3
2243
by: butatista | last post by:
Please help SQL Server 2000 Standard I am trying to restore databases on my server and am unable to get them to restore using the database option. For example, the location of my backup file is: D:\MSSQL\BACKUP\MY_DB\MY_DB_ddmmyy.bak
2
3157
by: Wit Wiech | last post by:
Database is OK. I just need to roll back all the transactions until certain point in time. How to do it?
9
13965
by: GL | last post by:
I am running DB2 8.1.1 on AIX 5.1 Having a problem with a redirected restore. Once into the restore continue phase, I immediately get the following “SQL2059W A device full warning was encountered on device "TBS_IDX". Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) t
2
8266
by: l0b0 | last post by:
I'm working on a restore procedure for the case where all MDF files are missing, but the LDF files are all intact. A full backup is done every 24 hours, and a log backup is done every 3 hours. After restoring the last full + log backups, is it at all possible to use the LDF files to recover data from that point up to a newer point in time? ...
0
7628
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8145
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7694
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6317
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5519
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3679
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3667
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2128
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.