December 20, 2016 at 2:52 am
I have 12.5 GB database backup file.i want to restore it on SQL Server 2012 ,when i m restore database then it shows Executing(100%) I wait 2 hours but still database not restore continue its showing Executing(100%)…
plz help me how to restore 12 gb database in sqlserver 2008 2 or 2012 .......
December 20, 2016 at 3:02 am
What command are you using to do the restore, or are you using the GUI? If you're using the GUI, please use the Script button on the Restore Database window to generate the script.
John
December 20, 2016 at 6:23 am
shashianireddy (12/20/2016)
I have 12.5 GB database backup file.i want to restore it on SQL Server 2012 ,when i m restore database then it shows Executing(100%) I wait 2 hours but still database not restore continue its showing Executing(100%)…plz help me how to restore 12 gb database in sqlserver 2008 2 or 2012 .......
I think it it possible that you have to wait such a long time because uncommitted transactions need to be rolled back (the recovery) to keep the database consistent.
If you can't wait so long, you could try my method (well, not just my method...):
1. Set the database in single-user mode
2. Set the database in simple recovery mode
3. Shrink the database and Transaction log to avoid restoring White space
4. Detach the database and copy it to the target Server
5. Hang the database on to the instance
6. Set the database in full recovery mode
7. Rebuild all Indexes
8. Ensure the database is in Multi user mode
There is more to do than simply restoring a backup but I have found it to be the best solution for me.
December 20, 2016 at 9:07 am
FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008 or in SQL 2012
December 20, 2016 at 9:40 am
Sreejith! (12/20/2016)
https://support.microsoft.com/en-us/kb/2653893%5B/quote%5D
The restore is the easy part.....it is the recovery that takes a long time if the Transaction log is large
December 20, 2016 at 11:01 pm
If you have the latest patches,then problem is due to high VLF count.
You can find out the VLF's count using below command
dbcc loginfo('databasename');
December 21, 2016 at 1:04 pm
How large is the transaction log file? When the backup runs is there some very long running update, delete, insert process running that hasn't committed yet?
Next time you run the restore script it out and run it manually in Mgt Studio.
Also, you can run this command and see what it says as the restore is running. My guess is it is allocating a very large transaction log file with a lot of data changes in it.
Select r.command
, s.text
, r.start_time
, r.percent_complete
, cast(((datediff(second, r.start_time, getdate())) / 3600) As varchar) + ' hour(s), '
+ cast((datediff(second, r.start_time, getdate()) % 3600) / 60 As varchar) + 'min, '
+ cast((datediff(second, r.start_time, getdate()) % 60) As varchar) + ' sec' As running_time
, cast((r.estimated_completion_time / 3600000) As varchar) + ' hour(s), '
+ cast((r.estimated_completion_time % 3600000) / 60000 As varchar) + 'min, '
+ cast((r.estimated_completion_time % 60000) / 1000 As varchar) + ' sec' As est_time_to_go
, dateadd(second, r.estimated_completion_time / 1000, getdate()) As est_completion_time
From sys.dm_exec_requests r
Cross Apply sys.dm_exec_sql_text(r.sql_handle) s
Where r.command Like 'DBCC%'
Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');
December 22, 2016 at 1:35 am
Markus (12/21/2016)
How large is the transaction log file? When the backup runs is there some very long running update, delete, insert process running that hasn't committed yet?Next time you run the restore script it out and run it manually in Mgt Studio.
Also, you can run this command and see what it says as the restore is running. My guess is it is allocating a very large transaction log file with a lot of data changes in it.
Select r.command
, s.text
, r.start_time
, r.percent_complete
, cast(((datediff(second, r.start_time, getdate())) / 3600) As varchar) + ' hour(s), '
+ cast((datediff(second, r.start_time, getdate()) % 3600) / 60 As varchar) + 'min, '
+ cast((datediff(second, r.start_time, getdate()) % 60) As varchar) + ' sec' As running_time
, cast((r.estimated_completion_time / 3600000) As varchar) + ' hour(s), '
+ cast((r.estimated_completion_time % 3600000) / 60000 As varchar) + 'min, '
+ cast((r.estimated_completion_time % 60000) / 1000 As varchar) + ' sec' As est_time_to_go
, dateadd(second, r.estimated_completion_time / 1000, getdate()) As est_completion_time
From sys.dm_exec_requests r
Cross Apply sys.dm_exec_sql_text(r.sql_handle) s
Where r.command Like 'DBCC%'
Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');
Here the problem isn't whether or not the transaction log file is large rather how many uncommitted transactions need to be rolled back.
After all, it is possible to have a file that is 50GB big but only 1% used. That is why I suggested the detach /attach earlier in the post.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply