June 14, 2011 at 1:17 am
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
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
just confirm the actual data files exsist and attach them back.
June 14, 2011 at 2:44 am
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
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:36 am
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
Thanks to all for your kind response.
June 14, 2011 at 7:02 am
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
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
June 15, 2011 at 1:02 am
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
June 15, 2011 at 2:58 am
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
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply