October 14, 2003 at 11:54 am
I need some help with getting a point in time restore to work successfully. I need to restore my database to a time between 10/7/2003 6 pm and 10/8/2003 1 am. It doesn't matter what time it is between then. Data was corrected before 6 and a purge process was fired at 1 am so we want the data after it was corrected but before the purge process ran.
Here are the backups that we have:
10/7/2003 2:23 AM database backup
10/7/2003 2:26 AM transaction log backup
10/8/2003 2:23 AM database backup
10/8/2003 2:26 AM transaction log backup
Here is what I have tried so far unsuccessfully:
Restored both 10/7/2003 Db & trans log backups. I then tried to restore the 10/8/2003 trans log backup and specify a point in time that I stated above. When I try to specify a time, it says that the time specified is less than the minimum point in time allowed. Time corrected. It then sets the time to the beginning time of the database backup on 10/8/2003. I have tried several different combinations but have been unsuccessful to this point. What order do I need to restore these backups in? Any help would be appreciated.
Thanks!
John
I forgot to mention that the backups are on tape. So we have a 10/7/2003 tape and a 10/8/2003 tape. I believe this would be easier if we had a disk backup.
Edited by - laker_42 on 10/14/2003 12:05:02 PM
October 15, 2003 at 7:30 am
Can anyone verify whether the following is correct:
Full backups are restored completely. So you can restore your 10/8/2003 full backup which will restore EVERYTHING up to 2:23AM. Then you can restore the transaction log backup from 10/8/2003 and use WITH STOPAT for any time from 2:23AM to 2:26AM.
IF you had done your transaction log backup BEFORE the full backup (ie. TLog at 2:23 and FULL at 2:26), you would have been able to restore the 10/7 Full Backup and then the 10/8 TLog backup using WITH STOPAT the time on the 8th that you needed.
October 15, 2003 at 10:38 am
I believe that is correct.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 15, 2003 at 10:57 am
Smart move laker_42. I'm glad to see that you double check information that you are given.
And I really do mean that.
(for anyone who wonders what I'm talking about, laker_42 posted this request on another site and I'm the one who provided the information being verified).
-SQLBill
October 16, 2003 at 6:49 am
quote:
Can anyone verify whether the following is correct:Full backups are restored completely. So you can restore your 10/8/2003 full backup which will restore EVERYTHING up to 2:23AM. Then you can restore the transaction log backup from 10/8/2003 and use WITH STOPAT for any time from 2:23AM to 2:26AM.
IF you had done your transaction log backup BEFORE the full backup (ie. TLog at 2:23 and FULL at 2:26), you would have been able to restore the 10/7 Full Backup and then the 10/8 TLog backup using WITH STOPAT the time on the 8th that you needed.
Well, it's technically correct, but the second paragraph implies that you cannot restore to the time desired, which is incorrect. You can restore the 10/7 database backup with norecovery, then the 10/7 log backup with norecovery, and then the 10/8 log backup with stopat and recover up to any time up to when the 10/8 tran log backup was made.
RESTORE DATABASE <DatabaseName>
FROM DISK = 'c:\mssql\backup\1007dbbu.bak'
WITH NORECOVERY
RESTORE LOG <DatabaseName>
FROM DISK = 'c:\mssql\backup\1007logbu.bak'
WITH NORECOVERY
RESTORE LOG <DatabaseName>
FROM DISK = 'c:\mssql\backup\1008logbu.bak'
WITH STOPAT '2003-10-08 1:00'
Database (i.e. Full) backups do not break the sequence of tran log backups. That's one reason to keep at least two full backups and all subsequent log backups: if you find your last full backup is corrupt, you can still recover using the older full backup and applying all the subsequent tran log backups.
--Jonathan
--Jonathan
October 16, 2003 at 8:12 am
I stick by what I said...
Per BOL (Administering SQL Server, Backup and Restore Operations, Transaction Log Backups):
The section on Restoring Transaction Log Backups says this (bolding is mine for emphasis):
It is not possible to apply a transaction log backup:
-unless the database or differential database backup preceding the transaction log backup is restored first.
In Laker_42's case that means if the the 10/8 transaction log is going to be restored, the 10/8 full backup MUST be restored first.
-SQLBill
October 16, 2003 at 8:53 am
quote:
I stick by what I said...Per BOL (Administering SQL Server, Backup and Restore Operations, Transaction Log Backups):
The section on Restoring Transaction Log Backups says this (bolding is mine for emphasis):
It is not possible to apply a transaction log backup:
-unless the database or differential database backup preceding the transaction log backup is restored first.
In Laker_42's case that means if the the 10/8 transaction log is going to be restored, the 10/8 full backup MUST be restored first.
-SQLBill
Well, it worked fine the last time I did it...
The text you quote is out of context and therefore misleading. The context consists of several bullet points and you only cite the first. The second point is "Unless all preceding transaction logs created since the database or differential database was backed up are applied first."
This is in both SQL Server 7 and SQL Server 2000 BOL:
quote:
The second option points out the redundant security offered by a chain of transaction log backups that can be used to restore a database even if a database backup is lost. You can restore an earlier database backup, and then restore all of the transaction log backups created after the database backup was created.
Database backups do not truncate the tran log, so chains of tran log backups are independent of and unaffected by database backups.
--Jonathan
--Jonathan
October 16, 2003 at 10:02 am
Have you ever done this and had it work? I haven't had it work yet and the answer I keep getting is that the transaction log backups apply to the last FULL or DIFFERENTIAL backup made.
-SQLBill
October 16, 2003 at 10:11 am
quote:
Have you ever done this and had it work? I haven't had it work yet and the answer I keep getting is that the transaction log backups apply to the last FULL or DIFFERENTIAL backup made.-SQLBill
As I wrote; yes. It works fine. Whom are you getting that "answer" from?
--Jonathan
--Jonathan
October 17, 2003 at 5:47 am
From other tech sites that I use. I'll test it again the next time I test my backups. I've just never been able to get it to work without restoring the FULL backup that was made immediately before the transaction logs.
-SQLBill
October 17, 2003 at 8:12 am
quote:
From other tech sites that I use. I'll test it again the next time I test my backups. I've just never been able to get it to work without restoring the FULL backup that was made immediately before the transaction logs.-SQLBill
It's a common misconception. I had to prove it to the DBAs at a site I was consulting with last year.
use master
backup database pubs to disk = 'c:\mssql\backup\pubs1.bak' with init
backup log pubs to disk = 'c:\mssql\backup\pubslog1.bak' with init
backup database pubs to disk = 'c:\mssql\backup\pubs2.bak' with init
backup log pubs to disk = 'c:\mssql\backup\pubslog2.bak' with init
restore database pubs from disk = 'c:\mssql\backup\pubs1.bak' with norecovery
restore log pubs from disk = 'c:\mssql\backup\pubslog1.bak' with norecovery
restore log pubs from disk = 'c:\mssql\backup\pubslog2.bak'
--Jonathan
--Jonathan
October 17, 2003 at 8:49 am
Sorry to barge in without any deep knowledge. But doesn't the log get truncated when you perform a backup? Well, if the Truncate on checkpoint option is switched on?
If I would do a full backup, I would not require the transactions from before the full backup to restore my database. I allready have the data!
Obviously, you might want to backup your logs if you need to be able to restore to any point in time.
October 17, 2003 at 9:37 am
quote:
Sorry to barge in without any deep knowledge. But doesn't the log get truncated when you perform a backup?
The tran log is truncated by default when a tran log backup is performed. Any other type of backup will not and can not truncate the log.
quote:
Well, if the Truncate on checkpoint option is switched on?
If "truncate on checkpoint" (Simple recovery model in SQL 2000) is true, then there is no facility for tran log backups, so this is a non-issue.
quote:
If I would do a full backup, I would not require the transactions from before the full backup to restore my database. I allready have the data!
And if your last full backup is corrupted or inadvertantly lost?
quote:
Obviously, you might want to backup your logs if you need to be able to restore to any point in time.
Or if you wish to restore as currently as possible to a failure. If you truncate log on checkpoint, you will not be able to back up the tran log if something untoward happens to your database, so you will lose any changes subsequent to the last usable database backup. The only advantages to using the Simple recovery model are that you may save some disk space and you can shirk the easy task of backing up the tran log file(s). The disadvantages are worse performance and limited recoverability. That's not a tradeoff that any responsible DBA would make with a production database that's not read-only.
--Jonathan
--Jonathan
October 18, 2003 at 2:38 am
Jonathan, thanks for clearing this up. Some good advice to remember.
BTW, if I ever need to administer a real DB, I will surely get some external help. I don't have a lot of experience with DBA tasks. More of a developper knowledge to SQL Server.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply