November 9, 2008 at 7:47 pm
Hi,
I would like to test the following recovery scenario:
1.There is some activity has been done on database 'abc' from 3:00pm to 3:10pm
2.stop sql server delete ldf file of 'abc' and start sql server.
3 recover the data up to 3:10pm
I have the full backup at 2am, diff every 4hrs n log every 15 min
is the above scenario possible?could you plz summarize the steps to do?
November 9, 2008 at 9:29 pm
madhu.arda (11/9/2008)
Hi,I would like to test the following recovery scenario:
1.There is some activity has been done on database 'abc' from 3:00pm to 3:10pm
2.stop sql server delete ldf file of 'abc' and start sql server.
3 recover the data up to 3:10pm
I have the full backup at 2am, diff every 4hrs n log every 15 min
is the above scenario possible?could you plz summarize the steps to do?
use the following from the master database
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'C:\MSSQL\Data\pubs.mdf'
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 9, 2008 at 10:22 pm
thanks,
I did the steps as below:
I have the full backup at 2am, diff every 4hrs n log every 15 min
1.There is some activity has been done on database 'abc' from 3:00pm to 3:10pm
2.stop sql server delete ldf file of 'abc' and start sql server.
3.executed the script as
use master
go
EXEC sp_attach_single_file_db 'abc',
'E:\SQLData\INS1\abc.mdf'
but Iam getting the error:
msg 1801,level 16, state 3, line 1
database 'abc' already exists.
November 9, 2008 at 11:14 pm
try like the following:
EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
if still error occurs, just create new file in data folder with .ldf extension and then attach the db using sp_attach_db,
for more info see SQL BOL.
November 10, 2008 at 12:10 am
madhu.arda (11/9/2008)
thanks,I did the steps as below:
I have the full backup at 2am, diff every 4hrs n log every 15 min
1.There is some activity has been done on database 'abc' from 3:00pm to 3:10pm
2.stop sql server delete ldf file of 'abc' and start sql server.
3.executed the script as
use master
go
EXEC sp_attach_single_file_db 'abc',
'E:\SQLData\INS1\abc.mdf'
but Iam getting the error:
msg 1801,level 16, state 3, line 1
database 'abc' already exists.
because you have deleted the log file but the mdf file remain exists in the database, detatch that mdf file and then apply the statement
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 10, 2008 at 1:25 am
madhu.arda (11/9/2008)
Hi,I would like to test the following recovery scenario:
1.There is some activity has been done on database 'abc' from 3:00pm to 3:10pm
2.stop sql server delete ldf file of 'abc' and start sql server.
3 recover the data up to 3:10pm
I have the full backup at 2am, diff every 4hrs n log every 15 min
is the above scenario possible?could you plz summarize the steps to do?
It's not possible. Because the log has been deleted, there's no way to do a tail log backup to get that last 10 min of data changes. You can get to 3pm by restoring the full, the last diff and then all the tran log backups since that point.
You can try detaching and then attaching the mdf file alone, however if the DB wasn't shut down cleanly before the log was depeted (which would happen if the log drive failed), it will give an error detaching and it won't attach at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply