Error Violation of Primary Key While Attaching Database

Hello all,
I have been trying to attach a database but it fails because of
duplicate rows (Error 2627).
I searched in the Internet for removing duplicate records but it can
only work if I manage to attach the database first (correct me if I'm
wrong). So without having the database attached to the SQL server, I
will not manage to remove the duplicate rows. But when I tried to
attach, it checks for duplicate records automatically and fail to
attach.
Is there any workaround for my case?
Thanks in advance.
Regards,
Garreth GohBTW, it is SQL 2000 with SP3.
Regards,
Garreth Goh
On Dec 18, 10:02 am, gohtiamt...@.gmail.com wrote:
> Hello all,
> I have been trying to attach a database but it fails because of
> duplicate rows (Error 2627).
> I searched in the Internet for removing duplicate records but it can
> only work if I manage to attach the database first (correct me if I'm
> wrong). So without having the database attached to the SQL server, I
> will not manage to remove the duplicate rows. But when I tried to
> attach, it checks for duplicate records automatically and fail to
> attach.
> Is there any workaround for my case?
> Thanks in advance.
> Regards,
> Garreth Goh|||Hi,
More updates after several attempts (with separate SQL server 2000
SP4).
01. I move the MDF and LDF files to another folders. I have two MDF
and two LDF files for one database.
02. I attach the same but blank backup database to my SQL server with
a new database name.
03. Back it up.
04. Restore the backup with the original database name in the initial
folder where the corrupted files were.
05. Detach the new restored (but empty) database.
06. Move the new created MDF and LDF files to another folder.
07. Copy the original corrupted MDF and LDF files back to the original
folder.
08. Attach the database. Got "Suspect" message.
09. Update "sysdatabases" to set the database into Emergency Mode.
10. Attach the database back. I got Emergency Mode.
11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
"REPAIR_ALLOW_DATA_LOSS".
12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
it tells me to have Single-User mode when trying to run "DBCC
CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
database 'MyDB' because the database is in bypass recovery mode."
I am stuck here. Could anyone here help, please?
I may have incorrectly listed down the sequence from point 8 to 11 but
my current status is at point 13.
Regards,
Garreth Goh
On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
> BTW, it is SQL 2000 with SP3.
> Regards,
> Garreth Goh|||The error pretty much speak for itself. My guess is that you have to try to export the data to a
clean database, using some combination of script objects (unless you have DDL files to re-create
them) and for the data SELECT INTO., INSERT SELECT, BCP etc.
I would bring in an expert if above doesn't feel comfortable to you...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<gohtiamtjai@.gmail.com> wrote in message
news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
> Hi,
> More updates after several attempts (with separate SQL server 2000
> SP4).
> 01. I move the MDF and LDF files to another folders. I have two MDF
> and two LDF files for one database.
> 02. I attach the same but blank backup database to my SQL server with
> a new database name.
> 03. Back it up.
> 04. Restore the backup with the original database name in the initial
> folder where the corrupted files were.
> 05. Detach the new restored (but empty) database.
> 06. Move the new created MDF and LDF files to another folder.
> 07. Copy the original corrupted MDF and LDF files back to the original
> folder.
> 08. Attach the database. Got "Suspect" message.
> 09. Update "sysdatabases" to set the database into Emergency Mode.
> 10. Attach the database back. I got Emergency Mode.
> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
> "REPAIR_ALLOW_DATA_LOSS".
> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
> it tells me to have Single-User mode when trying to run "DBCC
> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
> database 'MyDB' because the database is in bypass recovery mode."
> I am stuck here. Could anyone here help, please?
> I may have incorrectly listed down the sequence from point 8 to 11 but
> my current status is at point 13.
>
> Regards,
> Garreth Goh
>
> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
>> BTW, it is SQL 2000 with SP3.
>> Regards,
>> Garreth Goh|||Or get Microsoft Technical Support on the phone.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:261F6FD2-B073-42F2-A218-B3B6AFE3750D@.microsoft.com...
> The error pretty much speak for itself. My guess is that you have to try
> to export the data to a clean database, using some combination of script
> objects (unless you have DDL files to re-create them) and for the data
> SELECT INTO., INSERT SELECT, BCP etc.
> I would bring in an expert if above doesn't feel comfortable to you...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <gohtiamtjai@.gmail.com> wrote in message
> news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
>> Hi,
>> More updates after several attempts (with separate SQL server 2000
>> SP4).
>> 01. I move the MDF and LDF files to another folders. I have two MDF
>> and two LDF files for one database.
>> 02. I attach the same but blank backup database to my SQL server with
>> a new database name.
>> 03. Back it up.
>> 04. Restore the backup with the original database name in the initial
>> folder where the corrupted files were.
>> 05. Detach the new restored (but empty) database.
>> 06. Move the new created MDF and LDF files to another folder.
>> 07. Copy the original corrupted MDF and LDF files back to the original
>> folder.
>> 08. Attach the database. Got "Suspect" message.
>> 09. Update "sysdatabases" to set the database into Emergency Mode.
>> 10. Attach the database back. I got Emergency Mode.
>> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
>> "REPAIR_ALLOW_DATA_LOSS".
>> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
>> it tells me to have Single-User mode when trying to run "DBCC
>> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
>> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
>> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
>> database 'MyDB' because the database is in bypass recovery mode."
>> I am stuck here. Could anyone here help, please?
>> I may have incorrectly listed down the sequence from point 8 to 11 but
>> my current status is at point 13.
>>
>> Regards,
>> Garreth Goh
>>
>> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
>> BTW, it is SQL 2000 with SP3.
>> Regards,
>> Garreth Goh
>