I've been given a database backup in the form of an .mdf file, a bunch
of .ndf files, and a .log file. When I try to attach the .mdf file in
Server Management Studio, I get error 9004, An error occurred while
processing the log.
Any suggestions?
Phil Hibbs.Phil,
Kevin Farlee's answer has helped a couple of people with your problem.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=598396&SiteID=1
RLF
"PhilHibbs" <snarks@.gmail.com> wrote in message
news:1193140510.353843.250240@.y27g2000pre.googlegroups.com...
> I've been given a database backup in the form of an .mdf file, a bunch
> of .ndf files, and a .log file. When I try to attach the .mdf file in
> Server Management Studio, I get error 9004, An error occurred while
> processing the log.
> Any suggestions?
> Phil Hibbs.
>|||On 23 Oct, 13:31, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Kevin Farlee's answer has helped a couple of people with your problem.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=598396&SiteID=1
Hm, looked promising, but when I try to "bring online" the database
with the files replaced, I get a load of errors in this form:
E:\data\data_file_09.ndf is not a primary database file
I have a .mdf file and 26 .ndf files, it looks like the creation of
the empty database is creating these files in a different way to the
original database.
Phil Hibbs.|||Phil,
Did you first create the 26 ndf files in your database to be replaced before
trying to copy the files into place?
RLF
"PhilHibbs" <snarks@.gmail.com> wrote in message
news:1193148065.531336.193770@.e34g2000pro.googlegroups.com...
> On 23 Oct, 13:31, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> Kevin Farlee's answer has helped a couple of people with your problem.
>> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=598396&SiteID=1
> Hm, looked promising, but when I try to "bring online" the database
> with the files replaced, I get a load of errors in this form:
> E:\data\data_file_09.ndf is not a primary database file
> I have a .mdf file and 26 .ndf files, it looks like the creation of
> the empty database is creating these files in a different way to the
> original database.
> Phil Hibbs.
>|||On 23 Oct, 20:15, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Did you first create the 26 ndf files in your database to be replaced before
> trying to copy the files into place?
Yep. I made a big CREATE DATABASE statement with the .mdf and all
the .ndf files specified in it.
Phil Hibbs.|||On 23 Oct, 20:15, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Did you first create the 26 ndf files in your database to be replaced before
> trying to copy the files into place?
Yep. I made a big CREATE DATABASE statement with the .mdf and all
the .ndf files specified in it.
Phil Hibbs.|||Phil,
I understand that you are using SQL Server 2005, since you mention
Management Studio. I have tested and not been able to duplicate your
problem. When you use SSMS to do the attach, have you pressed the Script
command at the top of the panel. This will provide an exact script of the
commands to be run. If you do that, perhaps you will see something helpful.
Or, you can post it here for review.
Obviously something is different between our setups. I am running SQL
Server 2005 SP2. Are you also running SP2? I ask because SP2 fixed some
Management Studio problems and you should update to it (plus some hotfixes)
if you have not done so.
This describe my patch level:
http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
For more recent fixes, see also: http://support.microsoft.com/kb/937137/LN/
RLF
"PhilHibbs" <snarks@.gmail.com> wrote in message
news:1193222031.764752.35650@.v29g2000prd.googlegroups.com...
> On 23 Oct, 20:15, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> Did you first create the 26 ndf files in your database to be replaced
>> before
>> trying to copy the files into place?
> Yep. I made a big CREATE DATABASE statement with the .mdf and all
> the .ndf files specified in it.
> Phil Hibbs.
>|||On 25 Oct, 17:34, "Russell Fields" <russellfie...@.nomail.com> wrote:
> I have tested and not been able to duplicate your
> problem.
I suspect that is because you don't have the same .mdf and .ndf
and .log files that I have.
Phil Hibbs.|||Phil, No doubt about that. Sorry this did not help. - RLF
"PhilHibbs" <snarks@.gmail.com> wrote in message
news:1193761341.010344.78000@.k79g2000hse.googlegroups.com...
> On 25 Oct, 17:34, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> I have tested and not been able to duplicate your
>> problem.
> I suspect that is because you don't have the same .mdf and .ndf
> and .log files that I have.
> Phil Hibbs.
>
Showing posts with label attach. Show all posts
Showing posts with label attach. Show all posts
Tuesday, March 27, 2012
Error 9003 : fail to attach a database
Hi,
I sucessful attached a database, but deleted it without detach. When I tried
to attach again got this error 9003: The LSN(12:32:1) passed to log scan in
database "Contacts" is invalid.
I tried to detach it, but I can't find the database in system, so it fails t
oo.
Please help, thanks in advance!
IreneIrene,
Do you have the .mdf and .ldf files for Contacts?
Did you use the Enterprise Manager or issue the sp_attach_db and sp_detach_d
b from the Query Analyzer?
...but deleted it without detach...
What does this mean? How was this accomplished? Explain...
Does Contacts show up when you run this...
SELECT * FROM master..sysdatabases
Norman|||Hi, Norman
I used the Enterprise Manager to attach the Contact.mdf and Contact.ldf.
After attached it, I found the new database "Contact" was readonly. I
made a mistake to del the database "Contact" directly in the Enterprise
Manager explorer.
When I tried to attach the file again in the Enterprise Manager
explorer, it shows the error 9003. Then I tried sp_detach_db "Contact",
it fails.
When I run "SELECT * FROM sysdatabases", no "Contact" database record
in.
I guest it still has some reference files exist in database server, but
I don't know how to restore it.
Thanks again.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi, Norman
I used the Enterprise Manager to attach the Contact.mdf and Contact.ldf.
After attached it, I found the new database "Contact" was readonly. I made a
mistake to del the database "Contact" directly in the Enterprise Manager ex
plorer.
When I tried to attach the file again in the Enterprise Manager explorer, it
shows the error 9003. Then I tried sp_detach_db "Contact", it fails.
When I run "SELECT * FROM sysdatabases", no "Contact" database record in.
I guest it still has some reference files exist in database server, but I do
n't know how to restore it.
I sucessful attached a database, but deleted it without detach. When I tried
to attach again got this error 9003: The LSN(12:32:1) passed to log scan in
database "Contacts" is invalid.
I tried to detach it, but I can't find the database in system, so it fails t
oo.
Please help, thanks in advance!
IreneIrene,
Do you have the .mdf and .ldf files for Contacts?
Did you use the Enterprise Manager or issue the sp_attach_db and sp_detach_d
b from the Query Analyzer?
...but deleted it without detach...
What does this mean? How was this accomplished? Explain...
Does Contacts show up when you run this...
SELECT * FROM master..sysdatabases
Norman|||Hi, Norman
I used the Enterprise Manager to attach the Contact.mdf and Contact.ldf.
After attached it, I found the new database "Contact" was readonly. I
made a mistake to del the database "Contact" directly in the Enterprise
Manager explorer.
When I tried to attach the file again in the Enterprise Manager
explorer, it shows the error 9003. Then I tried sp_detach_db "Contact",
it fails.
When I run "SELECT * FROM sysdatabases", no "Contact" database record
in.
I guest it still has some reference files exist in database server, but
I don't know how to restore it.
Thanks again.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi, Norman
I used the Enterprise Manager to attach the Contact.mdf and Contact.ldf.
After attached it, I found the new database "Contact" was readonly. I made a
mistake to del the database "Contact" directly in the Enterprise Manager ex
plorer.
When I tried to attach the file again in the Enterprise Manager explorer, it
shows the error 9003. Then I tried sp_detach_db "Contact", it fails.
When I run "SELECT * FROM sysdatabases", no "Contact" database record in.
I guest it still has some reference files exist in database server, but I do
n't know how to restore it.
Error 9003 : fail to attach a database
Hi
I sucessful attached a database, but deleted it without detach. When I tried to attach again got this error 9003: The LSN(12:32:1) passed to log scan in database "Contacts" is invalid
I tried to detach it, but I can't find the database in system, so it fails too
Please help, thanks in advance
IreneIrene
Do you have the .mdf and .ldf files for Contacts
Did you use the Enterprise Manager or issue the sp_attach_db and sp_detach_db from the Query Analyzer
...but deleted it without detach..
What does this mean? How was this accomplished? Explain..
Does Contacts show up when you run this..
SELECT * FROM master..sysdatabase
Norman|||Hi, Norma
I used the Enterprise Manager to attach the Contact.mdf and Contact.ldf
After attached it, I found the new database "Contact" was readonly. I made a mistake to del the database "Contact" directly in the Enterprise Manager explorer
When I tried to attach the file again in the Enterprise Manager explorer, it shows the error 9003. Then I tried sp_detach_db "Contact", it fails.
When I run "SELECT * FROM sysdatabases", no "Contact" database record in
I guest it still has some reference files exist in database server, but I don't know how to restore it.
I sucessful attached a database, but deleted it without detach. When I tried to attach again got this error 9003: The LSN(12:32:1) passed to log scan in database "Contacts" is invalid
I tried to detach it, but I can't find the database in system, so it fails too
Please help, thanks in advance
IreneIrene
Do you have the .mdf and .ldf files for Contacts
Did you use the Enterprise Manager or issue the sp_attach_db and sp_detach_db from the Query Analyzer
...but deleted it without detach..
What does this mean? How was this accomplished? Explain..
Does Contacts show up when you run this..
SELECT * FROM master..sysdatabase
Norman|||Hi, Norma
I used the Enterprise Manager to attach the Contact.mdf and Contact.ldf
After attached it, I found the new database "Contact" was readonly. I made a mistake to del the database "Contact" directly in the Enterprise Manager explorer
When I tried to attach the file again in the Enterprise Manager explorer, it shows the error 9003. Then I tried sp_detach_db "Contact", it fails.
When I run "SELECT * FROM sysdatabases", no "Contact" database record in
I guest it still has some reference files exist in database server, but I don't know how to restore it.
Monday, March 26, 2012
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
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 823 : SQL Server
Hi
I'm getting an Error 823, when I tried to attach a detached SQL DB
[JPG file attached]
Tried this KB http://support.microsoft.com/default...b;EN-US;828339
But does not seem to work..
Any Ideas ?!
-- Logu
Did you run consistency checks on the database before detaching it? You might just have a corrupt database,
and detach/attach won't clean such corruptions...
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Logu" <klogu@.scapevelocity.com> wrote in message news:uoN$ZBOMEHA.620@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I'm getting an Error 823, when I tried to attach a detached SQL DB
> [JPG file attached]
> Tried this KB http://support.microsoft.com/default...b;EN-US;828339
> But does not seem to work..
>
> Any Ideas ?!
> -- Logu
>
|||Hi Logu,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
I'm getting an Error 823, when I tried to attach a detached SQL DB
[JPG file attached]
Tried this KB http://support.microsoft.com/default...b;EN-US;828339
But does not seem to work..
Any Ideas ?!
-- Logu
Did you run consistency checks on the database before detaching it? You might just have a corrupt database,
and detach/attach won't clean such corruptions...
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Logu" <klogu@.scapevelocity.com> wrote in message news:uoN$ZBOMEHA.620@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I'm getting an Error 823, when I tried to attach a detached SQL DB
> [JPG file attached]
> Tried this KB http://support.microsoft.com/default...b;EN-US;828339
> But does not seem to work..
>
> Any Ideas ?!
> -- Logu
>
|||Hi Logu,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Error 823 : SQL Server
Hi
I'm getting an Error 823, when I tried to attach a detached SQL DB
[JPG file attached]
Tried this KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339
But does not seem to work..
Any Ideas ?!
-- LoguDid you run consistency checks on the database before detaching it? You might just have a corrupt database,
and detach/attach won't clean such corruptions...
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Logu" <klogu@.scapevelocity.com> wrote in message news:uoN$ZBOMEHA.620@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I'm getting an Error 823, when I tried to attach a detached SQL DB
> [JPG file attached]
> Tried this KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339
> But does not seem to work..
>
> Any Ideas ?!
> -- Logu
>|||Hi Logu,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
I'm getting an Error 823, when I tried to attach a detached SQL DB
[JPG file attached]
Tried this KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339
But does not seem to work..
Any Ideas ?!
-- LoguDid you run consistency checks on the database before detaching it? You might just have a corrupt database,
and detach/attach won't clean such corruptions...
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Logu" <klogu@.scapevelocity.com> wrote in message news:uoN$ZBOMEHA.620@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I'm getting an Error 823, when I tried to attach a detached SQL DB
> [JPG file attached]
> Tried this KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339
> But does not seem to work..
>
> Any Ideas ?!
> -- Logu
>|||Hi Logu,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Error 823 : SQL Server
Hi
I'm getting an Error 823, when I tried to attach a detached SQL DB
[JPG file attached]
Tried this KB http://support.microsoft.com/defaul...kb;EN-US;828339
But does not seem to work..
Any Ideas ?!
-- LoguDid you run consistency checks on the database before detaching it? You migh
t just have a corrupt database,
and detach/attach won't clean such corruptions...
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Logu" <klogu@.scapevelocity.com> wrote in message news:uoN$ZBOMEHA.620@.TK2MSFTNGP10.phx.gbl.
.
> Hi
>
> I'm getting an Error 823, when I tried to attach a detached SQL DB
> [JPG file attached]
> Tried this KB [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339[/ur
l]
> But does not seem to work..
>
> Any Ideas ?!
> -- Logu
>|||Hi Logu,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql
I'm getting an Error 823, when I tried to attach a detached SQL DB
[JPG file attached]
Tried this KB http://support.microsoft.com/defaul...kb;EN-US;828339
But does not seem to work..
Any Ideas ?!
-- LoguDid you run consistency checks on the database before detaching it? You migh
t just have a corrupt database,
and detach/attach won't clean such corruptions...
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Logu" <klogu@.scapevelocity.com> wrote in message news:uoN$ZBOMEHA.620@.TK2MSFTNGP10.phx.gbl.
.
> Hi
>
> I'm getting an Error 823, when I tried to attach a detached SQL DB
> [JPG file attached]
> Tried this KB [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339[/ur
l]
> But does not seem to work..
>
> Any Ideas ?!
> -- Logu
>|||Hi Logu,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql
Error 823
When I try to attach a database I get the SQL 823: I/O
error (torn page) detected during 0x000000000... in
the ... .mdf file.
I tried to attach it using single (i.e. .mdf file) but
still the same error.
Could anyone tell me how can I attach this database?
The only thing I know about is when you get a torn page detected, the best
you can do to resolve is restore from backups...
<anonymous@.discussions.microsoft.com> wrote in message
news:03c501c49791$493e0570$a401280a@.phx.gbl...
> When I try to attach a database I get the SQL 823: I/O
> error (torn page) detected during 0x000000000... in
> the ... .mdf file.
> I tried to attach it using single (i.e. .mdf file) but
> still the same error.
> Could anyone tell me how can I attach this database?
|||that's the problem... unfortunately we have a very old
backup.
>--Original Message--
>The only thing I know about is when you get a torn page
detected, the best
>you can do to resolve is restore from backups...
><anonymous@.discussions.microsoft.com> wrote in message
>news:03c501c49791$493e0570$a401280a@.phx.gbl...
>
>.
>
|||Open a case with MS. They *might* have some way to get the db back...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:03ab01c497bf$9e02ae20$a501280a@.phx.gbl...[vbcol=seagreen]
> that's the problem... unfortunately we have a very old
> backup.
> detected, the best
sql
error (torn page) detected during 0x000000000... in
the ... .mdf file.
I tried to attach it using single (i.e. .mdf file) but
still the same error.
Could anyone tell me how can I attach this database?
The only thing I know about is when you get a torn page detected, the best
you can do to resolve is restore from backups...
<anonymous@.discussions.microsoft.com> wrote in message
news:03c501c49791$493e0570$a401280a@.phx.gbl...
> When I try to attach a database I get the SQL 823: I/O
> error (torn page) detected during 0x000000000... in
> the ... .mdf file.
> I tried to attach it using single (i.e. .mdf file) but
> still the same error.
> Could anyone tell me how can I attach this database?
|||that's the problem... unfortunately we have a very old
backup.
>--Original Message--
>The only thing I know about is when you get a torn page
detected, the best
>you can do to resolve is restore from backups...
><anonymous@.discussions.microsoft.com> wrote in message
>news:03c501c49791$493e0570$a401280a@.phx.gbl...
>
>.
>
|||Open a case with MS. They *might* have some way to get the db back...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:03ab01c497bf$9e02ae20$a501280a@.phx.gbl...[vbcol=seagreen]
> that's the problem... unfortunately we have a very old
> backup.
> detected, the best
sql
Error 823
When I try to attach a database I get the SQL 823: I/O
error (torn page) detected during 0x000000000... in
the ... .mdf file.
I tried to attach it using single (i.e. .mdf file) but
still the same error.
Could anyone tell me how can I attach this database?The only thing I know about is when you get a torn page detected, the best
you can do to resolve is restore from backups...
<anonymous@.discussions.microsoft.com> wrote in message
news:03c501c49791$493e0570$a401280a@.phx.gbl...
> When I try to attach a database I get the SQL 823: I/O
> error (torn page) detected during 0x000000000... in
> the ... .mdf file.
> I tried to attach it using single (i.e. .mdf file) but
> still the same error.
> Could anyone tell me how can I attach this database?|||that's the problem... unfortunately we have a very old
backup.
>--Original Message--
>The only thing I know about is when you get a torn page
detected, the best
>you can do to resolve is restore from backups...
><anonymous@.discussions.microsoft.com> wrote in message
>news:03c501c49791$493e0570$a401280a@.phx.gbl...
>> When I try to attach a database I get the SQL 823: I/O
>> error (torn page) detected during 0x000000000... in
>> the ... .mdf file.
>> I tried to attach it using single (i.e. .mdf file) but
>> still the same error.
>> Could anyone tell me how can I attach this database?
>
>.
>|||Open a case with MS. They *might* have some way to get the db back...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:03ab01c497bf$9e02ae20$a501280a@.phx.gbl...
> that's the problem... unfortunately we have a very old
> backup.
>>--Original Message--
>>The only thing I know about is when you get a torn page
> detected, the best
>>you can do to resolve is restore from backups...
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:03c501c49791$493e0570$a401280a@.phx.gbl...
>> When I try to attach a database I get the SQL 823: I/O
>> error (torn page) detected during 0x000000000... in
>> the ... .mdf file.
>> I tried to attach it using single (i.e. .mdf file) but
>> still the same error.
>> Could anyone tell me how can I attach this database?
>>
>>.
error (torn page) detected during 0x000000000... in
the ... .mdf file.
I tried to attach it using single (i.e. .mdf file) but
still the same error.
Could anyone tell me how can I attach this database?The only thing I know about is when you get a torn page detected, the best
you can do to resolve is restore from backups...
<anonymous@.discussions.microsoft.com> wrote in message
news:03c501c49791$493e0570$a401280a@.phx.gbl...
> When I try to attach a database I get the SQL 823: I/O
> error (torn page) detected during 0x000000000... in
> the ... .mdf file.
> I tried to attach it using single (i.e. .mdf file) but
> still the same error.
> Could anyone tell me how can I attach this database?|||that's the problem... unfortunately we have a very old
backup.
>--Original Message--
>The only thing I know about is when you get a torn page
detected, the best
>you can do to resolve is restore from backups...
><anonymous@.discussions.microsoft.com> wrote in message
>news:03c501c49791$493e0570$a401280a@.phx.gbl...
>> When I try to attach a database I get the SQL 823: I/O
>> error (torn page) detected during 0x000000000... in
>> the ... .mdf file.
>> I tried to attach it using single (i.e. .mdf file) but
>> still the same error.
>> Could anyone tell me how can I attach this database?
>
>.
>|||Open a case with MS. They *might* have some way to get the db back...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:03ab01c497bf$9e02ae20$a501280a@.phx.gbl...
> that's the problem... unfortunately we have a very old
> backup.
>>--Original Message--
>>The only thing I know about is when you get a torn page
> detected, the best
>>you can do to resolve is restore from backups...
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:03c501c49791$493e0570$a401280a@.phx.gbl...
>> When I try to attach a database I get the SQL 823: I/O
>> error (torn page) detected during 0x000000000... in
>> the ... .mdf file.
>> I tried to attach it using single (i.e. .mdf file) but
>> still the same error.
>> Could anyone tell me how can I attach this database?
>>
>>.
Sunday, March 11, 2012
Error 5180: Could not open FCB for invalid file id : 120
'"Error 5180: Could not open FCB for invalid file id : 120"
When we attach the mdf and ldf files we get this error, what can we do to restore the database?
SEe this KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;815183 to fix.Error 5180: Could not open FCB for invalid file id : 120
'"Error 5180: Could not open FCB for invalid file id : 120"
When we attach the mdf and ldf files we get this error, what can we do to restore the database?
SEe this KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;815183 to fix.|||I de-tached and then attempted reattached a database from my server. I was not able to reattached, so I changed the name of the old database file by adding an X to the end. I then created a new database with the same name as the old one, deleted the new database file, and then renamed the old file by removing the X. Now when I try to open tables or veiws, I get error 5180: Could not open FCB... I followed the link to the suggestion about parallelism, ran the code, and still have the problem. I am running SQL Server 2000, but I don't know about the service packs.
Friday, March 9, 2012
Error 5123 Operating System Error 32 on attaching a database
I have SQL Server loaded on my standalone laptop. I tried
to attach a database( xxx.MDF) and got an error as above.
How do I resolve this ?In your sp_attach_db statement, make sure the path to the
mdf, ldf files is correct.
Also, make sure the SQL Server service account has
permissions to the path, files that you are attaching.
-Sue
On Wed, 25 Feb 2004 10:19:26 -0800, "Kishore Kapadia"
<kishore_kapadia@.peoplesoft.com> wrote:
>I have SQL Server loaded on my standalone laptop. I tried
>to attach a database( xxx.MDF) and got an error as above.
>How do I resolve this ?
to attach a database( xxx.MDF) and got an error as above.
How do I resolve this ?In your sp_attach_db statement, make sure the path to the
mdf, ldf files is correct.
Also, make sure the SQL Server service account has
permissions to the path, files that you are attaching.
-Sue
On Wed, 25 Feb 2004 10:19:26 -0800, "Kishore Kapadia"
<kishore_kapadia@.peoplesoft.com> wrote:
>I have SQL Server loaded on my standalone laptop. I tried
>to attach a database( xxx.MDF) and got an error as above.
>How do I resolve this ?
Error 5123 Cannot Attach DB
I'm getting Error 5123 "Cannot Open file because file is being used by
another process" when I try re-attaching a DB (.mdf) I had just detached.
I'm afraid I issued a DBCC command (which may be locking the file) right
before I detached the Database: DBCC OPENTRAN (oldoaiapp)
Does anyone know how I can identify and kill whatever process is attached to
my .mdf database? I obviously cannot use Query Analyzer as I cannot see the
database anymore.
Hi,
Probably your MDF or LDF file will be used by either Backup process or Anti
virus software. Verify both first and attach again.
If you still have issues then try to copy the files to a new folder and use
SP_ATTACH_DB to attach the database.
Incase if you still have issues then restart sql server service.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:B94C2796-66C4-4641-B3C5-5B7F597CC7B2@.microsoft.com...
> I'm getting Error 5123 "Cannot Open file because file is being used by
> another process" when I try re-attaching a DB (.mdf) I had just detached.
> I'm afraid I issued a DBCC command (which may be locking the file) right
> before I detached the Database: DBCC OPENTRAN (oldoaiapp)
> Does anyone know how I can identify and kill whatever process is attached
> to
> my .mdf database? I obviously cannot use Query Analyzer as I cannot see
> the
> database anymore.
>
|||Hari,
I cannot copy the files as I get the same lock error. I have determined it
is indeed SQL Server which is locking the files. I have rebooted the SQL
Server as well, and that does not clear the file locks. I'm afraid the DBCC
OpenTran (oldoaiapp) is what caused the lock.
I tried the Query Analyzer command below and am getting the error below (the
filename path IS correct):
EXEC sp_attach_db @.dbname = 'OldOAIApp',
@.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
@.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
Error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
"Hari Prasad" wrote:
> Hi,
> Probably your MDF or LDF file will be used by either Backup process or Anti
> virus software. Verify both first and attach again.
> If you still have issues then try to copy the files to a new folder and use
> SP_ATTACH_DB to attach the database.
> Incase if you still have issues then restart sql server service.
> Thanks
> Hari
> SQL Server MVP
|||Hi,
Can you stop MSSQL Server service and copy both files into a new folder.
Then rename the copied files to a different name
and try to attach the database.
Also verify the file names and directory you mention is correct.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
> Hari,
> I cannot copy the files as I get the same lock error. I have determined
> it
> is indeed SQL Server which is locking the files. I have rebooted the SQL
> Server as well, and that does not clear the file locks. I'm afraid the
> DBCC
> OpenTran (oldoaiapp) is what caused the lock.
> I tried the Query Analyzer command below and am getting the error below
> (the
> filename path IS correct):
> EXEC sp_attach_db @.dbname = 'OldOAIApp',
> @.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
> @.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
> Error:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
> "Hari Prasad" wrote:
>
|||Thanks Hari,
I will try the stopping of SQL Server and do the file move/renames after
hours. I think this will work and hopefully SQL Server won't choke on
restart!
Cheers,
Leslie
"Hari Prasad" wrote:
> Hi,
> Can you stop MSSQL Server service and copy both files into a new folder.
> Then rename the copied files to a different name
> and try to attach the database.
> Also verify the file names and directory you mention is correct.
> Thanks
> Hari
> SQL Server MVP
> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
> news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
>
>
another process" when I try re-attaching a DB (.mdf) I had just detached.
I'm afraid I issued a DBCC command (which may be locking the file) right
before I detached the Database: DBCC OPENTRAN (oldoaiapp)
Does anyone know how I can identify and kill whatever process is attached to
my .mdf database? I obviously cannot use Query Analyzer as I cannot see the
database anymore.
Hi,
Probably your MDF or LDF file will be used by either Backup process or Anti
virus software. Verify both first and attach again.
If you still have issues then try to copy the files to a new folder and use
SP_ATTACH_DB to attach the database.
Incase if you still have issues then restart sql server service.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:B94C2796-66C4-4641-B3C5-5B7F597CC7B2@.microsoft.com...
> I'm getting Error 5123 "Cannot Open file because file is being used by
> another process" when I try re-attaching a DB (.mdf) I had just detached.
> I'm afraid I issued a DBCC command (which may be locking the file) right
> before I detached the Database: DBCC OPENTRAN (oldoaiapp)
> Does anyone know how I can identify and kill whatever process is attached
> to
> my .mdf database? I obviously cannot use Query Analyzer as I cannot see
> the
> database anymore.
>
|||Hari,
I cannot copy the files as I get the same lock error. I have determined it
is indeed SQL Server which is locking the files. I have rebooted the SQL
Server as well, and that does not clear the file locks. I'm afraid the DBCC
OpenTran (oldoaiapp) is what caused the lock.
I tried the Query Analyzer command below and am getting the error below (the
filename path IS correct):
EXEC sp_attach_db @.dbname = 'OldOAIApp',
@.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
@.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
Error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
"Hari Prasad" wrote:
> Hi,
> Probably your MDF or LDF file will be used by either Backup process or Anti
> virus software. Verify both first and attach again.
> If you still have issues then try to copy the files to a new folder and use
> SP_ATTACH_DB to attach the database.
> Incase if you still have issues then restart sql server service.
> Thanks
> Hari
> SQL Server MVP
|||Hi,
Can you stop MSSQL Server service and copy both files into a new folder.
Then rename the copied files to a different name
and try to attach the database.
Also verify the file names and directory you mention is correct.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
> Hari,
> I cannot copy the files as I get the same lock error. I have determined
> it
> is indeed SQL Server which is locking the files. I have rebooted the SQL
> Server as well, and that does not clear the file locks. I'm afraid the
> DBCC
> OpenTran (oldoaiapp) is what caused the lock.
> I tried the Query Analyzer command below and am getting the error below
> (the
> filename path IS correct):
> EXEC sp_attach_db @.dbname = 'OldOAIApp',
> @.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
> @.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
> Error:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
> "Hari Prasad" wrote:
>
|||Thanks Hari,
I will try the stopping of SQL Server and do the file move/renames after
hours. I think this will work and hopefully SQL Server won't choke on
restart!
Cheers,
Leslie
"Hari Prasad" wrote:
> Hi,
> Can you stop MSSQL Server service and copy both files into a new folder.
> Then rename the copied files to a different name
> and try to attach the database.
> Also verify the file names and directory you mention is correct.
> Thanks
> Hari
> SQL Server MVP
> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
> news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
>
>
Error 5123 Cannot Attach DB
I'm getting Error 5123 "Cannot Open file because file is being used by
another process" when I try re-attaching a DB (.mdf) I had just detached.
I'm afraid I issued a DBCC command (which may be locking the file) right
before I detached the Database: DBCC OPENTRAN (oldoaiapp)
Does anyone know how I can identify and kill whatever process is attached to
my .mdf database? I obviously cannot use Query Analyzer as I cannot see the
database anymore.Hi,
Probably your MDF or LDF file will be used by either Backup process or Anti
virus software. Verify both first and attach again.
If you still have issues then try to copy the files to a new folder and use
SP_ATTACH_DB to attach the database.
Incase if you still have issues then restart sql server service.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:B94C2796-66C4-4641-B3C5-5B7F597CC7B2@.microsoft.com...
> I'm getting Error 5123 "Cannot Open file because file is being used by
> another process" when I try re-attaching a DB (.mdf) I had just detached.
> I'm afraid I issued a DBCC command (which may be locking the file) right
> before I detached the Database: DBCC OPENTRAN (oldoaiapp)
> Does anyone know how I can identify and kill whatever process is attached
> to
> my .mdf database? I obviously cannot use Query Analyzer as I cannot see
> the
> database anymore.
>|||Hari,
I cannot copy the files as I get the same lock error. I have determined it
is indeed SQL Server which is locking the files. I have rebooted the SQL
Server as well, and that does not clear the file locks. I'm afraid the DBCC
OpenTran (oldoaiapp) is what caused the lock.
I tried the Query Analyzer command below and am getting the error below (the
filename path IS correct):
EXEC sp_attach_db @.dbname = 'OldOAIApp',
@.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
@.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
Error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
"Hari Prasad" wrote:
> Hi,
> Probably your MDF or LDF file will be used by either Backup process or Ant
i
> virus software. Verify both first and attach again.
> If you still have issues then try to copy the files to a new folder and u
se
> SP_ATTACH_DB to attach the database.
> Incase if you still have issues then restart sql server service.
> Thanks
> Hari
> SQL Server MVP|||Hi,
Can you stop MSSQL Server service and copy both files into a new folder.
Then rename the copied files to a different name
and try to attach the database.
Also verify the file names and directory you mention is correct.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
> Hari,
> I cannot copy the files as I get the same lock error. I have determined
> it
> is indeed SQL Server which is locking the files. I have rebooted the SQL
> Server as well, and that does not clear the file locks. I'm afraid the
> DBCC
> OpenTran (oldoaiapp) is what caused the lock.
> I tried the Query Analyzer command below and am getting the error below
> (the
> filename path IS correct):
> EXEC sp_attach_db @.dbname = 'OldOAIApp',
> @.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
> @.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
> Error:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
> "Hari Prasad" wrote:
>
>|||Thanks Hari,
I will try the stopping of SQL Server and do the file move/renames after
hours. I think this will work and hopefully SQL Server won't choke on
restart!
Cheers,
Leslie
"Hari Prasad" wrote:
> Hi,
> Can you stop MSSQL Server service and copy both files into a new folder.
> Then rename the copied files to a different name
> and try to attach the database.
> Also verify the file names and directory you mention is correct.
> Thanks
> Hari
> SQL Server MVP
> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
> news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
>
>
another process" when I try re-attaching a DB (.mdf) I had just detached.
I'm afraid I issued a DBCC command (which may be locking the file) right
before I detached the Database: DBCC OPENTRAN (oldoaiapp)
Does anyone know how I can identify and kill whatever process is attached to
my .mdf database? I obviously cannot use Query Analyzer as I cannot see the
database anymore.Hi,
Probably your MDF or LDF file will be used by either Backup process or Anti
virus software. Verify both first and attach again.
If you still have issues then try to copy the files to a new folder and use
SP_ATTACH_DB to attach the database.
Incase if you still have issues then restart sql server service.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:B94C2796-66C4-4641-B3C5-5B7F597CC7B2@.microsoft.com...
> I'm getting Error 5123 "Cannot Open file because file is being used by
> another process" when I try re-attaching a DB (.mdf) I had just detached.
> I'm afraid I issued a DBCC command (which may be locking the file) right
> before I detached the Database: DBCC OPENTRAN (oldoaiapp)
> Does anyone know how I can identify and kill whatever process is attached
> to
> my .mdf database? I obviously cannot use Query Analyzer as I cannot see
> the
> database anymore.
>|||Hari,
I cannot copy the files as I get the same lock error. I have determined it
is indeed SQL Server which is locking the files. I have rebooted the SQL
Server as well, and that does not clear the file locks. I'm afraid the DBCC
OpenTran (oldoaiapp) is what caused the lock.
I tried the Query Analyzer command below and am getting the error below (the
filename path IS correct):
EXEC sp_attach_db @.dbname = 'OldOAIApp',
@.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
@.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
Error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
"Hari Prasad" wrote:
> Hi,
> Probably your MDF or LDF file will be used by either Backup process or Ant
i
> virus software. Verify both first and attach again.
> If you still have issues then try to copy the files to a new folder and u
se
> SP_ATTACH_DB to attach the database.
> Incase if you still have issues then restart sql server service.
> Thanks
> Hari
> SQL Server MVP|||Hi,
Can you stop MSSQL Server service and copy both files into a new folder.
Then rename the copied files to a different name
and try to attach the database.
Also verify the file names and directory you mention is correct.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
> Hari,
> I cannot copy the files as I get the same lock error. I have determined
> it
> is indeed SQL Server which is locking the files. I have rebooted the SQL
> Server as well, and that does not clear the file locks. I'm afraid the
> DBCC
> OpenTran (oldoaiapp) is what caused the lock.
> I tried the Query Analyzer command below and am getting the error below
> (the
> filename path IS correct):
> EXEC sp_attach_db @.dbname = 'OldOAIApp',
> @.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
> @.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
> Error:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
> "Hari Prasad" wrote:
>
>|||Thanks Hari,
I will try the stopping of SQL Server and do the file move/renames after
hours. I think this will work and hopefully SQL Server won't choke on
restart!
Cheers,
Leslie
"Hari Prasad" wrote:
> Hi,
> Can you stop MSSQL Server service and copy both files into a new folder.
> Then rename the copied files to a different name
> and try to attach the database.
> Also verify the file names and directory you mention is correct.
> Thanks
> Hari
> SQL Server MVP
> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
> news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
>
>
ERROR 5120
I tried attach/detach approach to migrate sql server 2000 databases to sql 2005. I installed a fresh copy of sql server 2005 on windows 2003 server. I could move most of my databases except one. While attaching the database to the sql 2005 I got the following error:
Unable to oen "c:\program files\microsoftSQL server \MSSQL.1\MSSQL\DATA\mydb_log.ldf". operating system error 32:" 32(the process cannot access the file because it is being used by another process)". microsoft SQL server, error:5120)
You must be attaching the wrong file (one being used by another database).|||I have a similar problem with the same error:
1) Detached MyDb.mdf (and .ldf) (Auto close = True)
2) copied MyDb.mdf and MyDb.ldf to a different folder
3) Re-attached MyDb from original location
4) Renamed copy to MyDbX.mdf and MyDbX.ldf
5) Attach copy (MyDbX.mdf), and in attach dialog change "Attach As" column to MyDbX
= Error 5120
Problem was the rename at step 4. Without this step the attach works OK, with step 5 renaming the database.
Without going back to check, I think step 4 was ok in SQL 2000 EM.
Error 500 Takes it's toll
The Error:
An attempt to attach an auto-named database for file C:\Users\CodeFreak\Documents\Visual Studio 2005\WebSites\PersonalSite\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I've tried allowing permissions I'VE EVEN TRIED ALLOWING EVERYONE! I've tried right clicking the datasource under the datasource explorer and clicking 'detatch.' It works Locally but fails when i pusblish with IIS. Whats with these SQL databases that just dont want to work with IIS 7 ?
Published with: IIS 7
I've tried removing the "user instance."
This is User roles and all that which uses SQL Server 2005.
So my question is, How do i publish a website with SQL Server 2005 database? 
Please give it to me step by step as im still quite a newbie.
Thanks in advance
Does the Account the Web Service is running with have the appropiate permissions to access the file ? Did you made sure that once the database is opened, the connection is reused at the second time the database is accessed ? Do you have the autoclose property set on or off ?Jens K. Suessmeyer.
http://www.sqlserver2008.de
|||It was automatically set up, I checked the permissions in both NTFS and the website's permissions they all checked out fine|||
Auto CLose?
Can you elaborate?
I even tried Microsoft's template which failed aswell
I just don't know how to publish a web page after i click and drag a login for and use the administer options to create accounts and set permissions. Remember I'm still a newbie when it comes to aspx, VWD and IIS. I've only used HTML 2 weeks ago (ps. I'm a fast learner). So I would perfer step by step instructions. It works fine during debug but falls over on publish (yes i closed VWD).
|||Problem solved. It turns out I put it in the same directory as the website.
Moving it (and modifying the connection string accordingly works)
Old connectiong String (From web.config)
data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|datadirectory|aspnetdb.mdf;User Instance=true
Modified Connection String -works (after moving the database files of course)
data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=C:\Mydatabase\aspnetdb.mdf;User Instance=true
Soo easy, I couldn't believe I missed it!
Subscribe to:
Posts (Atom)