SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Crashed server.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NWS
Starting Member

8 Posts

Posted - 05/06/2008 :  12:41:21  Show Profile  Reply with Quote
Hi, i have a corrupt installation of SQL 7, so i am trying to attach the db's from that server to another, only it is not working. Is this soemthing that should be able to be done?

error message i get is:

Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1159b2c0, page ptr = 0x205b6000, pageid = (0x1:0x9), dbid = 6, status = 0x801, file = C:\MSSQL7\Data\casenotes_Data.mdf.

Connection Broken


any help would be greatly appreciated.

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 05/06/2008 :  14:40:40  Show Profile  Visit paulrandal's Homepage  Reply with Quote
The boot page of the database is corrupt (page 1:9). Unless you have a backup of the database, that database is lost - the database cannot be touched by SQL Server in any way if the boot page is damaged.

Do you have backups?

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com
Go to Top of Page

NWS
Starting Member

8 Posts

Posted - 05/08/2008 :  09:51:11  Show Profile  Reply with Quote
quote:
Originally posted by paulrandal

The boot page of the database is corrupt (page 1:9). Unless you have a backup of the database, that database is lost - the database cannot be touched by SQL Server in any way if the boot page is damaged.

Do you have backups?

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

Go to Top of Page

NWS
Starting Member

8 Posts

Posted - 05/08/2008 :  09:56:44  Show Profile  Reply with Quote
Hi, i do have backups but am having much trouble getting them to either attach to another server or restoring them. if i try to restore do i need to name a device, the original installation is several years ago, done by someone else and there is NO documentation for any of it.

i have tried to rebuild the master db but get this message:

Cannot copy northwnd.ldfy 6 Model 7 Stepping 3, Genuine Intel: Cannot find the specified file.
Make sure you specify the correct path and filename


then it throws a Dr Watson, (Server is NT4)

i have tried moving files between servers but cannot get the restore to work.

Any suggestions?

Thanks
Go to Top of Page

NWS
Starting Member

8 Posts

Posted - 05/08/2008 :  12:03:41  Show Profile  Reply with Quote
I have managed to restore one of the db's and when trying to attach now get this error message

Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x115a2bc0, page ptr = 0x2097e000, pageid = (0x1:0x9), dbid = 6, status = 0x801, file = c:\mssql7\restores\nurseworkstation.mdf.

Connection Broken


running the restore filelistonly stored procedure i get the following, so i am confident i have got the names correct.

LogicalName PhysicalName Type FileGroupName Size MaxSize
--------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ --------------------------------------------------------------------------------------------------------------------------------- ---------------------- ----------------------
nurseworkstation_devData d:\sqldata\nurseworkstation_dev.mdf D PRIMARY 1148387328 35184372080640
nurseworkstation_log d:\sqldata\nurseworkstation_log.ldf L NULL 268435456 35184372080640

(2 row(s) affected)


again any help would be appreciated.

Adam
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 05/08/2008 :  15:58:38  Show Profile  Reply with Quote
Not the expert on this but it looks like you backed up a database that was already corrupt. It's the same error you stated in your 1st post. Do you have another, older backup (prior to corruption)??

Terry
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 05/08/2008 :  16:04:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
NWS, in your last post you say "I have managed to restore one of the db's and when trying to attach now get this error message", I'm confused as to why you are trying to attach it when you've already restored it. Please explain.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

NWS
Starting Member

8 Posts

Posted - 05/08/2008 :  16:31:53  Show Profile  Reply with Quote
Terry- yep, this is what happens when companies have database installations and no DB admins!

which leads to the next point mentioned by Tara.

as i am not a db admin and what i have done so far I have learnt this week, i thought i would still need to attach the db after the restore as the db is from a different server, hence i believe is not in the master db of the server to which it is restored.

this started as a challenge to save a departments years of data, the expectation being that it would not be successful - it seems it is heading that way, but until i know for sure i shall persevere.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 05/08/2008 :  16:40:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
The restore takes care of putting the relevant data in the master database. Once the database has been restored, you can not do anything with those database files (MDF, LDF, ...) including trying to attach them. They are already "attached".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

NWS
Starting Member

8 Posts

Posted - 05/08/2008 :  17:47:40  Show Profile  Reply with Quote
Hi Tara, the mdf and ldf files are in the mssql\data folder but do not show up in the enterprise manager nor can i see them through exec sp_helpdb.

That's why I thought it would need attaching.

assuming that the files are in the said folder - am i ever likely to be able to see them; should i now give up?

adam
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 05/08/2008 :  18:02:07  Show Profile  Reply with Quote
Restoring a database is taking the backup file (not an mdf or ldf file, the .bak or other extension file that is the actuall backup of the db) and restoring it with the RESTORE command.

RESTORE DATABASE db_name FROM DISK = 'file location and name of backup file' WITH RECOVERY

Once this is done, there is no need to attach any files. You should see the db in Enterprise Manager. You may have to refresh or close Enterprise Manager and reopen it to get it to show up. Did you actually do a RESTORE using a backup file or simply copy the mdf and ldf files over? Copying the mdf and ldf files over is not a restore. If they won't attach (due to corruption), then you can't use them. Do you have an actual backup file? A backup of the database. You should have backups running regularly with the BACKUP command.

BACKUP DATABASE TO 'file location and file name'

So tell us if you actually have a backup file. Mdf and ldf files are NOT backup files.
Go to Top of Page

NWS
Starting Member

8 Posts

Posted - 05/08/2008 :  18:50:42  Show Profile  Reply with Quote
this is what i have run:
use master
restore database nurseworkstation_devdata from disk = 'C:\mssql7\backup\NurseWorkstation_db_200804140100.BAK' with recovery,
move 'nurseworkstation_devdata' to 'c:\mssql7\data\nurseworkstation_devdata.mdf',
move 'nurseworkstation_log' to 'c:\mssql7\data\nurseworkstation_log.ldf'


this is the error message:

Server: Msg 3270, Level 16, State 1, Line 2
An internal consistency error occurred. Contact Technical Support for assistance.
Server: Msg 3013, Level 16, State 1, Line 2
Backup or restore operation terminating abnormally.


as can be seen, I am using the backup. The ldf and mdf files are then appearing in the mssql\data folder yet they can not be seen on enterprise manager. The log file is recording the mdf and ldf files are being created and opened and closed.

I guess this then is the end of the line?

Adam
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 05/08/2008 :  19:46:14  Show Profile  Visit paulrandal's Homepage  Reply with Quote
This error occurs when restore reads a page from the backup and the page header contains an invalid page ID. Basically, you are dead in the water given all that you've said so far.

You may be able to get some of the data back by restoring the backup on SS2005 using the WITH CONTINUE_AFTER_ERROR option, but I suspect the damaged pages are critical to the database and so not even this will work.

The files are created because the first phase of restore is always to create the files. If the subsequent restore actions fail then the files are not deleted.

Thanks

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com
Go to Top of Page

NWS
Starting Member

8 Posts

Posted - 05/09/2008 :  03:29:03  Show Profile  Reply with Quote
Thanks Paul and to everyones else who offered advice and suggestions.

Certainly a good case for the company to employ a dba.

Take care all.

Adam
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000