May 17, 2016 at 5:59 pm
Hello friends, i really hope you may help me.
I've trying to restore a db since 2 days ago and it simply can't be restored.
I've tried from the wizard (it gets 100% completed but the process was not released, it got stucked) a couple times with no succesefull results.
Now i'm tryin from the query editor with the "RESTORE DATABASE dbname from DISK..." but doesn't look i'll get different results, the query has been runing by a half hour and i still don't get any progress on the messages tab, i should say i'm doing this from my computer (16GB RAM, intel i7 3.6 GHz) with a .bk witch size is 18GB.
May 17, 2016 at 8:33 pm
monterorf (5/17/2016)
Hello friends, i really hope you may help me.I've trying to restore a db since 2 days ago and it simply can't be restored.
I've tried from the wizard (it gets 100% completed but the process was not released, it got stucked) a couple times with no succesefull results.
Now i'm tryin from the query editor with the "RESTORE DATABASE dbname from DISK..." but doesn't look i'll get different results, the query has been runing by a half hour and i still don't get any progress on the messages tab, i should say i'm doing this from my computer (16GB RAM, intel i7 3.6 GHz) with a .bk witch size is 18GB.
Have a quick read of this site https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/
Note About Restores
I would like to touch base upon one more aspect of SQL Server which you will encounter while restoring databases. You may see that the restore is stuck at 100% or around 99.99% and is not moving further. Sometimes for databases that are very large, TB size databases, it may even take 5 hours for the recovery to complete. To understand this situation we need to understand the different phases that a restore goes through.
The three phases are Data Copy phase, Redo phase and Undo phase. While you may see the restore is 100% complete it's actually only the Data Copy phase that is complete and then SQL proceeds to subsequent phases before the recovery is totally complete.
In the Redo phase, all the committed transactions present in the transaction log when the database was being backed up are rolled forward.
In the Undo phase, all the uncommitted transactions in the transaction log while the database was being backed up are rolled back.
If the database is being restored with NORECOVERY, the Undo phase is skipped. Unfortunately SQL Server does not show the progress during the Redo and Undo phases as it does in the Data Copy phase. So, depending upon the activity in the database at the time it was getting backed up will decide the overall total recovery time.
May 18, 2016 at 1:30 am
monterorf (5/17/2016)
Hello friends, i really hope you may help me.I've trying to restore a db since 2 days ago and it simply can't be restored.
I've tried from the wizard (it gets 100% completed but the process was not released, it got stucked) a couple times with no succesefull results.
Now i'm tryin from the query editor with the "RESTORE DATABASE dbname from DISK..." but doesn't look i'll get different results, the query has been runing by a half hour and i still don't get any progress on the messages tab, i should say i'm doing this from my computer (16GB RAM, intel i7 3.6 GHz) with a .bk witch size is 18GB.
Hope you have enough disk space to restore the DB. Are you restore over an existing DB or creating a new one?
Are you getting any error?? If not then wait.
You can check the percentage complete by using select percent_complete,* from sys.dm_exec_requests where command like '%Restore%'
May 18, 2016 at 9:21 am
I really appreciate your answer, i decided to rollback the restore and keep the old backup i was working with, but i'll try to restore again tonight and let the process finish.
i was a little confused about the size of my DB, i know my backup file is 18GB, but i'm afraid the size of the DB is much bigger than that, the sum of mdf and ldf files is around 300 GB, so i'm thinking is totallty normal that the process were slow.
However i'm gonna try it again tonight, thanks a lot again.
May 18, 2016 at 9:33 am
Use [font="Courier New"]RESTORE HEADERONLY FROM DISK = '...'[/font] to get size information for the backup(s) in your file (a file can contain more than one backup). Use [font="Courier New"]RESTORE FILELISTONLY FROM DISK = '...'[/font] for information on the files that will comprise the restored database (including the space they will take up on disk).
John
May 18, 2016 at 8:10 pm
monterorf (5/18/2016)
I really appreciate your answer, i decided to rollback the restore and keep the old backup i was working with, but i'll try to restore again tonight and let the process finish.i was a little confused about the size of my DB, i know my backup file is 18GB, but i'm afraid the size of the DB is much bigger than that, the sum of mdf and ldf files is around 300 GB, so i'm thinking is totallty normal that the process were slow.
However i'm gonna try it again tonight, thanks a lot again.
It's possible that the MDF file was previously huge and someone deleted a lot. It's more likely that someone had a database and never did log file backups and the slated size for the log is huge. Because it has to format the log file with VLFs, that could take quite a bit of time. Let it run over night. Checking the progress may not actually help if it's a huge log file that must be built.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 19, 2016 at 1:03 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply