Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Log LDF file Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 4:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 2,353, Visits: 2,867
Execute the code below to get a list of all backups taken on the specific database.
You need to restore the backups in sequence, starting with the full backup that has the same number in the column [first_lsn] that the sequential LOG backups has in the [database_backup_lsn] column.
SELECT database_name
, backup_finish_date
, type
, first_lsn
, database_backup_lsn
, physical_device_name
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backup_finish_date IS NOT NULL
AND database_name = '{fill_in_your_dbname}'
ORDER BY database_name
, backup_finish_date



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1485728
Posted Tuesday, August 20, 2013 12:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Hanshi I executed your query on Sample Database.. below is the output

database_name backup_finish_date type first_lsn database_backup_lsn physical_device_name
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ---- --------------------------------------- --------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A 2013-08-20 02:00:20.000 D 24000000014800179 0 E:\A_Full.bak
A 2013-08-20 02:02:26.000 L 24000000014800179 24000000014800179 E:\A_Tran.trn
A 2013-08-20 02:03:14.000 D 24000000027900076 24000000014800179 E:\A2_Tran.trn

(3 row(s) affected)
====


Now please tell me the restoration step with details.. like standy by or no recovery or etc..








************************************
Every Dog has a Tail !!!!!
Post #1486118
Posted Tuesday, August 20, 2013 12:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 2,353, Visits: 2,867
Below is the code to restore your database [A] to a database with name [A_SB] using the FULL and two LOG backups of database [A]. You can query the standby database [A_SB] between each restore action.

I don't know what the logical names of your databasefiles are. I assumed this to be "A" for the datafile and "A_log" for the logfile. Alter these names in the script if the names are different.

-- restore the FULL backup using the STANDBY option (so the database can be queried after the restore)
RESTORE DATABASE [A_SB]
FROM DISK = N'E:\A_Full.bak'
WITH FILE = 1
, MOVE N'A' TO N'E:\A_SB.mdf' -- change A to the logical DATA filename within your database
, MOVE N'A_log' TO N'E:\A_SB_log.ldf' -- change A_log to the logical LOG filename within your database
, STANDBY = N'E:\A_SB_ROLLBACK_UNDO.BAK'
, NOUNLOAD, STATS = 10
GO
-- restore the first LOG backup, taken after the FULL backup
RESTORE DATABASE [A_SB]
FROM DISK = N'E:\A_Tran.trn'
WITH FILE = 1
, STANDBY = N'E:\A_SB_ROLLBACK_UNDO.BAK'
, NOUNLOAD, STATS = 10
GO
-- optional: restore consecutive LOG backups, taken after the FULL backup, up untill the point-in-time you need

/********/
-- at this point the database is in the state where the delete action is not executed yet
-- you can query the table and see all the records available
/********/

-- restore the last LOG backup to see that all executed action are logged in the backup
RESTORE DATABASE [A_SB]
FROM DISK = N'E:\A2_Tran.trn'
WITH FILE = 1
, STANDBY = N'E:\A_SB_ROLLBACK_UNDO.BAK'
, NOUNLOAD, STATS = 10
GO
/********/
-- at this point the database is in the state after the delete action is executed
-- you can query the table and see some records are deleted
/********/



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1486119
Posted Tuesday, August 20, 2013 1:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Thanks Hanshi, it worked...

Thanks Gail & others members to suggest me...








************************************
Every Dog has a Tail !!!!!
Post #1486131
Posted Wednesday, August 21, 2013 7:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Hanshi One more question on this...

Consider below scenario

Day 1
Full Backup
Log backup every 3 hours
(I have 1 Full backup & 4 log backup)

Day 2
Full Backup
Log backup every 3 hours
(I have 1 Full backup & 4 log backup)

Day 3
Full Backup
Log backup every 3 hours
(I have 1 Full backup & 4 log backup)

Day 4 - If i was told that on day 2 their was a record delete which they want to recover then...

What will i do is..


Restore Full Backup of Day 2 on some testing enviornment
Restore Log backup one by one till i get the data..

Is this correct???








************************************
Every Dog has a Tail !!!!!
Post #1486723
Posted Thursday, August 22, 2013 12:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 2,353, Visits: 2,867
Yes
Restore FULL backup from day 2 (with standby mode) and then restore each LOG backup taken after this FULL backuip up until the point-in-time of your needs.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1487076
Posted Friday, August 23, 2013 1:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 322, Visits: 527
OnlyOneRJ (8/14/2013)
Hi Gail,

Requirenment here is to track if any update or delete happens by mistake, for which i am asked to take Log backups every 3 hours..
Execution of the backups finishes in 10 minutes .. so not problem..
Just a query that..
In-case if any delete happens then how will i provide information using those Log backups??? do i need any tool for it???


I would suggest turning on auditing and logging the events that you deem important. Be careful not to log to much because too much logging can have a negative impact on performance.
Post #1487655
Posted Sunday, August 25, 2013 3:53 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 30, 2014 12:21 PM
Points: 11, Visits: 279
Yes, I've a solution for your problem, and I can say it's possible take a rollback rows straight from the log, you can get in touch with me.

sincerely Dondeg
Post #1488256
Posted Sunday, August 25, 2013 8:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Deg-235673 (8/25/2013)
Yes, I've a solution for your problem, and I can say it's possible take a rollback rows straight from the log, you can get in touch with me.

sincerely Dondeg


It would really be nice if you'd share such information with the rest of us instead of taking it underground. Thanks.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488276
Posted Sunday, August 25, 2013 9:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Backups and restores are a wonderful thing but, if the data is THAT important and mistakes in the form of modifications and deletions are made THAT often, then it's time to setup a simple audit system on the table. That'll save a whole bunch of headaches provided that the audit trigger is written correctly. With the addition of something like the ORIGINAL_LOGIN(), you might be able to catch someone doing it directly in the database (it won't help much through an application but at least you'll know someone is doing it through an app).

The next thing to do would be to tighten up who has privs to delete/modify rows because they're obviously not very good at it. Seriously!


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488277
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse