Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restoring Transactional Log Backup

Author  Topic 

mp5555
Starting Member

9 Posts

Posted - 2013-04-15 : 13:01:34
Hi,
I am new to this forum.Could anyone help me with this questions please. I am not able to resore my log backup.
My plan for backup is daily full backup, and transactional log backup for every 5 minutes because we do not have much transactions.My data base recovery model is "Full", and I scheduled transactional Backup with time stamp file name, and it works. Now I have problem with restoring transactional log backup. I restored my Full backup with Recovery state " RECOVERY WITH STANDBY" , and Restore options "WITH REPLACE", but when I want to restore transactional log backup, it is grayed out.
Any help would be greatly appreciated.
Thank in advance,

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-15 : 13:04:51
You need to do with NO RECOVERY

-Chad
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 2013-04-15 : 13:21:59

could you explan more.? I do not do this on command line. I go to task -> restore -> on here Transactional Log is grayed out. How can active this?
Thanks


quote:
Originally posted by chadmat

You need to do with NO RECOVERY

-Chad

Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-15 : 13:36:10
You're going to need to re-run the full restore. for Recovery State, choose the option "RESTORE WITH NORECOVERY"

Then you can restore the tran logs.
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-15 : 13:37:24
When you restore the full backup, you have to specify not to recover (it is an option in the GUI). if the full backup goes through recovery, you can't restore subsequent log backups, thus that option is grayed out.

-Chad
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 2013-04-15 : 14:33:10
I restored db with no recover in GUI, and it was successful, but still transactional Log was grayed out. Then I refreshed DB it shows "Restoring" with Green upside arrow key on DB for a log time. Did I need to set something during providing full backup?
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-15 : 14:38:35
The full backup was taken while the db was in full recovery model? If so, You should be able to restore logs from here. Try scripting it rather than using the gui.

-Chad
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 2013-04-15 : 16:56:09
I am sorry, I went to Tasks -> Restore -> Files and Filegroups -> on the option : checked overwrite the existing database and on Recovery Stat : chose "RESTORE WITH NORECOVERY", and I restored my full backup successfully, but still Transaction Log on Restore is grayed out. Is there aything else I have to do?
Thanks for your response
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-16 : 11:33:55
Have you run the final statement , after the log backups , such as : RESTORE DATABASE MyDB WITH RECOVERY;

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-16 : 13:45:35
Why are you restoring files/filegroups? You should have a full database backup, and that is what needs to be restored with no recovery, not a filegroup backup.

-Chad
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 2013-04-16 : 17:29:02
When I want to restore my full db with no recovery on the same db I get this error (Exclusive access could not be access because db is in use ). Then I changed db to single user mode with this command:
"alter database adventureworks2008 set single_user with rollback immediate".
Then I entered this command " restore database adventureworks2008 from disk 'c:\adven\AdventureWorks2008_backup_2013_04_15_154154_0316286.bak' with norecovery " , and still I get this error: " The tail of the log for the database "adventureworks2008" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log."
Whereas I used maintenace plan wizard, and set up transactional log back up for specific interval. Any help would be appreciated.
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 2013-04-17 : 11:45:42
Do'nt worry .I fixed this issue.


quote:
Originally posted by mp5555

When I want to restore my full db with no recovery on the same db I get this error (Exclusive access could not be access because db is in use ). Then I changed db to single user mode with this command:
"alter database adventureworks2008 set single_user with rollback immediate".
Then I entered this command " restore database adventureworks2008 from disk 'c:\adven\AdventureWorks2008_backup_2013_04_15_154154_0316286.bak' with norecovery " , and still I get this error: " The tail of the log for the database "adventureworks2008" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log."
Whereas I used maintenace plan wizard, and set up transactional log back up for specific interval. Any help would be appreciated.

Go to Top of Page
   

- Advertisement -