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>'
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
GO
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.
Can you use a variable for the @filename