Scenario:
You are working as SQL Server DBA, you run your full backup jobs and transaction log jobs on different schedules.Now you need to restore database, you have restored the full backup in NoRecovery mode and need to restore set of log backups. It is really annoying to choose one log backup file at a time and keep restoring.
The below script can be used to restore multiple log backup files to database. You can change the select query to include the log backup files you like to restore.
/*-------------------------------------------------- Restore Multiple transaction log backup files ----------------------------------------------------*/ DECLARE @DBName VARCHAR(500) DECLARE @LogFilePath VARCHAR(500) SET @DBName='TestDB' DECLARE LogR_cursor CURSOR FOR --Prepare your list of Log File those needs to be restored.
-- Uncomment the columns while prepare your List of Log files. SELECT --BS.database_name, BMF.physical_device_name --,BS.backup_start_date -- ,BS.backup_finish_date FROM msdb.dbo.backupset BS INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id WHERE BS.database_name = @DBName AND BS.Type = 'L' --AND BS.backup_start_date>='' Provide the StartDate and EndDate To Refine your Selection for Log Files --AND BS.backup_finish_date<='' those you want to restore. ORDER BY backup_start_date, backup_finish_date OPEN LogR_cursor FETCH NEXT FROM LogR_cursor INTO @LogFilePath WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL NVARCHAR(2000) SET @SQL='Restore Log ' + @DBName + ' from Disk=''' + @LogFilePath + ''' With NORECOVERY' PRINT @SQL EXEC(@SQL) FETCH NEXT FROM LogR_cursor INTO @LogFilePath END --SET DATABASE Mode to Recovery DECLARE @SQL_SET_RECOVERY NVARCHAR(2000) SET @SQL_SET_RECOVERY='Restore Database ' + @DBName + ' WITH RECOVERY' PRINT @SQL_SET_RECOVERY EXEC(@SQL_SET_RECOVERY) CLOSE LogR_cursor DEALLOCATE LogR_cursor
Impressive and powerful suggestion by the author of this blog are really helpful to me. Emergency Recovery Group
ReplyDeleteĐặt vé tại phòng vé Aivivu, tham khảo
ReplyDeletemua ve may bay di my
vé máy bay từ houston về việt nam
vé máy bay từ canada về việt nam giá rẻ
vé từ nhật về việt nam
khi nào mở lại đường bay hàn quốc
Vé máy bay từ Đài Loan về Việt Nam
giá khách sạn cách ly ở hà nội
chuyến bay chuyên gia trung quốc
Hi Everyone can anyone tell me, what are the changes need to be done. If we are using this script to restore in our environment. Kindly suggest
ReplyDelete