| 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 |
 |
|
|
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?Thanksquote:
Originally posted by chadmat
You need to do with NO RECOVERY-Chad
|
|
|
|
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. |
|
|
|
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 |
|
|
|
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? |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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. |
|
|
|
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.
|
|
|
|
|
Restoring Transactional Log Backup