locked
Restoring Database RRS feed

  • Question

  • I've been trying to restore database several times, and I even tried reinstalling the program as well. After hours, the restoring was still "executing". I've been thinking that my computer couldn't handle it to cause this to happen. I even turned off the firewall and tried to restore the database, but it was unable to complete the restoring. Any ideas on why this is happening? (I apologize in advance for my lack of knowledge.) Please contact me soon.

    Thank you, xLinhx.
    Tuesday, January 13, 2015 2:51 AM

Answers

  • you can get the currently executing queries by using the query in my blog.

    http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, January 13, 2015 4:06 AM
  • Restoration time depends upon the DB size.

    Please check why restore taking long by running the below T-SQL.

    select session_id, t.text, start_time, status, command, db_name(database_id) As DBname, blocking_session_id, wait_type, wait_time, wait_resource, percent_complete from sys.dm_exec_requests
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) as t

    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue

    _____________________________________

    Thanks,

    Shashikant

    Tuesday, January 13, 2015 3:47 AM
    1. Check if the restore is being blocked (sp_who2 active)
    2. Run the restore with stats =1 to see the progress every 1 percent
    3. Check the wait_type of the spid executing the restore and post it here
    4. See if the IO counter is increasing in sysprocesses, this is another important indicator that will tell whether the restore is hanging or progressing

    Satish Kartan www.sqlfood.com

    Tuesday, January 13, 2015 6:44 AM
  • How big is the file that you are restoring? Are you restoring it from a network drive or a local drive?

    You can track the restore operation using this query

    SELECT t.command,t.percent_complete FROM sys.dm_exec_requests t
    WHERE t.command LIKE 'Restore%'


    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, January 13, 2015 9:19 AM

All replies

  • Restoration time depends upon the DB size.

    Please check why restore taking long by running the below T-SQL.

    select session_id, t.text, start_time, status, command, db_name(database_id) As DBname, blocking_session_id, wait_type, wait_time, wait_resource, percent_complete from sys.dm_exec_requests
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) as t

    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue

    _____________________________________

    Thanks,

    Shashikant

    Tuesday, January 13, 2015 3:47 AM
  • you can get the currently executing queries by using the query in my blog.

    http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, January 13, 2015 4:06 AM
  • Hello ,

    Can you tell us the size of database ? What will be the last restore time ?


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Tuesday, January 13, 2015 4:37 AM
    1. Check if the restore is being blocked (sp_who2 active)
    2. Run the restore with stats =1 to see the progress every 1 percent
    3. Check the wait_type of the spid executing the restore and post it here
    4. See if the IO counter is increasing in sysprocesses, this is another important indicator that will tell whether the restore is hanging or progressing

    Satish Kartan www.sqlfood.com

    Tuesday, January 13, 2015 6:44 AM
  • How big is the file that you are restoring? Are you restoring it from a network drive or a local drive?

    You can track the restore operation using this query

    SELECT t.command,t.percent_complete FROM sys.dm_exec_requests t
    WHERE t.command LIKE 'Restore%'


    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, January 13, 2015 9:19 AM