Restore with stopat

Question

Post reply

Restore with stopat

y.koteswarrao-652921

SSCrazy

Points: 2010
More actions
July 7, 2010 at 10:37 am

#226602

Hi,
Is it possible to restore further transaction log backups after i issue a restore with stopat command..
Koteswar Rao

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

as far as I know, you need to redo the full sequence with a different stopat parameter.

Unless maybe you need to restore a single [set] of table and not the full db?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Yep, that's the way I thought it worked too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ray Mond SSCertifiable Points: 7159 More actions · Answer 3

If your first STOPAT recovery was used with the STANDBY or NORECOVERY (which doesn't make much sense) options, you can restore subsequent transaction logs. You do need to start with the transaction log that you used the STOPAT option with, and continue from there.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ray Mond (7/7/2010)
If your first STOPAT recovery was used with the STANDBY or NORECOVERY (which doesn't make much sense) options, you can restore subsequent transaction logs. You do need to start with the transaction log that you used the STOPAT option with, and continue from there.

Sounds good but do you have any demo scripts to back this up with?

Ray Mond SSCertifiable Points: 7159 More actions · Answer 5

CREATE DATABASE TheForce

GO

USE TheForce

GO

BACKUP DATABASE TheForce TO DISK = 'c:\TheForce_full.bak' WITH FORMAT

CREATE TABLE episodes (col1 int)

DECLARE @starttime DATETIME

SET @starttime = GETDATE()

INSERT INTO episodes VALUES (4)

WAITFOR DELAY '00:00:01'

INSERT INTO episodes VALUES (5)

WAITFOR DELAY '00:00:01'

INSERT INTO episodes VALUES (6)

BACKUP LOG TheForce TO DISK = 'c:\TheForce_log01.bak' WITH FORMAT

WAITFOR DELAY '00:00:01'

INSERT INTO episodes VALUES (1)

WAITFOR DELAY '00:00:01'

INSERT INTO episodes VALUES (2)

WAITFOR DELAY '00:00:01'

INSERT INTO episodes VALUES (3)

BACKUP LOG TheForce TO DISK = 'c:\TheForce_log02.bak' WITH FORMAT

USE master

RESTORE DATABASE TheForce FROM DISK = 'c:\TheForce_full.bak' WITH STANDBY = 'c:\TheForce.und', REPLACE

DECLARE @stopat DATETIME

SET @stopat = @starttime + CAST('00:00:02' AS DATETIME)

RESTORE LOG TheForce FROM DISK = 'c:\TheForce_log01.bak' WITH STANDBY = 'c:\TheForce.und', STOPAT = @stopat

SELECT * FROM TheForce..episodes

RESTORE LOG TheForce FROM DISK = 'c:\TheForce_log01.bak' WITH STANDBY = 'c:\TheForce.und'

SELECT * FROM TheForce..episodes

SET @stopat = @starttime + CAST('00:00:04' AS DATETIME)

RESTORE LOG TheForce FROM DISK = 'c:\TheForce_log02.bak' WITH STANDBY = 'c:\TheForce.und', STOPAT = @stopat

SELECT * FROM TheForce..episodes

RESTORE LOG TheForce FROM DISK = 'c:\TheForce_log02.bak' WITH STANDBY = 'c:\TheForce.und'

SELECT * FROM TheForce..episodes

DROP DATABASE TheForce

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 6

Hi,

Iam getting the following error

Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN 20000000003300001, which is too recent to apply to the database. An earlier log backup that includes LSN 20000000002700001 can be restored.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Restore Sequence

full backkup restore

restore database my_test1 from disk = 'F:\MSSQL\BACKUP\my_test_fullback.bak'

with replace,standby = 'f:\Mssql\backup\my_test.redo'

Transaction log resotre

restore log my_test1 from disk ='F:\MSSQL\BACKUP\my_test_log_02'

with stopat='2010-07-07 21:20:00.000',standby = 'f:\Mssql\backup\my_test.redo'

select * from <table_name>

Here iam trying to restore furthure logs

restore log my_test1 from disk ='F:\MSSQL\BACKUP\my_test_log_03'

with standby = 'f:\Mssql\backup\my_test.redo'

getting the above error...........Please suggest me

Koteswar Rao

Ray Mond SSCertifiable Points: 7159 More actions · Answer 7

After you restored from 'F:\MSSQL\BACKUP\my_test_log_02' with the STOPAT option, subsequent trx log restores need to start off from the same file i.e. restore 'F:\MSSQL\BACKUP\my_test_log_02' first without the STOPAT value, then followed by 'F:\MSSQL\BACKUP\my_test_log_03'.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 8

Hi,

thanks for your suggestions...i able to restore further txn logs after stopat as per your suggestions , but my purpose was not met...

From the restore Txnlog_2 with stopat i able to recover the deleted record.if do again restore same txnlog_2 subscently the recovered record getting lost.........i want to recover the deleted record and further transactions.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 9

That is not possible. Restoring the transaction logs will always put the database in the state it was in at the end of that backup, or if using the STOPAT option, the state it was in at that specific point in time. I guess you would just need to note down the deleted record's details and redo it.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

homebrew01 SSC Guru Points: 55548 More actions · Answer 10

y.koteswarrao-652921 (7/7/2010)
Hi,
thanks for your suggestions...i able to restore further txn logs after stopat as per your suggestions , but my purpose was not met...
From the restore Txnlog_2 with stopat i able to recover the deleted record.if do again restore same txnlog_2 subscently the recovered record getting lost.........i want to recover the deleted record and further transactions.

You could do the first restore before the records get deleted. Then copy the deleted records into a different database. Continue the restore (which will delete the records). Then copy the saved records from your other database back to the original database.

This assumes you can clearly identify the data that was deleted and/or updated.

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 11

Hi,

thanks for your response............. i have done like that and it was clear.........

thank you very much

Koteswarrao