SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restoring Transactional Log Backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mp5555
Starting Member

9 Posts

Posted - 04/15/2013 :  13:01:34  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/15/2013 :  13:04:51  Show Profile  Visit chadmat's Homepage  Reply with Quote
You need to do with NO RECOVERY

-Chad
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 04/15/2013 :  13:21:59  Show Profile  Reply with Quote

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

USA
5072 Posts

Posted - 04/15/2013 :  13:36:10  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 04/15/2013 13:36:52
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/15/2013 :  13:37:24  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 04/15/2013 :  14:33:10  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/15/2013 :  14:38:35  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 04/15/2013 :  16:56:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 04/16/2013 :  11:33:55  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
1974 Posts

Posted - 04/16/2013 :  13:45:35  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 04/16/2013 :  17:29:02  Show Profile  Reply with Quote
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 - 04/17/2013 :  11:45:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000