Error 954: Database has invalid schema - SQL2000

I'm restoring a database on another server, the restore from disk goes
through successfully, until I try to click on tables or views I get the
message below:
Error 954: Database 'dbname' has invalid schema
Thanks in advance for any help.ITDUDE27,
Are you using SharePoint and Small Business Server? If so, here are a
couple of links:
http://blogs.technet.com/edwalt/archive/2006/03/06/421346.aspx
http://msmvps.com/blogs/bradley/archive/2005/09/13/66344.aspx
I do have one weird thought: it sounds as if the file location for the files
in the database does not match the actual file locations of the mdf and/or
ldf. Did you use the MOVE option during the RESTORE in order to position
the files properly?
Other than that, I have no really useful suggestions.
RLF
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:DB40C301-EF5E-4664-819C-B251D4F8BE24@.microsoft.com...
> I'm restoring a database on another server, the restore from disk goes
> through successfully, until I try to click on tables or views I get the
> message below:
> Error 954: Database 'dbname' has invalid schema
> Thanks in advance for any help.
>|||Thanks Russ.
I'am running SP on SBS 2003. my Live sharepoint site is running fine and I
was able to install SP 2.0 on another server and it came up fine. I trying to
restore a copy of the live db as test and that is what is causing the error.
I had seen the second link you refered too, it was not help full. The point
of this exercise is to restore the sp database using Enterprize manager and
query it and then map it as my SP content database.
I've tried the move command using osql with no success. I think you are
absoluty correct about the Move, I'm just not having any luck, which ever way
I restore the SP db I get the same message.
Thanks in advance for any other feedback.
"Russell Fields" wrote:
> ITDUDE27,
> Are you using SharePoint and Small Business Server? If so, here are a
> couple of links:
> http://blogs.technet.com/edwalt/archive/2006/03/06/421346.aspx
> http://msmvps.com/blogs/bradley/archive/2005/09/13/66344.aspx
> I do have one weird thought: it sounds as if the file location for the files
> in the database does not match the actual file locations of the mdf and/or
> ldf. Did you use the MOVE option during the RESTORE in order to position
> the files properly?
> Other than that, I have no really useful suggestions.
> RLF
> "ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
> news:DB40C301-EF5E-4664-819C-B251D4F8BE24@.microsoft.com...
> > I'm restoring a database on another server, the restore from disk goes
> > through successfully, until I try to click on tables or views I get the
> > message below:
> >
> > Error 954: Database 'dbname' has invalid schema
> >
> > Thanks in advance for any help.
> >
> >
>
>|||Russell,
Can you comfirm the syntax for me.
Current location: N:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\avalanche.bak from other server
New location: N:\Program Files\Microsoft SQL Server\MSSQL\Data
RESTORE DATABASE avalanche_new FROM DISK = 'N:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\avalanche.bak'
WITH
MOVE 'northwind' TO 'N:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\avalanche_new.mdf'
MOVE 'northwind_log' TO 'N:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\avalanche_log.ldf'
Thanks in advance.
"Russell Fields" wrote:
> ITDUDE27,
> Are you using SharePoint and Small Business Server? If so, here are a
> couple of links:
> http://blogs.technet.com/edwalt/archive/2006/03/06/421346.aspx
> http://msmvps.com/blogs/bradley/archive/2005/09/13/66344.aspx
> I do have one weird thought: it sounds as if the file location for the files
> in the database does not match the actual file locations of the mdf and/or
> ldf. Did you use the MOVE option during the RESTORE in order to position
> the files properly?
> Other than that, I have no really useful suggestions.
> RLF
> "ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
> news:DB40C301-EF5E-4664-819C-B251D4F8BE24@.microsoft.com...
> > I'm restoring a database on another server, the restore from disk goes
> > through successfully, until I try to click on tables or views I get the
> > message below:
> >
> > Error 954: Database 'dbname' has invalid schema
> >
> > Thanks in advance for any help.
> >
> >
>
>|||ITDUDE27,
Syntax looks correct, but the code does not match your spec. Compare:
N:\Program Files\Microsoft SQL Server\MSSQL\Data
N:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\avalanche_new.mdf
If N:\ is a network drive that is not a supported configuration for SQL
Server databases. You will need to place the database files on locally
attached storage or on your SAN.
And, of course, I assume that the 'northwind%' logical names are correct.
I also usually specify a couple of things that should not be necessary in my
RESTORE command. Habit, you know, but I include the options:
RECOVERY, REPLACE
FWIW,
RLF
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:F7C1965F-448C-44F1-8DEF-9860E15E1A86@.microsoft.com...
> Russell,
> Can you comfirm the syntax for me.
> Current location: N:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\avalanche.bak from other server
> New location: N:\Program Files\Microsoft SQL Server\MSSQL\Data
>
> RESTORE DATABASE avalanche_new FROM DISK = 'N:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\avalanche.bak'
> WITH
> MOVE 'northwind' TO 'N:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\avalanche_new.mdf'
> MOVE 'northwind_log' TO 'N:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\avalanche_log.ldf'
> Thanks in advance.
>
> "Russell Fields" wrote:
>> ITDUDE27,
>> Are you using SharePoint and Small Business Server? If so, here are a
>> couple of links:
>> http://blogs.technet.com/edwalt/archive/2006/03/06/421346.aspx
>> http://msmvps.com/blogs/bradley/archive/2005/09/13/66344.aspx
>> I do have one weird thought: it sounds as if the file location for the
>> files
>> in the database does not match the actual file locations of the mdf
>> and/or
>> ldf. Did you use the MOVE option during the RESTORE in order to position
>> the files properly?
>> Other than that, I have no really useful suggestions.
>> RLF
>> "ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
>> news:DB40C301-EF5E-4664-819C-B251D4F8BE24@.microsoft.com...
>> > I'm restoring a database on another server, the restore from disk goes
>> > through successfully, until I try to click on tables or views I get the
>> > message below:
>> >
>> > Error 954: Database 'dbname' has invalid schema
>> >
>> > Thanks in advance for any help.
>> >
>> >
>>

Error 954: Database 'dbname' has invalid schema

I'm restoring a database on another server, the restore from disk goes
through successfully, until I try to click on tables or views I get the
message below:
Error 954: Database 'dbname' has invalid schema
any thoughts?Hi
Can you provide us with more info? What is the version are you using? Do you
try restore from SS2000 to SS2005?
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:35AE8C40-3659-44DB-BABB-8B65D1223181@.microsoft.com...
> I'm restoring a database on another server, the restore from disk goes
> through successfully, until I try to click on tables or views I get the
> message below:
> Error 954: Database 'dbname' has invalid schema
> any thoughts?|||Thanks for the response uri.
I'm doing the restore on SS2000 to SS2000. I'm trying to test the install of
WSS3.0 on a test environment but the restore is displaying that error.
Thanks in advance.
"Uri Dimant" wrote:
> Hi
> Can you provide us with more info? What is the version are you using? Do you
> try restore from SS2000 to SS2005?
> "ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
> news:35AE8C40-3659-44DB-BABB-8B65D1223181@.microsoft.com...
> >
> > I'm restoring a database on another server, the restore from disk goes
> > through successfully, until I try to click on tables or views I get the
> > message below:
> >
> > Error 954: Database 'dbname' has invalid schema
> >
> > any thoughts?
>
>

Error 9003 on SQL Server 2000

Hi guys,

I am having a little trouble with a SQL Server 2000 database and I would like some help. After failure and recovery of a hard disk my database is marked as suspect and I cannot get it to work again. I have tryed a few things such as copying the database and the log files to another computer and then running the attach command which returns the following "Error 9003: The LSN(12:32:1) passed to log scan in database 'dbName' is invalid.".

Upon getting this error I have tryed with failure attaching a single file, I have also tryed to create a new database with the same name in the same location as the original, then replacing the new files with my old ones, restarting the SQL Server and getting the database into Emergency Mode hopping to extract the data through DTS or BCP Utility, but I cannot access the objects nor the data.

I have also tryed to create a new database with the same name and replace only the ".mdf" file with my old one, but failed.

I would be gratefull for any help.

Manuel.pereira@.ricmade.ptit looks quite nasty, I wonder if it's possible to survive severe drive failure without performing full restore of db. But couple of thoughts: How many log files were originally in your db?, all other databases (system) are ok after drive recovery? By setting your db into emergency mode, you mean setting status column master..sysdatabases to -32768? Didn't work? After drive recovery, what were error messages in SQL Server log after startup concerning db in question? mojza|||Hi Mojza,

I only have one log file on my database, unfortunately I have another 2 databases with the same error. When I say that I set the database into emergency mode I mean setting the status column of master.sysdatabases to -32768, but this did not work, so I suspect that the .mdf file is damaged.

After I started up SQL I did get the following error messages.

Starting up database 'rjp'.
Error: 9003, Severity: 20, State: 6
The LSN (18611:16:1) passed to log scan in database 'rjp' is invalid.|||According to MS documentation, 9003 error should mean that your mdf and ldf files are out of syn. And the solution is usually attaching single file using sp_attach_db or sp_attach_single_file_db Sps to let SQL Server create a new log file for you. But you've said it didn't work. If it's not possible to put db into emergency mode, I guess there's corruption somewhere in data file header (just my opinion) and you have only two options left: go back to your last good backups or try to contact MS Support with uncertain results.
mojza

just for the sake of completness if you run dbcc dbrecover ('your_db')
command, the result is probably the same as during server startup?|||I get the same error message when I run the command dbcc dbrecover. Also when I try to attach a single file mdf, I get the error message 1813: Could not open new database 'db_name'. Create Database is Aborted. Device activation error, the physical file name 'log path' may be incorrect.

In general you are able to execute this comand and a log file will be created for you, but not with this database.|||The net is that one should not rely on SQL Server providing a silver bullet to recover from hard disk failures. It is a risky proposition.

Instead, plan for the failures by implementing RAID, Backups, Clusters, Log Shipping, or any other tools you are provided.

Expect and be prepared for failures. Understand your options, propose at least three options to the business unit, and let them decide. DO NOT MAKE THAT CHOICE FOR THEM or you will be chasing your tail for a long, long time.

In a land and time far, far away, where I claim client-server was invented, we had business users giving us servers for which we were accountable for providing health and welfare. Before the next budget cycle, I proposed to our GM, who got it approved by The Man, to have the business units budget for MB of data vs. hardware - we buy the hardware and we'll ensure recovery based on the following:
The cocktail weenies option was a database for which you can afford to lose a day's worth of data and accept a day of downtime - it cost $1 per MB.
To get no more than an hour loss of data and to be up w/in 4 hours of failure, that costs $2 per MB and was called the shrimp option.
And the caviar option was no more than 5 minutes data loss and you're up and running w/in 20 minutes. That'll be $10 per MB, thank you very much.

I let them choose, they'd transfer their budgets, and everyone lived happily ever after. For example, the DBAs on my team who had to go through the trouble of spending weekends ensuring the caviar option worked would receive a weekend, all expenses paid vacation, for them and their significant other following the harrowing experience. We spent the cavair cash in a way that made our users happier.

This also gave us the opportunity to consolidate our servers by about 50%. So, we bought less hardware and had happier customers.

Many stories to tell, some of them true, like running our most intense application on Beta NT and Beta SQL Server (their ship dates were synchronized) and delivering for our caviar clients. We hid beer under the floor tiles in the data center as a means to get us through some of our worse nights. Nothing like doing a Delete after sipping on a few Red Hooks.

The End.|||I'll assume that .MDF files are OK, so if that's the case, - there are 3 ways that accomplish the same thing (BTW, it does not matter how many LDF files there were originally, sp_attach_single_file_db will create only 1 new LDF file). For all three you need to delete or rename the original LDF files before you proceed (I recommend to move them to a safe location if none of the three methods will work). All three methods require the database to be in suspect mode, so don't mix the steps, and if you already changed the status, - make sure you pick up from there:

1. sp_configure 'allow', 1
reconfigure with override
go
update master.dbo.sysdatabases set status = status | 32768 where name = '<your_db>'
go
dbcc rebuild_log('<your_db>','c:\<your_db_log>.ldf')
go
alter database <your_db> set multi_user
go
sp_configure 'allow', 0
reconfigure with override
go

2. dbcc detachdb(<your_db>)
go
create database [<your_db>] on (filename='c:\<your_db>.mdf') for attach
go
if exists (select 1 from master.dbo.sysobjects where name = 'sp_removedbreplication') exec sp_removedbreplication '<your_db>'
go

3. ...Well, after all this - the third is the lazy man approach. SQL is actually trying to be nice to you, even if you put it to run on something that you shouldn't have ;) In short, after you renamed/deleted/moved the log devices while the databases were showing to be in Suspect mode, - just restart the service, and enjoy the show :D|||Do you think it's possible that .mdf file is ok and attach db or attach single file doesn't work? Have you come across this situation? I just try to collect experience from more knowledgable ones. mojza|||I'll assume that .MDF files are OK, so if that's the case, - there are 3 ways that accomplish the same thing (BTW, it does not matter how many LDF files there were originally, sp_attach_single_file_db will create only 1 new LDF file). For all three you need to delete or rename the original LDF files before you proceed (I recommend to move them to a safe location if none of the three methods will work). All three methods require the database to be in suspect mode, so don't mix the steps, and if you already changed the status, - make sure you pick up from there:

1. sp_configure 'allow', 1
reconfigure with override
go
update master.dbo.sysdatabases set status = status | 32768 where name = '<your_db>'
go
dbcc rebuild_log('<your_db>','c:\<your_db_log>.ldf')
go
alter database <your_db> set multi_user
go
sp_configure 'allow', 0
reconfigure with override
go

2. dbcc detachdb(<your_db>)
go
create database [<your_db>] on (filename='c:\<your_db>.mdf') for attach
go
if exists (select 1 from master.dbo.sysobjects where name = 'sp_removedbreplication') exec sp_removedbreplication '<your_db>'
go

3. ...Well, after all this - the third is the lazy man approach. SQL is actually trying to be nice to you, even if you put it to run on something that you shouldn't have ;) In short, after you renamed/deleted/moved the log devices while the databases were showing to be in Suspect mode, - just restart the service, and enjoy the show :D
I have tryed all three methods but it still does not work. I get the following error when I run the command dbcc rebuild_log

Server: Msg 3624, Level 20, State 1, Line 1

Location: filemgr.cpp:1890
Expression: fcb->GetSize () < fileSize
SPID: 52
Process ID: 3472

Connection Broken

I believe that the mdf file is damaged and that is why I cannot attach it as a single file. Do you know if there is any tool to export the data from the mdf file without being attached to SQL?

Error 9002 tempdb log file is full

Hi,
I've the following error message, which doesn't make sense to me.
The log file is not full, it has autogrow set to on and the disk drive on
which it
resides has 256GB free space. I hope i'm missing something and welcome
any suggestions on what to do or what to look at.
"Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log space."If the application is requesting space in TempDB and the request interval is
faster than the disk can allocate the space then you will see this message
whilst the Autogrow is happening.
If you have this much space then try pre allocating a larger TempDB
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Stressed" <k@.c.co.uk> wrote in message
news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> Hi,
>
> I've the following error message, which doesn't make sense to me.
> The log file is not full, it has autogrow set to on and the disk drive on
> which it
> resides has 256GB free space. I hope i'm missing something and welcome
> any suggestions on what to do or what to look at.
>
> "Server: Msg 9002, Level 17, State 6, Line 1
>
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log space."
>|||Thanks for that, it all makes sense now.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:O$FlXLJlDHA.1084@.tk2msftngp13.phx.gbl...
> If the application is requesting space in TempDB and the request interval
is
> faster than the disk can allocate the space then you will see this message
> whilst the Autogrow is happening.
> If you have this much space then try pre allocating a larger TempDB
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Stressed" <k@.c.co.uk> wrote in message
> news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> >
> >
> > I've the following error message, which doesn't make sense to me.
> >
> > The log file is not full, it has autogrow set to on and the disk drive
on
> > which it
> >
> > resides has 256GB free space. I hope i'm missing something and welcome
> >
> > any suggestions on what to do or what to look at.
> >
> >
> >
> > "Server: Msg 9002, Level 17, State 6, Line 1
> >
> >
> >
> > The log file for database 'tempdb' is full. Back up the
> >
> > transaction log for the database to free up some log space."
> >
> >
>|||Just to add - check with Profiler what is going on. TempDb could be a target
of a DoS attack.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:O$FlXLJlDHA.1084@.tk2msftngp13.phx.gbl...
> If the application is requesting space in TempDB and the request interval
is
> faster than the disk can allocate the space then you will see this message
> whilst the Autogrow is happening.
> If you have this much space then try pre allocating a larger TempDB
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Stressed" <k@.c.co.uk> wrote in message
> news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> >
> >
> > I've the following error message, which doesn't make sense to me.
> >
> > The log file is not full, it has autogrow set to on and the disk drive
on
> > which it
> >
> > resides has 256GB free space. I hope i'm missing something and welcome
> >
> > any suggestions on what to do or what to look at.
> >
> >
> >
> > "Server: Msg 9002, Level 17, State 6, Line 1
> >
> >
> >
> > The log file for database 'tempdb' is full. Back up the
> >
> > transaction log for the database to free up some log space."
> >
> >
>|||The server isn't exposed to the outside world, although the point has been
taken
on board for future occurences of the same.
Thanks.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OPFEUOJlDHA.3024@.tk2msftngp13.phx.gbl...
> Just to add - check with Profiler what is going on. TempDb could be a
target
> of a DoS attack.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:O$FlXLJlDHA.1084@.tk2msftngp13.phx.gbl...
> > If the application is requesting space in TempDB and the request
interval
> is
> > faster than the disk can allocate the space then you will see this
message
> > whilst the Autogrow is happening.
> >
> > If you have this much space then try pre allocating a larger TempDB
> >
> >
> >
> > --
> > --
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Stressed" <k@.c.co.uk> wrote in message
> > news:ufQ6UCJlDHA.2536@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > >
> > >
> > > I've the following error message, which doesn't make sense to me.
> > >
> > > The log file is not full, it has autogrow set to on and the disk drive
> on
> > > which it
> > >
> > > resides has 256GB free space. I hope i'm missing something and welcome
> > >
> > > any suggestions on what to do or what to look at.
> > >
> > >
> > >
> > > "Server: Msg 9002, Level 17, State 6, Line 1
> > >
> > >
> > >
> > > The log file for database 'tempdb' is full. Back up the
> > >
> > > transaction log for the database to free up some log space."
> > >
> > >
> >
> >
>sql

Error 823: I/O error (torn page) detected during read at offset...

Hi,
HDD failure caused to move the data to a new disk. We are no longer able to
attach the MDF file. I searched a lot but all of solutions work only when
the database is running under SQL Server and is suspected, not for me
(detached database).
I get this error when trying to attach the MDF file:
Error 823: I/O error (torn page) detected during read at offset
(0x0000036cba0000 in file 'D:\Data\Data_file)
Unfortunately the backup is 2 hours behind from the failure!
Any help would be greatly appreciated.
LeilaLeila (Leilas@.hotpop.com) writes:
> Date: Sun, 23 Apr 2006 20:49:51 +0430
Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.

> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database).
> I get this error when trying to attach the MDF file:
> Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
Well, at least there is a backup!
I have no idea how to handle the situation, and the only advice would
be to open a case with Microsoft, but I realise that this may not be
easy where you are located.
I'll inquire with Microsoft, but I would have too much hope.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
I really don't know what's wrong with my clock :-(

> I'll inquire with Microsoft, but I would have too much hope.
Thanks indeed!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>
> Well, at least there is a backup!
> I have no idea how to handle the situation, and the only advice would
> be to open a case with Microsoft, but I realise that this may not be
> easy where you are located.
> I'll inquire with Microsoft, but I would have too much hope.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
[vbcol=seagreen]
One suggestion I got from a fellow MVP was:
How about stopping SS, renaming the corrupted database files, starting
SS, create new database with same filenames as corrupted db had
originally, stopping SS, removing new database files and then renaming
corrupted db backup to the original names? SQL Server will possibly
recover the database fine...
If I were to do this operation, I would make sure that I have a copy of
the bad database files, to keep a starting point.
I'm a little skeptic that the above actually works. I guess that whar
you are really after is the transaction log, so you can back it up, and
apply it to the backup, so you don't lose those two hours of work.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila,
If you can read Russian, or fill in the gaps with
some online translation, this might help:
http://www.sql.ru/faq/faq_topic.aspx?fid=123
Steve Kass
Drew University
Leila wrote:

>I really don't know what's wrong with my clock :-(
>
>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>
>|||> As we say up here "Time goes fast, when you're having fun!". :-)
lol! We say exactly the same thing here! But it seems my notebook is having
fun, not me!

> How about stopping SS, renaming the corrupted database files, starting...
oops! Really tricky! I must try it...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AECB33EE3F0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> As we say up here "Time goes fast, when you're having fun!". :-)
>
> One suggestion I got from a fellow MVP was:
> How about stopping SS, renaming the corrupted database files, starting
> SS, create new database with same filenames as corrupted db had
> originally, stopping SS, removing new database files and then renaming
> corrupted db backup to the original names? SQL Server will possibly
> recover the database fine...
> If I were to do this operation, I would make sure that I have a copy of
> the bad database files, to keep a starting point.
> I'm a little skeptic that the above actually works. I guess that whar
> you are really after is the transaction log, so you can back it up, and
> apply it to the backup, so you don't lose those two hours of work.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Steve!
Unfortunately I cannot read Russian but the TSQL seems that you must at
least
have your database attached to SQL Server before using that instruction?
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23dKWuUwZGHA.3532@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Leila,
> If you can read Russian, or fill in the gaps with
> some online translation, this might help:
> http://www.sql.ru/faq/faq_topic.aspx?fid=123
> Steve Kass
> Drew University
> Leila wrote:
>|||Leila (Leilas@.hotpop.com) writes:
> Unfortunately I cannot read Russian but the TSQL seems that you must at
> least
> have your database attached to SQL Server before using that instruction?
Which can be solved by creating a database, and the throwing the files
for that away, and then slapping the files for the broken database in
place.
However, I doubt that this is the solution to your problem. That script
seems to create an empty log file, and if I understand correctly, the
log file is what you want to be able to access?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database). I get this error when trying to attach the MDF
> file: Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
I guess that you by now already have restored that two-hour old backup.
Unfortunately, it took some time to find out the correct procedure.
It is described on
http://www.sqljunkies.com/HowTo/F5B...8DDB08B0C1.scuk
With quite some help of some MVP colleagues I developed the demo script
below, to illustrate the procedure.
It assumes that you know the name and location of your log files, but
with a full backup available, it should not be difficult to work out.
-- See also
-- http://www.sqljunkies.com/HowTo/F5B...8DDB08B0C1.scuk
-- First take a copy of Northwind that we can mess up a bit.
BACKUP DATABASE Northwind to disk = 'c:\temp\Northwind.bak'
go
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\temp\northbreeze.mdf',
MOVE 'Northwind_log' TO 'C:\temp\northbreeze.ldf',
REPLACE
go
-- And make sure that it has full recovery.
ALTER DATABASE northbreeze SET RECOVERY FULL
go
USE northbreeze
go
-- Let's add a new customer to northbreeze.
INSERT Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode,
Country, Phone, Fax)
VALUES ('GARBO', 'Greta Garbos Deliktatesshandel', 'Frk. Gustafsson',
'Frken', 'Mosebacke Torg 12', 'Stockholm', NULL, '11223',
'Sweden', '+46-8-90510', NULL)
go
-- Make sure that this database is backed up on its own. Backup the log
-- as well.
BACKUP DATABASE northbreeze to disk = 'c:\temp\Northbreeze.bak'
BACKUP LOG northbreeze to disk = 'c:\temp\Northbreeze.logbak'
go
-- Perform further updates in the database.
DELETE Customers
FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
AND C.CustomerID <> 'GARBO'
go
-- Go to master
USE master
go
-- And detach database
EXEC sp_detach_db northbreeze
go
-- Assume that something bad happens to the MDF file while it is detached.
-- First step is to rename the files. (Although we could toss the bsd MDF
-- file.)
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.mdf c:\temp\northbreeze-
crashed.mdf'
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-
crashed.ldf'
go
-- Create a new empty database were we want the real thing to be. Make sure
-- that we have full recovery-
CREATE DATABASE northbreeze
ON (NAME = 'Northwind', FILENAME = 'C:\temp\northbreeze.mdf')
LOG ON (NAME = 'Northwind.log', FILENAME = 'C:\temp\northbreeze.ldf')
ALTER DATABASE northbreeze SET RECOVERY FULL
go
-- Shut down SQL Server.
SHUTDOWN
go
-- In file system do this:
-- 1) move northbreeze.mdf to northbreeze-dummy.mdf (or delete it)
-- 1) move northbreeze.ldf to northbreeze-dummy.ldf (or delete it)
-- 2) move northbreeze-crashed.ldf to northbreeze.ldf
-- Start server again
go
-- The northbreeze database does at this point consist of the log file only!
-- (Or SQL Server created a new MDF; I did not check.)
-- We can now back up the tail of the log. Note NO_TRUNCATE!
BACKUP LOG northbreeze TO DISK = 'C:\temp\northbreeze-tail.logbak'
WITH NO_TRUNCATE
go
-- Drop the database
DROP DATABASE northbreeze
go
-- The log file may still be there, just move it out of the way.
EXEC xp_cmdshell
'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-crashed.ldf'
go
-- Restore the database, use no NORECOVER, as we will apply logs as well.
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northbreeze.bak'
WITH NORECOVERY
-- Restore the fist log dump.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze.logbak'
WITH NORECOVERY
-- Apply the tail log.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze-tail.logbak'
go
-- Verify that northbreeze != Northwind.
SELECT a.CustomerID, b.CustomerID
FROM Northwind..Customers a
FULL JOIN northbreeze..Customers b ON a.CustomerID = b.CustomerID
WHERE a.CustomerID IS NULL OR
b.CustomerID IS NULL
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error 823: I/O error (torn page) detected during read at offset...

Hi,
HDD failure caused to move the data to a new disk. We are no longer able to
attach the MDF file. I searched a lot but all of solutions work only when
the database is running under SQL Server and is suspected, not for me
(detached database).
I get this error when trying to attach the MDF file:
Error 823: I/O error (torn page) detected during read at offset
(0x0000036cba0000 in file 'D:\Data\Data_file)
Unfortunately the backup is 2 hours behind from the failure!
Any help would be greatly appreciated.
LeilaLeila (Leilas@.hotpop.com) writes:
> Date: Sun, 23 Apr 2006 20:49:51 +0430
Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.

> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database).
> I get this error when trying to attach the MDF file:
> Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
Well, at least there is a backup!
I have no idea how to handle the situation, and the only advice would
be to open a case with Microsoft, but I realise that this may not be
easy where you are located.
I'll inquire with Microsoft, but I would have too much hope.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
I really don't know what's wrong with my clock :-(

> I'll inquire with Microsoft, but I would have too much hope.
Thanks indeed!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>
> Well, at least there is a backup!
> I have no idea how to handle the situation, and the only advice would
> be to open a case with Microsoft, but I realise that this may not be
> easy where you are located.
> I'll inquire with Microsoft, but I would have too much hope.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
One suggestion I got from a fellow MVP was:
How about stopping SS, renaming the corrupted database files, starting
SS, create new database with same filenames as corrupted db had
originally, stopping SS, removing new database files and then renaming
corrupted db backup to the original names? SQL Server will possibly
recover the database fine...
If I were to do this operation, I would make sure that I have a copy of
the bad database files, to keep a starting point.
I'm a little skeptic that the above actually works. I guess that whar
you are really after is the transaction log, so you can back it up, and
apply it to the backup, so you don't lose those two hours of work.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila,
If you can read Russian, or fill in the gaps with
some online translation, this might help:
http://www.sql.ru/faq/faq_topic.aspx?fid=123
Steve Kass
Drew University
Leila wrote:

>I really don't know what's wrong with my clock :-(
>
>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>
>|||> As we say up here "Time goes fast, when you're having fun!". :-)
lol! We say exactly the same thing here! But it seems my notebook is having
fun, not me!

> How about stopping SS, renaming the corrupted database files, starting...
oops! Really tricky! I must try it...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AECB33EE3F0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> As we say up here "Time goes fast, when you're having fun!". :-)
>
> One suggestion I got from a fellow MVP was:
> How about stopping SS, renaming the corrupted database files, starting
> SS, create new database with same filenames as corrupted db had
> originally, stopping SS, removing new database files and then renaming
> corrupted db backup to the original names? SQL Server will possibly
> recover the database fine...
> If I were to do this operation, I would make sure that I have a copy of
> the bad database files, to keep a starting point.
> I'm a little skeptic that the above actually works. I guess that whar
> you are really after is the transaction log, so you can back it up, and
> apply it to the backup, so you don't lose those two hours of work.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Steve!
Unfortunately I cannot read Russian but the TSQL seems that you must at
least
have your database attached to SQL Server before using that instruction?
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23dKWuUwZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Leila,
> If you can read Russian, or fill in the gaps with
> some online translation, this might help:
> http://www.sql.ru/faq/faq_topic.aspx?fid=123
> Steve Kass
> Drew University
> Leila wrote:
>|||Leila (Leilas@.hotpop.com) writes:
> Unfortunately I cannot read Russian but the TSQL seems that you must at
> least
> have your database attached to SQL Server before using that instruction?
Which can be solved by creating a database, and the throwing the files
for that away, and then slapping the files for the broken database in
place.
However, I doubt that this is the solution to your problem. That script
seems to create an empty log file, and if I understand correctly, the
log file is what you want to be able to access?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database). I get this error when trying to attach the MDF
> file: Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
I guess that you by now already have restored that two-hour old backup.
Unfortunately, it took some time to find out the correct procedure.
It is described on
http://www.sqljunkies.com/HowTo/F5B...8DDB08B0C1.scuk
With quite some help of some MVP colleagues I developed the demo script
below, to illustrate the procedure.
It assumes that you know the name and location of your log files, but
with a full backup available, it should not be difficult to work out.
-- See also
-- http://www.sqljunkies.com/HowTo/F5B...8DDB08B0C1.scuk
-- First take a copy of Northwind that we can mess up a bit.
BACKUP DATABASE Northwind to disk = 'c:\temp\Northwind.bak'
go
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\temp\northbreeze.mdf',
MOVE 'Northwind_log' TO 'C:\temp\northbreeze.ldf',
REPLACE
go
-- And make sure that it has full recovery.
ALTER DATABASE northbreeze SET RECOVERY FULL
go
USE northbreeze
go
-- Let's add a new customer to northbreeze.
INSERT Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode,
Country, Phone, Fax)
VALUES ('GARBO', 'Greta Garbos Deliktatesshandel', 'Frk. Gustafsson',
'Frken', 'Mosebacke Torg 12', 'Stockholm', NULL, '11223',
'Sweden', '+46-8-90510', NULL)
go
-- Make sure that this database is backed up on its own. Backup the log
-- as well.
BACKUP DATABASE northbreeze to disk = 'c:\temp\Northbreeze.bak'
BACKUP LOG northbreeze to disk = 'c:\temp\Northbreeze.logbak'
go
-- Perform further updates in the database.
DELETE Customers
FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
AND C.CustomerID <> 'GARBO'
go
-- Go to master
USE master
go
-- And detach database
EXEC sp_detach_db northbreeze
go
-- Assume that something bad happens to the MDF file while it is detached.
-- First step is to rename the files. (Although we could toss the bsd MDF
-- file.)
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.mdf c:\temp\northbreeze-
crashed.mdf'
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-
crashed.ldf'
go
-- Create a new empty database were we want the real thing to be. Make sure
-- that we have full recovery-
CREATE DATABASE northbreeze
ON (NAME = 'Northwind', FILENAME = 'C:\temp\northbreeze.mdf')
LOG ON (NAME = 'Northwind.log', FILENAME = 'C:\temp\northbreeze.ldf')
ALTER DATABASE northbreeze SET RECOVERY FULL
go
-- Shut down SQL Server.
SHUTDOWN
go
-- In file system do this:
-- 1) move northbreeze.mdf to northbreeze-dummy.mdf (or delete it)
-- 1) move northbreeze.ldf to northbreeze-dummy.ldf (or delete it)
-- 2) move northbreeze-crashed.ldf to northbreeze.ldf
-- Start server again
go
-- The northbreeze database does at this point consist of the log file only!
-- (Or SQL Server created a new MDF; I did not check.)
-- We can now back up the tail of the log. Note NO_TRUNCATE!
BACKUP LOG northbreeze TO DISK = 'C:\temp\northbreeze-tail.logbak'
WITH NO_TRUNCATE
go
-- Drop the database
DROP DATABASE northbreeze
go
-- The log file may still be there, just move it out of the way.
EXEC xp_cmdshell
'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-crashed.ldf'
go
-- Restore the database, use no NORECOVER, as we will apply logs as well.
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northbreeze.bak'
WITH NORECOVERY
-- Restore the fist log dump.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze.logbak'
WITH NORECOVERY
-- Apply the tail log.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze-tail.logbak'
go
-- Verify that northbreeze != Northwind.
SELECT a.CustomerID, b.CustomerID
FROM Northwind..Customers a
FULL JOIN northbreeze..Customers b ON a.CustomerID = b.CustomerID
WHERE a.CustomerID IS NULL OR
b.CustomerID IS NULL
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Error 823: I/O error (torn page) detected during read at offset...

Hi,
HDD failure caused to move the data to a new disk. We are no longer able to
attach the MDF file. I searched a lot but all of solutions work only when
the database is running under SQL Server and is suspected, not for me
(detached database).
I get this error when trying to attach the MDF file:
Error 823: I/O error (torn page) detected during read at offset
(0x0000036cba0000 in file 'D:\Data\Data_file)
Unfortunately the backup is 2 hours behind from the failure!
Any help would be greatly appreciated.
LeilaLeila (Leilas@.hotpop.com) writes:
> Date: Sun, 23 Apr 2006 20:49:51 +0430
Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database).
> I get this error when trying to attach the MDF file:
> Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
Well, at least there is a backup!
I have no idea how to handle the situation, and the only advice would
be to open a case with Microsoft, but I realise that this may not be
easy where you are located.
I'll inquire with Microsoft, but I would have too much hope.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
I really don't know what's wrong with my clock :-(
> I'll inquire with Microsoft, but I would have too much hope.
Thanks indeed!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Date: Sun, 23 Apr 2006 20:49:51 +0430
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>> HDD failure caused to move the data to a new disk. We are no longer able
>> to attach the MDF file. I searched a lot but all of solutions work only
>> when the database is running under SQL Server and is suspected, not for
>> me (detached database).
>> I get this error when trying to attach the MDF file:
>> Error 823: I/O error (torn page) detected during read at offset
>> (0x0000036cba0000 in file 'D:\Data\Data_file)
>> Unfortunately the backup is 2 hours behind from the failure!
>> Any help would be greatly appreciated.
> Well, at least there is a backup!
> I have no idea how to handle the situation, and the only advice would
> be to open a case with Microsoft, but I realise that this may not be
> easy where you are located.
> I'll inquire with Microsoft, but I would have too much hope.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
>> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
>> I'll inquire with Microsoft, but I would have too much hope.
One suggestion I got from a fellow MVP was:
How about stopping SS, renaming the corrupted database files, starting
SS, create new database with same filenames as corrupted db had
originally, stopping SS, removing new database files and then renaming
corrupted db backup to the original names? SQL Server will possibly
recover the database fine...
If I were to do this operation, I would make sure that I have a copy of
the bad database files, to keep a starting point.
I'm a little skeptic that the above actually works. I guess that whar
you are really after is the transaction log, so you can back it up, and
apply it to the backup, so you don't lose those two hours of work.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila,
If you can read Russian, or fill in the gaps with
some online translation, this might help:
http://www.sql.ru/faq/faq_topic.aspx?fid=123
Steve Kass
Drew University
Leila wrote:
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>
>I really don't know what's wrong with my clock :-(
>
>>I'll inquire with Microsoft, but I would have too much hope.
>>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>>Leila (Leilas@.hotpop.com) writes:
>>
>>Date: Sun, 23 Apr 2006 20:49:51 +0430
>>
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>
>>HDD failure caused to move the data to a new disk. We are no longer able
>>to attach the MDF file. I searched a lot but all of solutions work only
>>when the database is running under SQL Server and is suspected, not for
>>me (detached database).
>>I get this error when trying to attach the MDF file:
>>Error 823: I/O error (torn page) detected during read at offset
>>(0x0000036cba0000 in file 'D:\Data\Data_file)
>>Unfortunately the backup is 2 hours behind from the failure!
>>Any help would be greatly appreciated.
>>
>>Well, at least there is a backup!
>>I have no idea how to handle the situation, and the only advice would
>>be to open a case with Microsoft, but I realise that this may not be
>>easy where you are located.
>>I'll inquire with Microsoft, but I would have too much hope.
>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>Books Online for SQL Server 2005 at
>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>>Books Online for SQL Server 2000 at
>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
>|||> As we say up here "Time goes fast, when you're having fun!". :-)
lol! We say exactly the same thing here! But it seems my notebook is having
fun, not me!
> How about stopping SS, renaming the corrupted database files, starting...
oops! Really tricky! I must try it...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AECB33EE3F0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>> I really don't know what's wrong with my clock :-(
> As we say up here "Time goes fast, when you're having fun!". :-)
>
>> I'll inquire with Microsoft, but I would have too much hope.
> One suggestion I got from a fellow MVP was:
> How about stopping SS, renaming the corrupted database files, starting
> SS, create new database with same filenames as corrupted db had
> originally, stopping SS, removing new database files and then renaming
> corrupted db backup to the original names? SQL Server will possibly
> recover the database fine...
> If I were to do this operation, I would make sure that I have a copy of
> the bad database files, to keep a starting point.
> I'm a little skeptic that the above actually works. I guess that whar
> you are really after is the transaction log, so you can back it up, and
> apply it to the backup, so you don't lose those two hours of work.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks Steve!
Unfortunately I cannot read Russian but the TSQL seems that you must at
least
have your database attached to SQL Server before using that instruction?
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23dKWuUwZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Leila,
> If you can read Russian, or fill in the gaps with
> some online translation, this might help:
> http://www.sql.ru/faq/faq_topic.aspx?fid=123
> Steve Kass
> Drew University
> Leila wrote:
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>I really don't know what's wrong with my clock :-(
>>
>>I'll inquire with Microsoft, but I would have too much hope.
>>Thanks indeed!
>>
>>
>>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>>Leila (Leilas@.hotpop.com) writes:
>>Date: Sun, 23 Apr 2006 20:49:51 +0430
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>
>>HDD failure caused to move the data to a new disk. We are no longer able
>>to attach the MDF file. I searched a lot but all of solutions work only
>>when the database is running under SQL Server and is suspected, not for
>>me (detached database).
>>I get this error when trying to attach the MDF file:
>>Error 823: I/O error (torn page) detected during read at offset
>>(0x0000036cba0000 in file 'D:\Data\Data_file)
>>Unfortunately the backup is 2 hours behind from the failure!
>>Any help would be greatly appreciated.
>>Well, at least there is a backup!
>>I have no idea how to handle the situation, and the only advice would
>>be to open a case with Microsoft, but I realise that this may not be
>>easy where you are located.
>>I'll inquire with Microsoft, but I would have too much hope.
>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>Books Online for SQL Server 2005 at
>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>>Books Online for SQL Server 2000 at
>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>|||Leila (Leilas@.hotpop.com) writes:
> Unfortunately I cannot read Russian but the TSQL seems that you must at
> least
> have your database attached to SQL Server before using that instruction?
Which can be solved by creating a database, and the throwing the files
for that away, and then slapping the files for the broken database in
place.
However, I doubt that this is the solution to your problem. That script
seems to create an empty log file, and if I understand correctly, the
log file is what you want to be able to access?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database). I get this error when trying to attach the MDF
> file: Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
I guess that you by now already have restored that two-hour old backup.
Unfortunately, it took some time to find out the correct procedure.
It is described on
http://www.sqljunkies.com/HowTo/F5B99949-4D67-4B27-B752-AA8DDB08B0C1.scuk
With quite some help of some MVP colleagues I developed the demo script
below, to illustrate the procedure.
It assumes that you know the name and location of your log files, but
with a full backup available, it should not be difficult to work out.
-- See also
-- http://www.sqljunkies.com/HowTo/F5B99949-4D67-4B27-B752-AA8DDB08B0C1.scuk
-- First take a copy of Northwind that we can mess up a bit.
BACKUP DATABASE Northwind to disk = 'c:\temp\Northwind.bak'
go
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\temp\northbreeze.mdf',
MOVE 'Northwind_log' TO 'C:\temp\northbreeze.ldf',
REPLACE
go
-- And make sure that it has full recovery.
ALTER DATABASE northbreeze SET RECOVERY FULL
go
USE northbreeze
go
-- Let's add a new customer to northbreeze.
INSERT Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode,
Country, Phone, Fax)
VALUES ('GARBO', 'Greta Garbos Deliktatesshandel', 'Frk. Gustafsson',
'Fröken', 'Mosebacke Torg 12', 'Stockholm', NULL, '11223',
'Sweden', '+46-8-90510', NULL)
go
-- Make sure that this database is backed up on its own. Backup the log
-- as well.
BACKUP DATABASE northbreeze to disk = 'c:\temp\Northbreeze.bak'
BACKUP LOG northbreeze to disk = 'c:\temp\Northbreeze.logbak'
go
-- Perform further updates in the database.
DELETE Customers
FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
AND C.CustomerID <> 'GARBO'
go
-- Go to master
USE master
go
-- And detach database
EXEC sp_detach_db northbreeze
go
-- Assume that something bad happens to the MDF file while it is detached.
-- First step is to rename the files. (Although we could toss the bsd MDF
-- file.)
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.mdf c:\temp\northbreeze-
crashed.mdf'
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-
crashed.ldf'
go
-- Create a new empty database were we want the real thing to be. Make sure
-- that we have full recovery-
CREATE DATABASE northbreeze
ON (NAME = 'Northwind', FILENAME = 'C:\temp\northbreeze.mdf')
LOG ON (NAME = 'Northwind.log', FILENAME = 'C:\temp\northbreeze.ldf')
ALTER DATABASE northbreeze SET RECOVERY FULL
go
-- Shut down SQL Server.
SHUTDOWN
go
-- In file system do this:
-- 1) move northbreeze.mdf to northbreeze-dummy.mdf (or delete it)
-- 1) move northbreeze.ldf to northbreeze-dummy.ldf (or delete it)
-- 2) move northbreeze-crashed.ldf to northbreeze.ldf
-- Start server again
go
-- The northbreeze database does at this point consist of the log file only!
-- (Or SQL Server created a new MDF; I did not check.)
-- We can now back up the tail of the log. Note NO_TRUNCATE!
BACKUP LOG northbreeze TO DISK = 'C:\temp\northbreeze-tail.logbak'
WITH NO_TRUNCATE
go
-- Drop the database
DROP DATABASE northbreeze
go
-- The log file may still be there, just move it out of the way.
EXEC xp_cmdshell
'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-crashed.ldf'
go
-- Restore the database, use no NORECOVER, as we will apply logs as well.
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northbreeze.bak'
WITH NORECOVERY
-- Restore the fist log dump.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze.logbak'
WITH NORECOVERY
-- Apply the tail log.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze-tail.logbak'
go
-- Verify that northbreeze != Northwind.
SELECT a.CustomerID, b.CustomerID
FROM Northwind..Customers a
FULL JOIN northbreeze..Customers b ON a.CustomerID = b.CustomerID
WHERE a.CustomerID IS NULL OR
b.CustomerID IS NULL
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Error 5149: Not enough disk space

Newbie question:
Importing a 25,000,000 record .csv file into SQL2000 fails
with a disk space error. I go into sql enterprise manager
to increase the size of the database and it will only let
me go to about 4000 MB. I need about 30GIG. At 5000 MB
enterprise manager says:
Error 5149: ModifyFile encountered operating system error
112 (There is not enough space on the disk while
attempting to expand physical file).
I have 50GIG free on that disk.
What am I doing wrong'"Ben Silvert" <anonymous@.discussions.microsoft.com> wrote in message
news:1be701c39fc7$d4faa450$3101280a@.phx.gbl...
> Newbie question:
> Importing a 25,000,000 record .csv file into SQL2000 fails
> with a disk space error. I go into sql enterprise manager
> to increase the size of the database and it will only let
> me go to about 4000 MB. I need about 30GIG. At 5000 MB
> enterprise manager says:
> Error 5149: ModifyFile encountered operating system error
> 112 (There is not enough space on the disk while
> attempting to expand physical file).
> I have 50GIG free on that disk.
> What am I doing wrong'
It's possible that you are either running out of transaction log space (on
either the target database) or on tempdb -- check that you have enabled
database file autogrow, or have size the database large enough to hold the
imported data (my preference).
If this fails, you may want to watch the server os page file (performance
monitor) in case the os is swapping out memory to disk...
Steve|||I'm getting this error when I go into properties of the
database, Data Files tab, Space Allocated (MB) column. I
try to change the entry from 4000 to 5000, and it gives me
the 5149 error. I'm not trying to import the data, yet.
When I tried before to import the data it failed with a
message the Primary File Group ran out of space. So I
deleted the database, made a new one, and tried to set the
data file size, to no avail.
The database is set to grow automatically, and it grows
unlimited.
>--Original Message--
>"Ben Silvert" <anonymous@.discussions.microsoft.com> wrote
in message
>news:1be701c39fc7$d4faa450$3101280a@.phx.gbl...
>> Newbie question:
>> Importing a 25,000,000 record .csv file into SQL2000
fails
>> with a disk space error. I go into sql enterprise
manager
>> to increase the size of the database and it will only
let
>> me go to about 4000 MB. I need about 30GIG. At 5000 MB
>> enterprise manager says:
>> Error 5149: ModifyFile encountered operating system
error
>> 112 (There is not enough space on the disk while
>> attempting to expand physical file).
>> I have 50GIG free on that disk.
>> What am I doing wrong'
>It's possible that you are either running out of
transaction log space (on
>either the target database) or on tempdb -- check that
you have enabled
>database file autogrow, or have size the database large
enough to hold the
>imported data (my preference).
>If this fails, you may want to watch the server os page
file (performance
>monitor) in case the os is swapping out memory to disk...
>Steve
>
>.
>|||Could it be the version of SQL? I have the personal
edition?
>--Original Message--
>Newbie question:
>Importing a 25,000,000 record .csv file into SQL2000
fails
>with a disk space error. I go into sql enterprise manager
>to increase the size of the database and it will only let
>me go to about 4000 MB. I need about 30GIG. At 5000 MB
>enterprise manager says:
>Error 5149: ModifyFile encountered operating system error
>112 (There is not enough space on the disk while
>attempting to expand physical file).
>I have 50GIG free on that disk.
>What am I doing wrong'
>.
>

Error 3271

Hi,
during a backup of a database with the following command I get an error:
backup database TV_TOPO to disk='d:\Backup.nacher\TV_TOPO.bck'
Server: Nachr.-Nr. 3271, Schweregrad 16, Status 1, Zeile 1
Nonrecoverable I/O error occurred on file 'D:\Database\TV_TOPO.mdf'.
Server: Nachr.-Nr. 3013, Schweregrad 16, Status 1, Zeile 1
BACKUP DATABASE is terminating abnormally.
What can I do to repair the database files ?
Best regards
Axel LanserHi,
See SQL Server error log and Event viewer logs for more informations on this
error.
Run a DBCC CHECKDB on the database to make sure that it doesnt have any
consistency or allocation errors. It is most likely there will be errorr.
In that case you see the DBCC Checkdb with repair options in Books online.
If the database have errors and if it is difficult to solve using repair
options, Best option will be is to RESTORE from last
healthy backup or contact MS Product support.
Thanks
Hari
SQL Server MVP
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:B471FEE1-F07D-44F5-9150-ABF4C5BF630B@.microsoft.com...
> Hi,
> during a backup of a database with the following command I get an error:
> backup database TV_TOPO to disk='d:\Backup.nacher\TV_TOPO.bck'
> Server: Nachr.-Nr. 3271, Schweregrad 16, Status 1, Zeile 1
> Nonrecoverable I/O error occurred on file 'D:\Database\TV_TOPO.mdf'.
> Server: Nachr.-Nr. 3013, Schweregrad 16, Status 1, Zeile 1
> BACKUP DATABASE is terminating abnormally.
> What can I do to repair the database files ?
> Best regards
> Axel Lanser