Tuesday, March 20, 2012

[URGENT] Problem to Restore Database from backup


Mrs,

I'm having a serious problem in SQL 2000 SP3 Database which is used Business Applications.
Today, I tryied to take backup in service time by the Enterprize Manager, the following
error occured and could not take backup.
--
Error 926 : Database '[database name]' cannot opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
--
After that, we tried same operation 3 times. But result is same.

After sometime, end-user report us that they can not log-in to database.

So we tried to dettach this database and attached it.
We success to dettach, but we can not attached it.

Also we tried to restore this database from Yesterday's backup file by using Enterprize Manager,
but the following error occured, and could not complete.
This backup file was generated daily backup task in Maintenance Plan in Enterprize Manager.
--
Microsoft SQl-DMO(ODBC SQLState:42000)
The media family on device "E:\ccd\......xxx.bak" is incorrectly formed.
SQL Server cannot process this media family.
RESTORE DATABASE is terminating abnormally.

--

We need to recover the database immediately not to stop the end-user business process.
Please help us.

Yamagury.

refer , http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1977204&SiteID=1 try as suggested and get back.... also you need to apply SP4 or go to sql 2005 as sql 2000 sp3 has ended its life cycle in july 10th 2007 itself....

refer

http://support.microsoft.com/gp/lifesupsps

|||Thank you for your support.

Yes, We already tried to apply SP4.
But this problem was not solved.

Now I'm trying to do as your instruction.|||(you had to back up the transaction log before you perform any restore operations and hadn't detach it before.

You had to verify your backup with RESTORE VERIFYONLY statement .)

use RESTORE HEADERONLY and see if you have multiple backup files in the the same backup file. If yes then
try using FILE= option while restoring.

File is from :

RESTORE DATABASE { database_name | @.database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @.file_number } ] .....


see Books Online

(Why do you have not SP4?

Read this.)

|||
Dear Deepak

I tried to create database and swap mdf/ldf file.
and go into single user mode, and then run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS,

but, DBCC command return error. Database could not be repaired.

How can I do after this?|||

Ok you can try as follows,

Alter database DBname set emergency

this will bring your db into emergency mode so that you can access your db and then subsequently export the datas alone from this db to another db so that you can minimize the dataloss.....if you have a full backup then drop this suspect database and restore it from full backup but there will be dataloss to minimize it set the db as emergency mode and export the tables,sps etc which you feel is required to another db in UAT server and script those and rerun the script in the restored db

No comments:

Post a Comment