Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log LDF file


Log LDF file

Author
Message
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 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 !!!!! :-D
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 690
Thanks Hanshi, it worked... :-):-)

Thanks Gail & others members to suggest me...:-)

************************************
Every Dog has a Tail !!!!! :-D
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 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 !!!!! :-D
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 920
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.
Deg-235673
Deg-235673
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 311
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search