473,837 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to properly restore transaction log

Hi,

Currently, I am on SQL2000 SP3 and Windows 2003.

I am trying to restore the full database backup and transaction log
backup on the development server. The full database restore is not a
problem, but the transaction log restore happened during the day is.
Everytime when I try to compare the transaction in the database
(select the last update date from one of my table as a benchmark, then
backup the transaction log for restore) to the development db with the
restored transaction log, I got none of the transaction from the log
backup.

What did I do wrong? Is my concept about using the transaction log
restore wrong?

Thanks in advance.
Jul 20 '05 #1
4 2088
Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar
Jul 20 '05 #2
Hi Manoj,

Thank you very much for the steps on restoring. Actually from your
restore logs description, I have a question. It seems that you are
expecting that there are multiple log backups to restore and they
should be done with the backup option WITH INIT. However, I am trying
to use the backup option WITH NOINIT, NO_TRUNCATE, NOUNLOAD, NOSKIP,
STATS = 10. And from the restore of that one log backup, I am unable
to obtain any transaction which is supposed to be backup all through
the day. Am I using the wrong backup options?

Thanks in advance.

ma**********@ya hoo.com (Manoj Rajshekar) wrote in message news:<ab******* *************** ***@posting.goo gle.com>...
Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar

Jul 20 '05 #3
Hi Manoj,

Thank you very much for the steps on restoring. Actually from your
restore logs description, I have a question. It seems that you are
expecting that there are multiple log backups to restore and they
should be done with the backup option WITH INIT. However, I am trying
to use the backup option WITH NOINIT, NO_TRUNCATE, NOUNLOAD, NOSKIP,
STATS = 10. And from the restore of that one log backup, I am unable
to obtain any transaction which is supposed to be backup all through
the day. Am I using the wrong backup options?

Thanks in advance.

ma**********@ya hoo.com (Manoj Rajshekar) wrote in message news:<ab******* *************** ***@posting.goo gle.com>...
Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar

Jul 20 '05 #4

"xo55ox" <xo****@hotmail .com> wrote in message
news:ab******** *************** ***@posting.goo gle.com...
Hi Manoj,

Thank you very much for the steps on restoring. Actually from your
restore logs description, I have a question. It seems that you are
expecting that there are multiple log backups to restore and they
should be done with the backup option WITH INIT. However, I am trying
to use the backup option WITH NOINIT, NO_TRUNCATE, NOUNLOAD, NOSKIP,
STATS = 10. And from the restore of that one log backup, I am unable
to obtain any transaction which is supposed to be backup all through
the day. Am I using the wrong backup options?
Yes, you need with NO_RECOVERY or STANDBY.

Otherwise all transactions are rolled back or forward and no further
restores can be applied.


Thanks in advance.

ma**********@ya hoo.com (Manoj Rajshekar) wrote in message

news:<ab******* *************** ***@posting.goo gle.com>...
Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar

Jul 20 '05 #5

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

Similar topics

1
4092
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 since the last FULL backup. (any time up to now !!!) I can't understand why a point in time restore...
2
4409
by: Rajesh Garg | last post by:
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
0
2089
by: xo55ox | last post by:
Hi, I have been trying to set up an automated restore process from prod to backup server. First, I schedule the full database backup nightly, transfer the backup file and restore it to the database on the backup server. Meanwhile, I leave the database ready to accept transaction log from the transaction log backup at noon daily. And I had used different restore options to test out the transaction that was being applied. And I couldn't...
1
1251
by: sumGirl | last post by:
When you restore a backup from a point in time, how do you then know which transaction ID to start with when you want to roll forward from that point in time to another point in time?
5
1760
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
2
1921
by: Michael Bourgon | last post by:
I need to build an automated email that gives the completion messages when a database is restored (i.e. "Executed as user: sa. Executing RESTORE DATABASE DB1 FROM DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY (Message 0) Processed 3816 pages for database 'DB1', file 'DB1_Data' on file 1. (Message 4035) Processed 1 pages for database 'DB1', file 'DB1_Log' on file 1. (Message 4035)") Currently, the Job History box contains...
2
2001
by: si.downes | last post by:
Using SQL Server 2000 SP3 I'm developing a data warehouse where data will be archived off to a filegroup, this filegroup backed up and the tables in this filegroup truncated to free up space on the server. So using Enterprise Manager I've copied data to the filegroup tables, backed up the file group, truncated the tables, but when I have restored the file group the database is stuck in a state of '(loading...)' and I can't get it to...
3
3412
by: t2581 | last post by:
Hi , I run restore with rollforward In job in output, last commited transaction time less then backup image time Is it normal ? RESTORE DATABASE PRICE_V8 FROM "E:\BACKUP" TAKEN AT 20050610095841 TO "D:" INTO PR_READ NEWLOGPATH "D:\DB2_FILES\PR_READ\LOG" WITH 2 BUFFERS BUFFER 1024 REDIRECT PARALLELISM 1 WITHOUT PROMPTING .....
5
6116
by: patrick | last post by:
Mysql 4.1.15 on Win2k. Using InnoDB. Using the mysql administrator gui to create a backup, everything goes fine, and restores quickly. Using the command line: mysqldump %dbname% --single-transaction > %dbname%.sql creates a file about 15% smaller than the gui produces, and is
0
10877
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10273
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9402
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7806
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7001
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5849
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4474
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
2
4041
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3124
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.