Deleting and recreating database with same name

Question...
I created a database, but I want to totally blow that database away
and recreate it. I right-clicked on the DB and deleted it, no longer
there. Now when I go to recreate the database with the same name, I
get the following error:
Error 5170: Cannot create file 'C:\...MDF' because it already exists.
CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
Okay, when I tell SQL I want to delete a database, I want it 100%
deleted. Apparently SQL's idea of Delete and mine are different, so
any suggestions on how I can totally remove this database? Since MS
is known to do plenty hidden the background, I don't want to just
delete the .MDF file, since I'm sure this will confuse SQL quite a
bit.
So back to the root question... how do I 100% delete a database where
I can create another DB with the same name?
Thanks,
Alex.You'll need to delete the MDF file it's complaining about from the OS.
HTH
Ryan Waight, MCDBA, MCSE
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.|||> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted.
This is what DROP DATABASE does, which is the command called from QA and EM. Unless you detach it,
of course.
> Apparently SQL's idea of Delete and mine are different
No, something strange must have happened.
Just try to delete the database files. If the database still exists in the eyes of SQL Server, then
the files are locked and you can't delete them.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.

deleted mdf file want to restore from logfile?

I have deleted my mdf file by accident en need to restore the db from a logfile
the size of the logfile is 54gb.
I am totally desparate this was critical data, I tried to get the data from
the subscription server but this file is totally messed up , so I bought a
app to recover the data but it only managed bits and pieces.
My only hope now is to restore the data from the logfile but I have no idea
how or where to start. Would appriciate any helpDo you have full backup and transaction files up to the time that the
mdf was deleted or just the ldf? If not, I'm afraid you're going to
be out of luck.
On Sep 25, 9:00 am, jules <ju...@.discussions.microsoft.com> wrote:
> I have deleted my mdf file by accident en need to restore the db from a logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no idea
> how or where to start. Would appriciate any help|||In article <0A70FE38-9F46-4EBC-BFE3-9ABA3D9D1985@.microsoft.com>,
jules@.discussions.microsoft.com says...
> I have deleted my mdf file by accident en need to restore the db from a logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no idea
> how or where to start. Would appriciate any help
>
Might investigate Lumigent Logreader -- can restore data from a log
file. There may be other products as well
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||Get in touch with Brian Lockwood at ApexSQL. They have done this type of
disaster recovery before, although I am not sure recovery is possible
without the database file as well as the log file. apexsql.com
"jules" <jules@.discussions.microsoft.com> wrote in message
news:0A70FE38-9F46-4EBC-BFE3-9ABA3D9D1985@.microsoft.com...
>I have deleted my mdf file by accident en need to restore the db from a
>logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data
> from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no
> idea
> how or where to start. Would appriciate any help

Delete Multiple Records from Multiple Tables

What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)

delete dbo.tblcase

where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseclient

where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseinformation

where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaselawyer

where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseprosecutor

where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')The simplest method is to establish relationships between your case table and the child tables, and define those relationships to have cascading deletes.
Then just delete from your case table and let SQL Server handle the rest.