Error while Restoring transactional backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error while Restoring transactional backup

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-01-12 : 12:02:17

Hi Experts,

I tried to restore my database from Enterprise manager. I checked Complete backup and then all associated transactional backup but its throwing me the below error.

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Cannot find file ID 145 on device 'D:\SQLBackup\Tran_App.BAK'.
RESTORE LOG is terminating abnormally.
---------------------------
OK
---------------------------

Path D:\SQLBackup\ and the file Tran_App.BAK both exists.

Thanks in advance
SS

Don't sit back because of failure. It will come back to check if you still available. -- Binu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-12 : 12:52:56

Does D:\SQLBackup\Tran_App.BAK exist on the actual SQL Server or on your cilent machine? If it's just on the client machine, then that's the problem. The backup and restore commands run from the server not the client.

To verify though, let's run this in Query Analyzer:

EXEC master.dbo.xp_cmdshell 'dir D:\SQLBackup\'

Post the results here of that query.

Tara Kizer

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-01-12 : 13:53:50

Hi Tara,

Thanks for the reply.

Yes I am on server. Below is the result of the query you asked for.

Volume in drive D is Local Disk
Volume Serial Number is 1CD0-DCBE
NULL
Directory of D:\SQLBackup
NULL
01/12/2007 11:59 PM <DIR> .
01/12/2007 11:59 PM <DIR> ..
01/12/2007 11:00 PM 893,608,448 Full_AI_CUSWS.BAK
01/12/2007 10:00 PM 16,283,136 Full_Master.BAK
01/12/2007 10:00 PM 35,354,112 Full_MSDB.BAK
01/12/2007 10:35 PM 30,538 IntigrityCheckAI_CUSWS.txt
01/11/2007 10:23 AM <DIR> PerfLog
01/12/2007 02:00 AM 168,118 SpaceCheck_AI.txt
01/13/2007 12:10 AM 473,769,472 Tran_App.BAK
09/12/2006 10:25 AM 696 TSPExpireUATdb.txt
7 File(s) 1,419,214,520 bytes
3 Dir(s) 114,211,622,912 bytes free
NULL

Thanks
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-12 : 14:23:28

I would try the restore via Query Analyzer as probably you are selecting an incorrect option in Enterprise Manager.

Check out RESTORE DATABASE in SQL Server Books Online for details. But here's an example to get you started:

RESTORE DATABASE YourDbName
FROM DISK = 'D:\SQLBackup\Tran_App.BAK'
WITH REPLACE

Tara Kizer

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-01-12 : 14:47:26

Thanks Tara,

But in my case the Transactional backup job is running under append mode. In Enterprise manager, I can check and uncheck the transactional backup I want to apply on my database.

How can I do that from Query Analyser?

Thanks
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-12 : 14:56:37

To apply transaction logs, you use RESTORE LOG, which has practically the same syntax as RESTORE DATABASE.

Tara Kizer

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-01-13 : 01:43:14

Before running RESTOER LOG...Run RESTORE HEADERONLY FROM DISK = 'D:\SQLBackup\Tran_App.BAK' to see the contents of backup file.

MohammedU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources