March 17, 2004 at 12:24 pm
Hi everyone,
How can we restore the transaction log file using Query Analyzer in one go? and how do we check that it has restored the transaction log correctly?
Thanx for the help...
Muneeb.
March 17, 2004 at 1:38 pm
First you MUST restore a full backup with the WITH NORECOVERY option. Then you restore each transaction log in order using WITH NORECOVERY on all but the last one.
example:
RESTORE DATABASE mydb
FROM mydb_full
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log1
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY
Enterprise Manager will show the database restored.
-SQLBill
March 17, 2004 at 2:03 pm
Thanx SQLBill for your reply.
I'm using a standard version of the SQLServer 2000 and I in the middle of building my standby server. The method you mentioned to me, I have already using it with the exception of STANDBY option instead of NORECOVERY. I need to know if I use the FILE option with the RESTORE LOG command what would happened and if I don't use it. One more thing, how can I check if the logs are are restored properly.
Thanx.
Muneeb.
March 18, 2004 at 9:45 am
FILE is used to specify which backup set to restore from a backup device that contains multiple backup sets. For example, if your backup device contains log_bkup1, log_bkup2, log_bkup3, then FILE=1 specifies that log_bkup1 is to be restored, FILE=2 specifies log_bkup2, etc.
I believe that if you omit FILE, the default is to restore the first backup set.
Greg
Greg
March 18, 2004 at 10:19 am
Hi Greg,
Thanx for reply.
In continuation of my previous question. We have logs appending in the Transaction log file every 10 mins and I have to restore it on my standby server. If I check the log file using the following command:
restore headeronly from disk=''
it shows me 124 backupsets taken during the period from 2:00AM till 10:00PM. Now how do I restore it in one single go. I know I can restore it one by one as you mentioned but that process is time consuming...Thanx.
Muneeb.
March 18, 2004 at 11:28 am
The only way I know to restore all the backup sets without interruption is to do it in Enterprise Manager. All backup sets are listed and you can check all the one you want restored. I'll bet, if you run profiler while you're doing that, you'd see what the T-SQL commands are.
Greg
Greg
March 18, 2004 at 11:51 am
Thanx Greg,
Profiler is really a cool tool. check out the following link that I consulted for my standby server. Check out especially the restore log part which I'm using and my question are related to that.
http://www.sql-server-performance.com/sql_server_log_shipping.asp
thanx.
March 18, 2004 at 3:01 pm
I see what you're getting at, now. Consider this: The plan in the article specifies copying the log backup file to the standby server and restoring it immediately after the log is backed up on the production server. If you're backing up your t-logs every 10 minutes, you should be copying the backup file and restoring it on your standby server every 10 minutes, also.
Notice, too, in the detailed description of the backup job, the log is backed up WITH INIT. That means the backup file will only contain one backup at any given time. If you did this, you wouldn't have to worry about restoring multiple backup sets.
Greg
Greg
March 18, 2004 at 3:30 pm
Once again thanx for your reply Greg.
You see that's my problem. The article is using INIT which is an ideal case but right now I have to do the same thing in the production environment. And the Backup job for transaction log is not using INIT, it's actually appending in the file. I can do it manually to restore every single backup set using FILE option with RESTORE LOG and which I'm right now doing but I would like to do this automatically. Any suggestion or scripts would help me a lot.
Thanx.
March 19, 2004 at 7:15 am
If you are taking transaction log backups every 10 minutes and NOT using them immediately, what's the use of doing it that way?
I would change the backup method. You can still backup every 10 minutes, but you should add a differential backup every hour. Then all you really need to do is restore the differential to your standby server.
-SQLBill
March 19, 2004 at 12:35 pm
Thanx SQLBill.
So, does this means that if I take the differential back then I don't have to restore all the backup set in the Transaction file one by one manually. Will I be able to restore the differential log file in one go...
Thanx.
Muneeb
March 19, 2004 at 12:47 pm
Yes.
Transaction log backup is of everything since the last backup of any type.
Differential backup is of everything since the last FULL backup.
Scenerio: Midnight - Full Backup, every 10 Minutes a Transaction Log (TL) backup, every hour a Differential (Diff) backup.
Now at 1 PM your database crashes. You COULD restore the Full backup and every TL backup upto 1 PM (a total of 79 restores - 1 full and 78 TLs).
Or you could restore the Full backup and the 12 (noon) Diff backup and then all the TL's from noon to 1 pm (a total of 8 restores - 1 Full, 1 Diff, 6 TLs).
Why don't you need to restore ALL of the Diff backups? Because EACH Differential has ALL the changes since the last full backup. So each following DIFF has everything the last Diff backed up, plus anything since the last Diff.
-SQLBill
June 3, 2004 at 3:12 pm
I am having a problem with the log restore as well but the error I receive is
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Here is the restore log part
CREATE PROCEDURE restore_log_backup AS
RESTORE LOG dbname
FROM DISK = 'c:\mssql7\backup\db_backup_log.bak'
WITH
STANDBY = 'c:\undo.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'dbname', 'single user', true
GO
(dbname is substituted for the actual dbname)
I too am following sql-server-performance article...
Any ideas?
June 7, 2004 at 8:32 pm
Yes RH, I have also the same problem yesterday till didn't sloved. Today I am going to try to back up dagain tha transaction log and then restore the previous back up with norecovery. I hHope this will work. If not we both still have to search
kalyan
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply