LiteSpeed – Restore Script

Script to check the backup file – data and log file information

exec master.dbo.xp_restore_filelistonly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to check the backup file header information

exec master.dbo.xp_restore_headeronly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to verify the backup file if the backup file is valid or not

EXEC master.dbo.xp_restore_verifyonly @filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to restore database using Full backup with the default options

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>'

Script to restore database using Full backup with file move option

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

Script to restore database using Full backup with replace option

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

Script to restore Full backup with no recovery

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"',
@with='NORECOVERY'

Script to restore log backup with no recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='NORecovery'

Script to restore log backup with recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery'

Script to do point in time recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery',
@with = 'STOPBEFOREMARK = <LogMark>'

3 thoughts on “LiteSpeed – Restore Script”

sachin May 13, 2015 at 8:25 pm

how about if we want to automate this thing and get the dev database restored everynight from prod backup using litespeed and backup is on tape.

exec master.dbo.xp_restore_database @database = N’DatabaseName’ ,
@tsmconfigfile = N’\\pathfordsmfile\dsmls.opt’,
@tsmclientnode = N’Nodename’,
@tsmclientownerpwd = ‘***’,
@tsmobject = N’objectname’,
@tsmpointintime = ‘this we have to pass for last night’,
@tsmarchive = 1,
@filenumber = 1,
@with = N’REPLACE’,
@with = N’STATS = 10′,
@with = N’MOVE N”.mdf”’,
@with = N’MOVE N”.LDF”’,
@affinity = 0,
@logging = 0

Reply ↓

Benjamin Mathew July 2, 2015 at 8:45 pm

Thank you for posting this. I recently was attempting to recover a database to a point in time, and I only had Litespeed transaction log backups. Now I know how to restore them without the Litespeed Console.

Reply ↓