Log LDF file

Question

Post reply

Log LDF file

Viewing 15 posts - 16 through 30 (of 40 total)

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

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 1

OnlyOneRJ

Hall of Fame

Points: 3894

August 16, 2013 at 12:13 am

#1641988

Thanks homeBrew..

Gail - Will APEX SQL ttol help me to read Log files which are backed up every 3 hours..

************************************
Every Dog has a Tail !!!!! :-D

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

OnlyOneRJ (8/16/2013)

Will APEX SQL ttol help me to read Log files which are backed up every 3 hours..

I don't have any experience with their tool, I just googled a bit and came on their website.

According to the information on the website, it (ApexSQL LOG) can read the LOG-file and get audit information out of it. They also have a specific SQL Audit tool (ApexSQL Audit). Why don't you just contact them and ask for more information, a demonstration and/or use the trial version. They can tell you much more about the product(s) then we can on this forum.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

HanShi

SSC-Dedicated

Points: 33506

August 16, 2013 at 12:58 am

#1641998

But remember: the LOG backups are primarily for recovering lost data. It is not intended to do auditing. There are other features and tools to do auditing.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

OnlyOneRJ (8/16/2013)

Gail - Will APEX SQL ttol help me to read Log files which are backed up every 3 hours..

Probably, but it's a damn expensive app. Was over $1000 per licence last time I checked

Why are you insisting on using the wrong tool for the job here? Log backups are NOT for auditing purposes. If it's some managerial decree, please go and explain to said manager that while possible what he's asking is going to be expensive, painful and complex at best and that there are way better solutions for auditing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 5

Gail, now they have come up with new need..

They dont want to know who has deleted the records.. they just want to recover the data which gets deleted if incase..

Like if data gets deleted after 2 hours then using log backup they want that data to be recovered ... will it be possible???

************************************
Every Dog has a Tail !!!!! :-D

HanShi SSC-Dedicated Points: 33506 More actions · Answer 6

Yes, recover lost data from a backup is the reason why you take backup.

Restore the last FULL and last DIFF (if applicable) backup with the NORECOVERY option to an alternate location. This can be a test instance (preferred) or you can restore the backups using another database name on the original instance. Restore all consecutive LOG backups until moments before the delete-action took place (using the STOPAT option). Use the RECOVERY option with the last restore action.

For more information about restoring to a point-in-time, see http://technet.microsoft.com/en-us/library/ms179451(v=sql.100).aspx

You can now query the restored database to get the deleted rows. A tool like Redgate Data Compare can come in handy at this point, but it's also possible without it.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

OnlyOneRJ (8/16/2013)

Like if data gets deleted after 2 hours then using log backup they want that data to be recovered ... will it be possible???

Now that's what log backups are for. Restore full backup, restore logs up to the last one before the delete occurred. Copy the deleted data over.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 8

I would also look at securing your database with the relevant lowest possible priviledge. Having correct security from the outset can reduce the chances that someone will delete data directly from the tables. Perhaps look at removing the db_datawriter/dbo perms (which are all too common) and replacing with a stored procedure(s) and only give access to these specific SProcs. This will not remove all risk but may reduce the chances of it occurring again.

You can additionally use dml triggers to ensure that only the correct users can delete data.

Additionally, look at using some form of auditing. Enterprise version is rich with auditing features. Table based triggers can work, if you only have standard edition or you can use the merge statements output clause for another way of doing this(although I think that they may not work in conjunction with dml triggers) , if you are using CRUD .

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 9

Thanks Hanshi, Ness & GAIL...

below will be tyhe step which will be done incase of deletion..

1) Restore Last Full Backup (Normal Backup)

2) Restore all Log Backup Copies after Full backup till available file.

Just a query... When i restore Full backup.. automatically the Log file will be created then how do i restore other Log backup copies on to the existing Full backup???

************************************
Every Dog has a Tail !!!!! :-D

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

OnlyOneRJ (8/18/2013)

Thanks Hanshi, Ness & GAIL...

below will be tyhe step which will be done incase of deletion..

1) Restore Last Full Backup (Normal Backup)

2) Restore all Log Backup Copies after Full backup till available file.

Just a query... When i restore Full backup.. automatically the Log file will be created then how do i restore other Log backup copies on to the existing Full backup???

I would recommend restoring your full backup WITH STANDBY. This will leave the database in a Standby/Read-only state where you can review data in the database, yet still restore additional log backups. From the point when you have the full database restored, you can begin to restore each log file, again WITH STANDBY, and then review the data after each restore. Once you know which log file contained the DELETE transaction that wiped out your data you can start the process again, except do restore that last log backup. Or, if your requirements are more granular, i.e. to recover the missing data from the exact moment before it was deleted and not up to 3 hours before (your log backup schedule), then you will need to experiment with the log that contains the delete transaction to find the exact point in time when the delete was issued. Once you know the exact point in time you can restart the process and issue a restore with a STOPAT with a value right before the delete was issued. It's not a difficult process, but tedious and potentially very time consuming. Better to get on top of who has permissions to delete data and prevent errant deletes via permissions and training, where possible.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 11

BELOW STEPS FOLLOWED FOR TESTING PURPOSE.. BUT GETTING ERROR..

1) Created New Db & add 1 table with 6 records

2) took full backup

3) inserted 2 more records

4) took Log backup

5) deleted 3 records

..

Now went to other server &

1) restored full backup with standby readonly mode

2) trying to attach log file to it in standby mode.. it gives error :w00t:

************************************
Every Dog has a Tail !!!!! :-D

HanShi SSC-Dedicated Points: 33506 More actions · Answer 12

HanShi

SSC-Dedicated

Points: 33506

August 19, 2013 at 4:21 am

#1642603

OnlyOneRJ (8/19/2013)

2) trying to attach log file to it in standby mode.. it gives error :w00t:

You need to RESTORE the LOG backup and not attach a log file. Include the STANDBY option again in the LOG RESTORE.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

OnlyOneRJ (8/19/2013)

2) trying to attach log file to it in standby mode.. it gives error :w00t:

RESTORE LOG <database name> FROM DISK = <log backup location> WITH STANDBY = <standby file location>, STOPAT = <time just before delete occurred>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 14

I did try to restore .trn file..

error is

The Log in this backup terminates at LSN 200002222.. etc

whcih is too early to apply to the database :w00t::w00t:

i took two log backup..

one after full backup & one after deletion of records..

tried to restore both using standby.. stilll same error

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 15

You need to restore the full backup, all the log backups taken after the full backup ending with the one that contains the deletion (that's why the STOPAT clause)

You're trying to restore a log backup from before the full backup was taken, given that error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass