The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT
command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction.
This is also why you can't rollback this INSERT
: it's already committed. So the rule is the same as explicit transactions: you can't rollback once they've been committed.
You can see what I mean directly from inside SQL Server.
Microsoft ships SQL Server with a DMF called sys.fn_dblog
that can be used to look inside the transaction log of a given database.
For this simple experiment I'm going to use the AdventureWorks database:
USE AdventureWorks2008;
GO
SELECT TOP 10 *
FROM dbo.Person;
GO
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
BEGIN TRAN;
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
GO
SELECT *
FROM sys.fn_dblog(NULL, NULL);
GO
Here I'm doing two inserts: one with and one without an explicit transaction.
In the log file you can see that there's absolutely no difference between the two:
The red one is the INSERT
within an autocommit transaction and the blue one is the INSERT
with an explicit transaction.
As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file.
See this Connect item, which is almost identical to the scenario you're trying -- they're close enough that I would say that it's highly likely to be the same issue. I would attempt to reproduce the exact same scenario presented in the bug report (I assume you will be able to).
While it's unclear in which release the fix for that bug landed, by the date of Microsoft's response, I believe this should be fixed in SP3.
If applying a service pack is not an option at this time, you'll want to go with a trusted 3rd-party solution, as mentioned in the other answer.
Best Answer
No. The recover process always starts from a full database backup and then it applies the log backups.
If your database has no backups then it is not in full recovery mode. Untill you actually take a backup it will always run in simple, no matter the intented recovery mode (aka 'pseudo simple', see New script: is that database REALLY in the FULL recovery mode?)
If your database really is in FULL recovery mode, then a backup was tacken and the recovery can start with that.
There are third party tools that can analyze the log and recover stuff, but they work only if the log was not recycled, so is back to the discussion whether the recovery model is really full or simple.