Restoring issue without any error message

mliu

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

petey

Could you pls post the output from Management Studio/Query Analyzer when you restore a single file? Thanks.

mliu

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.

mliu

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.

mliu

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

petey

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.

mliu

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

mliu

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