SQL 2014 Table Truncated Inadvertently

  • Lippy

    SSC Eights!

    Points: 890

    One of our users has created a production database and subsequently inadvertently truncated a table. This would not be an issue had he involved his support team so that the new DB could have been included in the backups. As it is it was not so we have a database and a somewhat large log file. Can we roll back to a point in time? I have tried using the point in time function in restore but this is not working.

  • Phil Parkin

    SSC Guru

    Points: 244739

    Do you have any backups of this database?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeff Moden

    SSC Guru

    Points: 997150

    First of all (and you probably all ready know this but I have to say it out loud), fix the main problem that started all of this... stop giving users privs enough to create their own databases and start proactively monitoring for when someone does create a new database even if they're not just a "user".

    Shifting gears to the problem at hand, I believe that Glen Barry has an article about how to retrieve data from a log file and you can Google for that but... if you never took a backup of the file and as large as the log file may have grown at one time, the database wasn't so much in the FULL recovery model and you may not have anything to recover from the log no matter how big it has grown physically.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher

    SSC Guru

    Points: 98559

    For new db(s), the support team can "notify itself".  It's easy enough to check every day to see if a new db has been created, whether a user tells you of it or not.  If you're going to allow users to create dbs, you should automatically take the steps necessary to back up such (a) db(s).

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Grant Fritchey

    SSC Guru

    Points: 396714

    Total agreement with everyone else that you should be spotting when new databases are created. However, it's entirely possible to create a backup script (or use Minion or Ola Hollengren's scripts) to backup all the databases on a server, without have to list them all and know what they are ahead of time. Until you get the fixes in place that have been suggested, I'd do that.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 997150

    Grant Fritchey wrote:

    Total agreement with everyone else that you should be spotting when new databases are created. However, it's entirely possible to create a backup script (or use Minion or Ola Hollengren's scripts) to backup all the databases on a server, without have to list them all and know what they are ahead of time. Until you get the fixes in place that have been suggested, I'd do that.

    As "Yogi" Berra has been quoted, "When you come to a fork in the road, take it".  In other words, do both (actually, there are 4 things) and there's absolutely no good reason not to.

    The script can and should be made to...

    1. Automatically backup new databases
    2. Do T-Log backups based on the Recovery Model of each database
    3. Auto-magically send an email of what completed and when on a daily basis to be received as a "Morning Report"
    4. A part of the report should include either a special section any new databases or a highlighted "Created On" date column that appeared in the last week.

    Then, remove all privs that allow users to create, drop, and otherwise modify database settings, configurations, and privs.  You've already got the justification to do that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • david.edwards 76768

    Ten Centuries

    Points: 1070

    At the risk of going slightly off topic, can I ask the gurus' opinions of DDL triggers?

    In my infancy as accidental, lone DBA - we were undergoing a large period of change. External third parties were connecting in and "doing stuff" frequently. I wanted a method to track certain things that they had done.

    Are they best avoided or did my rather naive script below have any merit? Would a DDL trigger for CREATE DATABASE be best avoided as a basis notifying/auditing the OP's situation?

    CREATE TRIGGER [DDLAudit]
    ON ALL SERVER
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ALTER_PROCEDURE, ALTER_DATABASE
    AS

    BEGIN
    SET NOCOUNT ON;
    DECLARE
    @EventData XML = EVENTDATA();

    DECLARE
    @ip VARCHAR(32) =
    (
    SELECT client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID
    );

    INSERT INTO AuditDB.dbo.DDLEvents
    (
    EventType,
    EventDDL,
    EventXML,
    DatabaseName,
    SchemaName,
    ObjectName,
    HostName,
    IPAddress,
    ProgramName,
    LoginName,
    ServerName
    )
    SELECT
    @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
    @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
    @EventData,
    @Eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(200)'),
    --DB_NAME(),
    @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
    @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
    HOST_NAME(),
    @ip,
    PROGRAM_NAME(),
    SUSER_SNAME(),
    @Eventdata.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(200)');
    END

     

  • ScottPletcher

    SSC Guru

    Points: 98559

    DDL triggers are a great option.  I use them in nearly every db to prevent actions we don't want taken.  As examples, in some dbs we don't want objects created except by clearance with everyone (including master and msdb); in nearly all dbs we don't want tables created that are named 'sys%' or 'tbl%' (just our rule); in some utility dbs, only views can be freely created, not other types of objects; etc..

    The key is to write the trigger as efficiently as possible, even at the cost of clarity / ease of understanding.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden

    SSC Guru

    Points: 997150

    I see nothing wrong with such a DDL trigger to detect when a new database was created... but I wouldn't rely on it to make sure backups are performed.  The backup code should auto-magically take the addition of databases is stride just in case no one was paying attention to the results of DDL triggr output.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Grant Fritchey

    SSC Guru

    Points: 396714

    Jeff Moden wrote:

    I see nothing wrong with such a DDL trigger to detect when a new database was created... but I wouldn't rely on it to make sure backups are performed.  The backup code should auto-magically take the addition of databases is stride just in case no one was paying attention to the results of DDL triggr output.

    Very much this.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Lippy

    SSC Eights!

    Points: 890

    sorry got picked up by another problem and work part-time so just picking up the comments.

    So thank you all who responded.

    No, there was no backup - the DBAs were not told that the server had been taken into production. We had Hallengren's scripts ready but had not implemented them. We normally have an install phase followed by testing and then a handover to production.

    The users had been given excessive privileges by a superuser who "needed them to build databases" with the approval of the server and service owner. The superuser apparently was tryong to save the DBAs some time and effort.

    There was a mixture of Full and Simple Recovery Models without logic as to which was which. Turns out the one that lost data was in Simple recovery so I could do nothing.

    We don't allow users to create databases normally and have often warned of the consequences of this. In the past, we have recovered but this time one of the creators has some hard work to do and justify.

    I have now caused a process to review who needs what (really needs it), the takeover process, and maintenance to kick off while I clean up the mess. I think this leads back to " you can lead a horse to water but you cannot force it to drink". Pity the horse had to die to prove the point.

Viewing 11 posts - 1 through 11 (of 11 total)

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