how to drop a suspect database with MS/SQL

Posted on 2005-04-17
Last Modified: 2012-06-27
I have a database marked as suspect in MS/SQL.
The reason is simple : the data files are not there anymore.
In fact I had to restart MS/SQL after copying back all the ldf and mdf files that were savec when MS/SQL was down, but one extremely large database used for test I didn't copy back as I don't need it anymore (I need it empty but with no data).
But now MS/SQL of course says it is market as suspect which is normal.
How can I definitively drop it then create it back from scratch ?
I do not want (too long) to copy back the corresponding ldf and mdf files which of course is one solution, but too long for me.
Question by:LeTay
    LVL 17

    Accepted Solution

    execute script


    From BOL
    To use DROP DATABASE, the database context of the connection must be in the master database.

    DROP DATABASE removes damaged databases marked as suspect and removes the specified database. Before dropping a database used in replication, first remove replication. Any database published for transactional replication, or published or subscribed to merge replication cannot be dropped. For more information, see Administering and Monitoring Replication. If a database is damaged and replication cannot first be removed, in most cases you still can drop the database by marking it as an offline database.

    A dropped database can be re-created only by restoring a backup. You cannot drop a database currently in use (open for reading or writing by any user). When a database is dropped, the master database should be backed up.

    System databases (msdb, master, model, tempdb) cannot be dropped.

    LVL 17

    Expert Comment

    If You haven't got script for this DB generate it before dropping !!
    in EM Tools|Generate SQL Script..
    select all options except only script 7.0 compatible..

    Author Comment

    Indeed scripting from ISQL works !
    I was trying from the SQL Server Enterprise Manager which looks buggy ? (MS/SQL 7.0 service pack 1)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now