SQL database question

[hereigns]

I realize this was a bonehead move but am hoping someone might be able to offer some insight.

Running SQL 2K on W2K Server. I configured a backup of the database via SQL Manager and wasn't paying attention when I told it to create an append backup to the existing database so naturally the size of the database is now double the size of what it should be...any ideas on how I can remove this backup within the database itself?

 

[jbenson001]

I am confused, how did you specify to backup to the DB itself? As far as I know, you backup to a file or backup device.

Jim

 

[SQLBill]

Jim is right, and it sounds like you did an IMPORT or something similar.

We don't know enough about your data to help you out at this time. Were there other inserts at the same time? If not, and if you have a primary key such as ROWID, you could figure out which rows are the dupes and delete them.

-SQLBill

Posting advice: FAQ481-4875

 

[hereigns]

Ok, let me try and clarify.

Within SQL Enterprise Mngr I went to the SQL Server Backup utility, which allows you to select the database + name + destination. I accidently chose the same database name + location "c:\databases\sql_databasename" as the destination.
I can visually see the size of the database has doubled, went from 1GB to 2GB.

Does that help at all?

 

[icemel]

do the backup again, choose overwrite option

i think that will do it?

the database backup (not the database) doubled in size because you appended it

 

[SQLBill]

Icemel,

That won't work. The poster's database is already 'corrupted' and is 2 GB. If they back it up again, they are just backing up the 'bad' version.

Hereigns,

Unless you have a good backup, you are pretty much out of luck. You will have to figure out a way to 'recognize' the duplicate data and delete it from the database.

Since, we don't know what your schema looks like we can't help you with it.

Some thoughts..........and you should do a GOOD backup before you try these.

You could check for DISTINCT rows HAVING COUNT(*) > 1 and delete all but one.

You could create a new database and SELECT INTO the new database using DISTINCT and HAVING COUNT(*) > 1

Check for some column that can be used to determine duplicate rows.

-SQLBill

Posting advice: FAQ481-4875

 

[hereigns]

SQLBill,
I agree, the database is already corrupted. I will try your suggestion and report the results.

Thanks for your insight!