August 9, 2017 at 1:13 pm
Hi All,
I have an issue and hoping some-one here can have a script available for this.
we have a database that is setup up with full( weekend) and T- log ( once a day) backups on one server. One the other server, we need to apply the full backup and T-log backups in a schedule way and to keep the DB in ready only. We can not apply log shipping because there are time we need to stop this process and initiate it when needed. So Far this is what I am able to do:
- Copy the latest available full backup file to new server ( scheduled).
- Apply the full backup and leaving it in restoring state ( scheduled).
How can I get a T-log restore script so it will go at the backup location find out the last T-Log backup that was applied at the destination and apply the next available T-Log for restore?
Any help is appreciated.
B
August 9, 2017 at 2:38 pm
modify this..
-- choose database or currently used database will be selected
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT
s.server_name,
s.database_name,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.user_name,
s.backup_start_date,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
s.recovery_model, CASE s.[type]
WHEN 'D' THEN 'Restore database ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + m.physical_device_name + ''' with norecovery, replace'
WHEN 'I' THEN 'Restore database ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + m.physical_device_name + ''' with norecovery'
WHEN 'L' THEN 'Restore log ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + m.physical_device_name + ''' with norecovery'
end as cmd
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date ,
backup_finish_date
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply