August 19, 2013 at 4:48 am
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
August 20, 2013 at 12:19 am
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
August 20, 2013 at 12:43 am
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
/********/
August 20, 2013 at 1:29 am
Thanks Hanshi, it worked... :-):-)
Thanks Gail & others members to suggest me...:-)
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 7:56 am
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
August 22, 2013 at 12:48 am
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.
August 23, 2013 at 1:20 am
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.
August 25, 2013 at 3:53 pm
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
August 25, 2013 at 8:59 pm
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
Change is inevitable... Change for the better is not.
August 25, 2013 at 9:04 pm
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
Change is inevitable... Change for the better is not.
August 26, 2013 at 12:02 am
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
Deg, that would be great if you share some of the technique here....
************************************
Every Dog has a Tail !!!!! :-D
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply