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>'
