SQL 2014 Table Truncated Inadvertently

Question

SQL 2014 Table Truncated Inadvertently

Lippy

SSC Eights!

Points: 953
More actions
September 15, 2020 at 6:25 pm

#3788635

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.

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Do you have any backups of this database?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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.

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

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

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...

Automatically backup new databases
Do T-Log backups based on the Recovery Model of each database
Auto-magically send an email of what completed and when on a daily basis to be received as a "Morning Report"
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.

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

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 6

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

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

ScottPletcher SSC Guru Points: 101097 More actions · Answer 7

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

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.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lippy SSC Eights! Points: 953 More actions · Answer 10

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.