Crashed server. - SQL Server Forums

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

General SQL Server Forums

Data Corruption Issues

Crashed server.

Author

Topic

NWS
Starting Member

8 Posts

Posted - 2008-05-06 : 12:41:21

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

899 Posts

Posted - 2008-05-06 : 14:40:40

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

NWS
Starting Member

8 Posts

Posted - 2008-05-08 : 09:51:11

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

NWS
Starting Member

8 Posts

Posted - 2008-05-08 : 09:56:44

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

NWS
Starting Member

8 Posts

Posted - 2008-05-08 : 12:03:41

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

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-08 : 15:58:38

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-08 : 16:04:37

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

NWS
Starting Member

8 Posts

Posted - 2008-05-08 : 16:31:53

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-08 : 16:40:11

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

NWS
Starting Member

8 Posts

Posted - 2008-05-08 : 17:47:40

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

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-05-08 : 18:02:07

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.

NWS
Starting Member

8 Posts

Posted - 2008-05-08 : 18:50:42

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

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-05-08 : 19:46:14

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

NWS
Starting Member

8 Posts

Posted - 2008-05-09 : 03:29:03

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources