SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log LDF file


Log LDF file

Author
Message
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8568 Visits: 3718
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
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 692
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
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8568 Visits: 3718
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
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 692
Thanks Hanshi, it worked... :-):-)

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

************************************
Every Dog has a Tail !!!!! :-D
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 692
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
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8568 Visits: 3718
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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4451 Visits: 1025
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 (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 315
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 Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213605 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213605 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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