Post reply

Restore database never completes

  • February 8, 2005 at 4:53 pm

    #185063

    Hi Friends,

    I have faced a wiered problem with my SQL server restore operations. Problem is when i run the following from the QA it never finishes (run status is still RED). This doesn't happen with all the database restores on this server, itried with some smaller databases (upto 50 GB) but this error didn't happen. This databse is about 150 GB in size once it's restored (Took about 3-4 hours). Restore status goes up to the line before the blue line. It doesn't go to the blue line to report as restore completed. Funny thing is when i stop the execution and check the databse status, there is nothing wrong, the database is actually restored successfully.

    98 percent restored.

    99 percent restored.

    100 percent restored.

    Processed xxx pages for database 'MyDatabase', file 'MyDatabase' on file 1.

    Processed xxx pages for database 'MyDatabase', file 'MyDatabase_Data1' on file 1.

    Processed xxx pages for database 'MyDatabase', file 'MyDatabase_Data2' on file 1.

    Processed xxx pages for database 'MyDatabase', file 'MyDatabase_Data3' on file 1.

    Processed xxx pages for database 'MyDatabase', file 'MyDatabase_Data4' on file 1.

    Processed xxx pages for database 'MyDatabase', file 'MyDatabase_Data5' on file 1.

    Processed xxx pages for database 'MyDatabase', file 'MyDatabase_Log' on file 1.

    STOPS HERE

    RESTORE DATABASE successfully processed xxxx pages in 0.201 seconds (25.110 MB/sec).

    This is the restore statment

    restore database MyDatabase 

    from disk = '\\mybackupserver\SQLBackups\MyDatabase\MyDatabase_db_200502062300.BAK'  with move 'MyDatabase' to 'F:\SQLData\MyDatabase_1__Data.MDF'

    ,move 'MyDatabase_Data1' to 'F:\SQLData\MyDatabase_2__Data.MDF'

    ,move 'MyDatabase_Data2' to 'F:\SQLData\MyDatabase_3__Data.MDF'

    ,move 'MyDatabase_Data3' to 'F:\SQLData\MyDatabase_4__Data.MDF'

    ,move 'MyDatabase_Data4' to 'F:\SQLData\MyDatabase_5__Data.MDF'

    ,move 'MyDatabase_Data5' to 'F:\SQLData\MyDatabase_6__Data.MDF'

    ,move 'MyDatabase_Log' to 'I:\SQLLog\MyDatabase_7__Log.LDF'

    , stats = 1

    Has anybody come across simlar problem? Any idea what's going on?

    Thanks in Advance...

  • February 9, 2005 at 12:16 am

    #541304

    Hello Bimal,

    May be the Restore process is doing lot of Recovery (Rollback/Roll Forward)of transactions. Keep in mind that the recovery process happens only in the end.

    How much time did you give before killing the restore process ?

    Gopi

  • February 9, 2005 at 1:08 am

    #541306

    What is your recovery method




    My Blog: http://dineshasanka.spaces.live.com/

  • February 9, 2005 at 9:50 am

    #541386

    Hi,

    that behavior might occur if your msdb is full, so the actual restore is successfull, but the history of it can not be written to msdb.

    are there any error messages in the error log?

    regards

    karl

    Best regards
    karl

  • February 9, 2005 at 3:48 pm

    #541481

    Thanks for all your comments everybody.

    I checked the SQL Log and i found that the Restore has been completed at the correct time as we expected but QA doesn't report it.

    It can't be a msdb , or recovery problem becasue the restore has been actually completed from the SQL side. Seems like some final state messages from SQL server did not reach QA so that it can report the completion of the restore. This has happened to me several times randomly now, all the times it happened when i was connecting to the server with a Terminal Client and kick off the restore on the server. Never happened when i was doing it on QA (connected to the remote server) from my machine. And the other thing is each time restore progress reporting on QA stops at the same point which is one line before the last line.

    Hope this helps to crack the probelm.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply