Restoring issue without any error message

mliumliu Posts: 26
edited April 11, 2007 12:15PM in SQL Backup Previous Versions
Hi,

We are having a very werid issue where the restore stopped, even though the process keeps in running mode.

It doesn't restore any backups from the primary server. and all backup files are accumulating on the server.

Since it doesn't generate any log, it is not possible for us to find out why. We have since restarted the secondary server, and nothing seems to help.

Could anyone shed some lights on this?

Thanks

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls post the output from Management Studio/Query Analyzer when you restore a single file? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Here is the result:

    (3 row(s) affected)


    (3 row(s) affected)

    Server: Msg 50000, Level 16, State 1, Line 6
    SQL Backup job failed with exitcode: 220 SQL error code: 0


    At the same time, we could see all of the log files from the primary server.
  • We have tried it again. Currently, the behavior is that it has kept running for 15 minutes, and still running. The size of the log file that needs to be restored is only 275KB.

    What could be the reason for this process keeps running?

    Thanks.
  • One more piece of information is as follow:

    We have a test script that is exactly the same is the one that's not working. The only thing that's different is that the database is a test database. The job has been disabled since it was a test. We just ran that script from backup to restore. Everything worked just fine.

    We are totoally puzzled as to why the same script will not work for the production database.

    Any help and suggestion will be greatly appreciated.

    Thanks
  • peteypetey Posts: 2,358 New member
    Could you pls try restoring just a single trx log file manually, using Management Studio/Query Analyzer, instead of using the SQL Agent job?

    Pls post the command you use, and the resulting output. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Petey,

    We have tried that several times, but the process just kept running, and never stopped until we stopped it manually. There wasn't any error message, or any other messages either.

    What could we do to see what is really going on? If we run Profiler, what should we be looking at?

    Thanks
  • Hi Petey,

    Below is the command that is used to run through Query Analyzer:

    DECLARE @exitcode int
    DECLARE @sqlerrorcode int
    exec master..sqlbackup N'-sql "RESTORE LOG [dbname]
    FROM DISK = ''\\primaryservername\SQLShare\LOG_(local)_dbname_20070404_121501.sqb''
    WITH NORECOVERY, MAILTO_ONERROR = ''mei@calyxsoftware.com'',
    ERASEFILES = 7, FILEOPTIONS = 1, PASSWORD = ''xxxxxxxxxxxx'',
    MOVETO = ''D:\SQLBackup\SQLProcessed\''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
    IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
    END


    This particular log file that we run through Query Analyzer is only 24KB. We have started the processed 10 minutes ago, and it is still running....

    Any ideas?

    Thanks
Sign In or Register to comment.