July 31, 2012 at 1:35 pm
I am trying to restore a huge database in the lower environment from prod backup,
When I execute the below query It indicates the restore is complete
SELECT percent_complete,* FROM sys.dm_exec_requests WHERE session_id = 57
percent_complete =100
But the Database is still in "Restoring" - State, When started the restore command I did execute the restore command "with recovery" and when i run the below
sp_who2 active
I can see one active restore spid, I belive the database is in undo \ redo phase.
My first question is
How do i confirm if its in undo \ redo phase.
My second question is ,
Can i Recover this database manually by issuing even though its in "Restoring" State
RESTORE DATABASE dbname WITH RECOVERY
As I am not concerned about open \ uncommited transactions, If I do that will the database will result in any corruption or in unusuable state.
Any advice is appreciated.
-Shan
July 31, 2012 at 1:49 pm
What was the exact restore statement that you ran?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2012 at 2:11 pm
Below is the statement...
RESTORE DATABASE DBNAME
FROM DISK = 'L:\SQL_Restore\dbbak.BAK'
WITH
STATS = 10,-- Show progress (every 10%)
MOVE 'DBNAME' TO 'P:\Data01\db_data_file.mdf',
MOVE 'DBNAME_log' TO 'P:\Logs01\db_data_file.ldf'
July 31, 2012 at 2:13 pm
You'll just have to wait. Most likely the log is severely fragmented (lots of VLFs) and the recovery portion of the restore takes forever as a result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2012 at 2:23 pm
Thanks for your response, Is there anything that I could have done in the restore command to have the Database available faster, I mean not to move forward with undo \ redo state or there is no such thing, We just have to wait.
-Shan
July 31, 2012 at 2:27 pm
If you have the option you should plan for a piece meal restore.
July 31, 2012 at 2:39 pm
Jayanth_Kurup (7/31/2012)
If you have the option you should plan for a piece meal restore.
How is that going to save on recovery time? Or total time to restore the entire database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2012 at 7:29 am
if there are readonly filegroups that do not have a critical role in bringint eh server up thenhe can restore the read write fielgroups to bring the DB online. sy for example the read only file group contains 500 GB worth of archive data, or contains archice tables that are not needed for functionaloty
August 10, 2012 at 5:22 am
That's a big if, since no such filegroups were mentioned. Also, if bringing the DB to a lower environment (dev/test), those read only filegroups would be needed, though the tables could probably be empty.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply