November 5, 2008 at 2:32 pm
Gurus,
I was testing something at my home, I created a table from table in adventure works database.
1. inserted 50 rows from base table in to new table, took full backup,
2. inserted another 50 rows took log backup,
3. inserted another 50 rows took another log backup
4. inserted another 50 rows and then dropped table.
I took backup of the tail log after dropping table.
Database is in full recovery mode.
Now if I want to recover table to the time right before I dropped table, i need to find that particular time.
How do I find that time? Is there any system SP where it would tell me particular time when Table was dropped?
Any help would be appreciated.
Thanks
November 5, 2008 at 4:16 pm
No stored procedure can help you here. You need manually apply t-logs in sequence and test wth the final log restoration by specifying some guessed time and see if you get close to the point when table was not there after restoration.
You can get rough estimation of drop table time if you were making use of DDL triggers and logging the trigger invocation information somewhere in centralised table.
MJ
November 5, 2008 at 4:47 pm
You can try looking in the default trace for the delete event for that table.
Here is an example query:
Select *
From fn_trace_gettable(@default_trace_file,Default)
Where EventClass In (46 ,47)
And ObjectName = [your object name];
You can get @default_trace_file by using: Select * From sys.traces;
EventClass 46 is Object:Created, 47 is Object:Deleted
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 6, 2008 at 5:59 am
Thanks Manu & Jeffrey Williams.
I don't see the trace file for the day I created and dropped, so looks like I am out of luck.
November 6, 2008 at 6:34 am
A trace file does not switch by day.
run
SELECT * FROM ::fn_trace_getinfo(default)
If you don't see an active trace, someone did disable the default trace for your instance.
There are tools that interpret your db-log, they may help out.
However, best is to just enable the default trace.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2008 at 6:45 am
good example
November 6, 2008 at 7:23 am
Thanks ALZDBA.
I got it almost work. I opened all the trace files and checked in it and I found when I dropped table and took all the backups. But still I can't recover up to the last 50 inserts I made. As I mentioned in the beginning of this thread I did insert 4 times. So I got it up to first 3 inserts. Can you pls help me sugges what am I doing wrong here?
Thanks
November 6, 2008 at 7:55 am
Before you start the restore to recover PIT, you should still backup the current log of the db !
After that, you can restore your previous full backup ( PIT, norecovery), and add on your log-restores (PIT!)
e.g. to generate your restore sequence
Declare @pit datetime
set @pit = '2009/11/06 12:23:34' -- MODIFY TO YOUR PIT !
declare @Logbackup_Filename varchar(500)
declare @Restore_New_DBname varchar(128)
select @Logbackup_Filename = 'E:\MSSQL.1\MSSQL\Backup\IncLogBackup\aaa_Log.BAK'
, @Restore_New_DBname = 'newdbname'
declare @RestoreDb_stmt varchar(5000)
select @RestoreDb_stmt = 'restore database ' + @Restore_New_DBname + '
FROM DISK = ''c:\dtestfull.bak''
WITH MOVE N''DTEST'' TO N''U:\DB78\Data\' + @Restore_New_DBname + '.mdf''
, MOVE N''DTEST_log'' TO N''S:\DB78\Log\' + @Restore_New_DBname + '_log.LDF''
, NoRecovery '
print @RestoreDb_stmt + char(10) + 'GO'
-- exec (@RestoreDb_stmt)
if (select isnull( object_id(N'tempdb.[dbo].[#Tmp_BackupHeaders]'), 0)) <> 0
begin
DROP table #Tmp_BackupHeaders
end
-- versie SQL2K5 sp2
create table #Tmp_BackupHeaders (
BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,[CodePage] smallint
,UnicodeLocaleId int
, UnicodeComparisonStyle int
, CompatibilityLevel tinyint
, SoftwareVendorId int
, SoftwareVersionMajor int
, SoftwareVersionMinor int
, SoftwareVersionBuild int
, MachineName nvarchar(128)
, Flags int
, BindingID uniqueidentifier
, RecoveryForkID uniqueidentifier
, Collation nvarchar(128)
, FamilyGUID uniqueidentifier
, HasBulkLoggedData bit
, IsSnapshot bit
, IsReadOnly bit
, IsSingleUser bit
, HasBackupChecksums bit
, IsDamaged bit
, BeginsLogChain bit
, HasIncompleteMetaData bit
, IsForceOffline bit
, IsCopyOnly bit
, FirstRecoveryForkID uniqueidentifier
, ForkPointLSN numeric(25,0) NULL
, RecoveryModel nvarchar(60)
, DifferentialBaseLSN numeric(25,0) NULL
, DifferentialBaseGUID uniqueidentifier
, BackupTypeDescription nvarchar(60)
, BackupSetGUID uniqueidentifier NULL
)
declare @SQL varchar(5000)
set @SQL = 'RESTORE HEADERONLY FROM DISK = ''' + @Logbackup_Filename + ''' '
insert into #Tmp_BackupHeaders
exec (@SQL)
declare csrRestoreLog cursor for
select 'print ''-- db [' + convert(varchar(128),DatabaseName) + '] -- ' + convert(varchar(15), Position)
+ ' -- LSN ' + convert(varchar(128), FirstLSN ) + ' ' + convert(varchar(128), LastLSN )
+ ' -- BackupDate ' + convert(varchar(25), BackupStartDate, 121) + ' ' + convert(varchar(25), BackupFinishDate, 121)
+ ' ''' + char(10)
+ 'Restore Log ' + @Restore_New_DBname + '
FROM DISK = ''' + @Logbackup_Filename + '''
WITH FILE = ' + convert(varchar(15),Position) + char(10)
+ case when @pit is null then + ' -- , STOPAT = ''datetime'' '
else ' , STOPAT = ''' + convert(char(23),@PIT,121) + ''' '
end + char(10)
+ case Position when M.MAX_Position then ' , Recovery '
else
case when @pit is null then ' , NoRecovery '
else ' , Recovery '
end
end
-- + char(10) + 'GO'
from #Tmp_BackupHeaders
, (select max(Position) as MAX_Position from #Tmp_BackupHeaders ) M
order by Position
declare @RestoreLog_stmt varchar(5000)
open csrRestoreLog
FETCH NEXT FROM csrRestoreLog
INTO @RestoreLog_stmt
WHILE @@FETCH_STATUS = 0
BEGIN
print @RestoreLog_stmt + char(10) + 'GO'
-- exec (@RestoreLog_stmt)
-- Volgende rij inlezen
FETCH NEXT FROM csrRestoreLog
INTO @RestoreLog_stmt
END
-- Cursor afsluiten
CLOSE csrRestoreLog
DEALLOCATE csrRestoreLog
go
As you will see the PIT restore restores using RECOVERY with every PIT !
As soon as it reaches the PIT it will restore the db, restores afterward will fail !
It leaves a comment the PIT was not found in the other cases.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2008 at 8:20 am
ALZDBA,
Thanks a bunch for the reply with the script.
Actually I made mistake. I again ran RESTORE and it worked out.
When I dropped table time was 10:53:34 AM and I restored upto 10:53 AM, so it did restore but only up to 10:53 AM. I think I must have inserted another 50 Records between 10:53 AM and 10:53:34 AM. So that's why I wasn't getting all 200 records.
First time:
RESTORE LOG POINTINTIMERECOVERY
FROM DISK = 'C:\DATABASEFILES\POINTINTIMERECOVERY_TAILLOG.TRN'
WITH RECOVERY, STOPAT = 'Nov 3, 2008 10:53 AM'
As you see in the code I didn't mention seconds.
Second Time:
RESTORE LOG POINTINTIMERECOVERY
FROM DISK = 'C:\DATABASEFILES\POINTINTIMERECOVERY_TAILLOG.TRN'
WITH RECOVERY, STOPAT = 'Nov 3, 2008 10:53:30 AM'
As you see I put second and it restored all 200 record.
Thanks again for the help.
And by the way I opened that trace file in the SQL Server Profiler that's how I found the time. Is there any way to customize view of the TRACE file in Profiler? It shows so many columns.
November 6, 2008 at 8:43 am
When you do your restore....do you first have to restore the FULL BAK then the LOG TRN after this.
I.e lets say you have FULL BACK Midnight and then 5 TRN for every hour.
Then it goes down on 4 hour.
Do you do FULL BACK then TRN1, 2, 3, 4 or can you just restore TRN1 without a FULL BACKUP.
Cheers
How do you get the final log just by doing backup trn again.
November 6, 2008 at 8:59 am
Your baseline is always the full backup restored first. Then each log is applied to the restored database, oldest to newest, up to the point you want to get to, either point-in-time or last log backup. Each restore should indicate "norecovery" so that additional logs can be restored. The final restore should indicate "recovery" to bring the database online and ready for use.
To get a final log, simply backup log, provided the database is online, that simple.
-- You can't be late until you show up.
November 6, 2008 at 9:08 am
When you do your restore....do you first have to restore the FULL BAK then the LOG TRN after this.
I.e lets say you have FULL BACK Midnight and then 5 TRN for every hour.
Then it goes down on 4 hour.
Do you do FULL BACK then TRN1, 2, 3, 4 or can you just restore TRN1 without a FULL BACKUP.
Cheers
How do you get the final log just by doing backup trn again.
I was trying PIT with this article. http://www.sql-server-performance.com/articles/dba/Restore_Transaction_Logs_for_Point_in_Time_Recovery_p1.aspx
But the only problem with this article is it doesn't show you the way how do you find particular point up to which you want to do restore. So here how i did it.
Start SQL Server Profiler/ Open/ Trace File...
I am using SQL 2008 so it stores trace file in following location.
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log
I guess you would try to do this exercise today, so after you have dropped table open the latest trace file created by the SQL Server.
In the trace file you should be able to find the information about the table creation, all the back up you took and then drop table (DELETED OBJECT).
Once you find the time you drop the table then you can restore to the time right before the deletion of table.
Hope this helps.
November 6, 2008 at 9:16 am
Thanks i thought that was the procedure ......i just checking that it stayed in my memory bank.
November 6, 2008 at 9:19 am
That thread and the way to get the time of the deleted object is very good ..... i wouldn't have thought to go read the TRACE file.....i might give that a go myself today.
We don't have FULL mode on yet...we in SIMPLE.....yes i know.
But we have replication going on and i did read having this on a system and full mode can be tricky cause the replication has to read the logs too.
One thing ....what do you do for your timing of your LOG TRN do you run these say ever 15 minutes all day long.
So say midnight at 12 you do full back then fire off TRN every 15 minutes. What about when you do your index rebuilds do you still keep the TRN running.
Cheers.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply