Post reply

Missing Database

  • June 14, 2011 at 1:17 am

    #252822

    Hi All,

    I came across the situation that a developer complaining that the database is missing. I checked the sql server logs and defaul trace managed by SQL Server in a log file show the existance of the database. Now the situation is it is not showing in SSMS i also checked sys.databases and sys.master_files nothing found there. How to find the cause for missing the db or how to get back the DB.

    Thanks in advance

  • June 14, 2011 at 1:26 am

    #1338189

    Did you look in the data and log directory to see if the data and log files are there?

    -------------------------------------------------
    Will C,
    MCITP 2008 Database Admin, Developer

  • June 14, 2011 at 2:13 am

    #1338197

    just confirm the actual data files exsist and attach them back.

  • June 14, 2011 at 2:43 am

    #1338212

    @ricer & @arun_sql: There is no files for the Database but thing is that the person was using it and after 5 min of time login failed for the database. The default trace show the entries for the same.

  • June 14, 2011 at 2:44 am

    #1338213

    If the database is showing up when u query sys.databases its not dettached. But you can still try.

    Try to see if the database is available by querying for objects in it , you could try running dbcheck as well.

  • June 14, 2011 at 2:47 am

    #1338215

    try select * from sys,sysfiles under the db your missing , if you think its in a different loaction. Else if u cant the see the file , the db mdf and ldf has been deleted ie db dropped

  • June 14, 2011 at 3:01 am

    #1338226

    @jayanth KURUP : If the db is droped then it would be captured in the default trace ryt. Also as u said under same db exec select * from sys.sysfiles but DB is not showing and not available how will i get the result.::w00t:

  • June 14, 2011 at 3:36 am

    #1338236

    Give other users Readonly rights and then try to take the full backup and then restore it with different name after restore is complete then drop the previous one and then rename the new one with old name and then give DML rights to other users

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • June 14, 2011 at 6:45 am

    #1338320

    Thanks to all for your kind response.

  • June 14, 2011 at 7:02 am

    #1338339

    ur right that the trace should contain details of the database being dropped.

    However since your trace log files do not show the details i cant explain y ?

    Since your not able to connect to the db or query sys.sysfiles i guess your only left with the option of restoring from backup. The fact that you dont have records in sys.database , sysfiles or even the physical file on the OS makes it clear the db has been dropped , why it was not captured in the trace I can only suggest after looking at it.

    Hope this helped.

  • June 14, 2011 at 7:02 am

    #1338340

    Syed Jahanzaib Bin hassan (6/14/2011)

    Give other users Readonly rights

    ...and then try to take the full backup

    ...and then restore it with different name

    ...after restore is complete then drop the previous one

    ...and then rename the new one with old name

    ...and then give DML rights to other users

    why? what purpose would that serve? I'm reading this as a lot of extra steps that don't really do anything...can you explain how this helps the original poster?

    if you take the users rights way...give them another database...then give them the rights back....why remove the rights in the first place?

    if you are going to restore the database, drop it, and rename a restore to be the same, why not just restore the database over the exisitng, and skip the extra steps?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 15, 2011 at 1:02 am

    #1338783

    I have the same problem. i have around 40-50 databases in my system. Some of them just disappears. Here there is no log anywhere of any deletion. Sometimes they just get detached. so i am able to attach them, but sometimes even the data file and log file are deleted . So i have to restore from the previous day backup. Since the databases are from live project, i am facing issues with data discrepancy.

    Appreciate any help.

    Thanks

  • June 15, 2011 at 1:13 am

    #1338785

    This is an administration issue , you could enable a server trigger to audit the use of DDL statements. also revoke access to all users who have no reason to connect to the db .

    Jayanth Kurup[/url]

  • June 15, 2011 at 2:58 am

    #1338819

    You should review the SQL Server error log for date/time when this is happening and as explained you might review the number of users with SYSADMIN privileges.

    -Satya SK Jayanty
    SQL Server MVP (Follow me @sqlmaster)
    Author of SQL Server 2008 R2 Administration CookBook
    SQL Server Knowledge Sharing network

  • June 15, 2011 at 4:46 am

    #1338867

    Satya_skj (6/15/2011)

    You should review the SQL Server error log for date/time when this is happening and as explained you might review the number of users with SYSADMIN privileges.

    actually, a user in the db_owner role of a specific database has permission to drop that specific database , so it might not be limited to just sysadmins;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply