Tuesday, January 16, 2018

sql server restore full & transactionlog backups

1) check waths inside the backup file:


RESTORE FILELISTONLY from disk = 'L:\My.bak'

you shoul get a list with first column
LogicalName
My 
My_log

RESTORE HEADERONLY FROM DISK='L:\My.trn'
should give you a list of all transaction log backups in this file
next backup FirstLSN=LastLSN of preceeding backup

2) restore full Backup:

use NoRecovery only if you want to restore Transaction Log afterwards

restore database myRestore from disk = 'L:\My.bak' with file=1, NORECOVERY,
move 'My' to 'd:\temp\my2.mdf',
move 'My_Log' to 'd:\temp\my2.ldf'

3) restore Transaction Logs:

restore database myRestore from disk = 'L:\My.trn' with file=1

if there are many transaction log backups in the file my.trn you have to restore it like this:

restore database myRestore from disk = 'L:\My.trn' with file=2

RESTORE HEADERONLY FROM DISK='L:\My.trn'
gives a list of backups in the file

No comments: