locked
Database deleted please need help RRS feed

  • Question

  • Hi All,

    Over the weekend somehow the dev database got deleted. can someone please suggest me where to look and how it got deleted?

    Thanks

    Monday, October 22, 2018 1:25 PM

Answers

  • If you do not have  last good backup I think you are out of luck... Do you have perhaps the OS Backup?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 23, 2018 4:34 AM
  • Are the files still there ? <YOURDBDRIVE>:\Program Files\Microsoft SQL Server\MSSQL<YOURSQLVERSION>\MSSQL\data\<YOURDB>.mdf or look at other mdf in the same instance to see where they are located and see if you can find yours. 

    Regards

    Philippe

    https://blog.meestart.com

    • Marked as answer by LisaKruger Thursday, October 25, 2018 2:18 PM
    Tuesday, October 23, 2018 4:49 AM
  • Hi LisaKruger,

     

    >>In the hisotry when i see DROP does that mean its DROP of the whole database or just an object in the database?

     

    The "DROP" record in the result of your above command means the drop objects operation not just database.

     

    >>can someone please suggest me where to look and how it got deleted?

     

    You can also depend on some third party tools to analyze logs directly. For example: ApexSQL.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by LisaKruger Thursday, October 25, 2018 2:17 PM
    Tuesday, October 23, 2018 6:21 AM

All replies

  • Hello,

    With some luck you can get it from Default trace: In SSMS right-mouse click on Server node => Reports => Standard Reports => Schema Changes History


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 22, 2018 1:29 PM
    Answerer
  • Thanks Olaf,

    Not much there :-(

    Monday, October 22, 2018 2:21 PM
  • Hello, 

    Take your scripts and create new database.

    As second task - create backup schedule.


    Sincerely, Highly skilled coding monkey.

    Monday, October 22, 2018 2:28 PM
  • It was deleted by a human.

    There should be an entry in the SQL Server log file showing when it was done, but may not show who did it depending on how your security is setup.

    Monday, October 22, 2018 2:30 PM
    Answerer
  • Thanks Tom,

    I looked at all the logs, including the windows application log. seems like a patch was applied and after that rest of the databases came up except for the one . doe s that happen?

    i executed the below sql and it gives only starting 21st and the database was deleted on the 20th. Any idea as to how to go back past 21st?

    declare @isenabled int
    select top 1 @isenabled = convert(int ,value_in_use) from sys.configurations where name = 'default trace enabled'
    if @isenabled = 1
    begin
    declare @datepara1 datetime
    declare @intpara1 int;declare @intpara2 int ;
    declare @currenttrace varchar(500);
    declare @basetrace varchar(500);
    declare @tracetabletemporary table (c1_objectname nvarchar(256) collate database_default , c2_databasename nvarchar(256) collate database_default , c3_starttime datetime , c4_eventclass int , c5_eventsubclass int , c6_objecttype int , c7_servername nvarchar(256) collate database_default , c8_loginname nvarchar(256) collate database_default , c9_applicationname nvarchar(256) collate database_default ,c10_ddloperation nvarchar(40) collate database_default);
    select @currenttrace = path from sys.traces where is_default = 1 ; set @currenttrace = reverse(@currenttrace)
    select @intpara2 = PATINDEX('%\%' , @currenttrace); set @currenttrace = reverse(@currenttrace)
    set @basetrace = LEFT( @currenttrace ,len(@currenttrace) - @intpara2) + '\log.trc';
    insert into @tracetabletemporary select ObjectName ,DatabaseName ,StartTime ,EventClass ,EventSubClass ,ObjectType ,ServerName ,LoginName ,ApplicationName ,'temp'
    from ::fn_trace_gettable( @basetrace , default ) where EventClass in (46 ,47 ,164) and EventSubclass = 0 and DatabaseID <> 2 
    update @tracetabletemporary set c10_ddloperation = 'DROP' where c4_eventclass = 47
    delete from @tracetabletemporary where c10_ddloperation <> 'DROP'
    select @datepara1 = min(c3_starttime) from @tracetabletemporary
    set @intpara1= datediff(hh ,@datepara1 ,getdate())
    set @intpara1=@intpara1/24;
    select c3_starttime as c1_LogTime ,c2_databasename ,c7_servername ,c8_loginname ,c9_applicationname ,c10_ddloperation from @tracetabletemporary where c6_objecttype not in (21587) order by c3_starttime desc
    end

    • Edited by LisaKruger Monday, October 22, 2018 3:19 PM
    Monday, October 22, 2018 3:00 PM
  • In the hisotry when i see DROP does that mean its DROP of the whole database or just an object in the database?

    Thanks

    Monday, October 22, 2018 3:59 PM
  • If you do not have  last good backup I think you are out of luck... Do you have perhaps the OS Backup?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 23, 2018 4:34 AM
  • Are the files still there ? <YOURDBDRIVE>:\Program Files\Microsoft SQL Server\MSSQL<YOURSQLVERSION>\MSSQL\data\<YOURDB>.mdf or look at other mdf in the same instance to see where they are located and see if you can find yours. 

    Regards

    Philippe

    https://blog.meestart.com

    • Marked as answer by LisaKruger Thursday, October 25, 2018 2:18 PM
    Tuesday, October 23, 2018 4:49 AM
  • Hi LisaKruger,

     

    >>In the hisotry when i see DROP does that mean its DROP of the whole database or just an object in the database?

     

    The "DROP" record in the result of your above command means the drop objects operation not just database.

     

    >>can someone please suggest me where to look and how it got deleted?

     

    You can also depend on some third party tools to analyze logs directly. For example: ApexSQL.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by LisaKruger Thursday, October 25, 2018 2:17 PM
    Tuesday, October 23, 2018 6:21 AM