October 24, 2007 at 6:50 pm
Hello
Environment : SQL Server 2005 CE , Win 2003
I am new to SQL Server and has currently set up a (Production) Standby db. I have a few questions ; Would appreciate if someone can provide with inputs.
Q1. Other than looking at the log how to find out what what was the LAST Trn log file applied? Is there any system view which will tell me what logs are applied and what NOT applied? The reason i am asking is i can generate a dynamic sql script to do the applying at the standby instead of doing it manually.
Q2. How to find out what is the Start LSN and End LSN for a log file?
Q3. After a planned maintenance / downtime how to speeden up applying of the log? Currently i have set up 5 minutes gap and it seems to be applying only 12 files in an hour and does not seem to be catching up. Am i missing something?
Thanks
October 24, 2007 at 7:43 pm
Found answer to Question 2 .
"backupset" is the view (in msdb)
select database_name, backup_finish_date
, type, [name], user_name, first_lsn, last_lsn, * from backupset
where database_name = 'YOUR_DATABASE_NAME_HERE'
order by 2
But this doesn't seem to indicate if it was successfully applied at the standby site.
October 31, 2007 at 8:54 am
If you decide to generate your log file restore commands dynamically....here's a script found awhile back that does it.
This procedure uses the MSDB backup tables, and will generate the basic disk RESTORE commands for a database. It will include the Full, differential and all associated log recovery commands for you. Run the stored proc in Query Analyser then copy/alter the output to recover your DB.
I have attached a copy of this procedure which will be pushed to all our servers.
This is great if using Enterprise Manager is not your cup of tea.
Note: Always TEST before you use this recover.
How to Use:
-- Generates script starting from the last FULL backup between the two dates below using a stat recalc of 40 for Interface_Manager
Exec sp_GenRestoreDBScript 'Interface_Manager', 'Nov 21, 2005 9:00 AM', 'Nov 22, 2005 10:00 PM', 40
--
-- Generates script to restore DB to 'Nov 22, 2002 11:45 AM' using available log files
Exec sp_GenRestoreDBScript 'Interface_Manager', Nov 21, 2005 9:10 AM', Nov 22, 2005 9:20 AM', 40, 'Nov 22, 2005 11:45 AM'
CREATE procedure sp_GenRestoreDBScript @p_dbname varchar(50), @p_datetime datetime, @p_usefullbackupend datetime, @p_stats varchar(20) = 10, @p_stopat datetime = null as
SET NOCOUNT ON
--
-- Name: sp_GenRestoreDBScript
--
-- Parameters
-- @p_dbnameDatabase to generate restore scripts for
--@p_datetimeStart time to search for last FULL backup
--@p_usefullbackupendEnd time to search for last FULL backup
--@p_statsdisplay STATS (default 10%)
--@p_stopatFor Point In Time (PTR) recovery, date which to recover to
--
-- Current Restrictions
--1) doesnt factor in more than 1 device, only works of FROM DISK=
--2) doesnt work with the TAPE option
--3) doesnt restore specific FILE or FILEGROUPS
--4) doesnt restore to a specific MARK, only STOPAT
--5) not tested for replicated or clustered databases
-- How it works
--1) locates a FULL backup between @p_datetime and @p_usefullbackupend
--2) locates any differentials and log backups associated with this FULL (by locating the next FULL backup after the one it found and using anything between these for the DB)
--3) supports multiple appended backups to a single backup device (file)
--4) will script the FULL backup, then the last DIFFERENTIAL then all LOGs after this differential, finally doing a WITH RECOVERY for the last LOG or full/differential (whater the last backup type was)
-- Examples
--
--Generates script starting from the last FULL backup between the two dates below using a stat recalc of 40 for DB tmp
--exec sp_GenRestoreDBScript 'tmpdb', 'Nov 22, 2005 9:00 AM', 'Nov 22, 2002 10:00 PM', 40
--
--Generates script to restore DB to 'Nov 22, 2005 11:45 AM' using available log files
--exec sp_GenRestoreDBScript 'tmpdb', 'Nov 21, 2005 9:10 AM', 'Nov 22, 2005 9:20 AM', 40, 'Nov 22, 2005 11:45 AM'
DECLARE @v_lastfullbackupINTEGER
DECLARE @v_nextdayfullbackup INTEGER
DECLARE @v_lastdiffbackup INTEGER
DECLARE @v_lastlogbackup INTEGER
DECLARE @v_logfilestopat VARCHAR(100)
DECLARE @v_restoreoptions_all_db VARCHAR(150)
DECLARE @v_errorVARCHAR(150)
-- Locate last backup details for the database
-- the @p_datetime defines the FULL backup file to be restored from (last file is always used for the particular date)
set @v_lastfullbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'D')
set @v_nextdayfullbackup = (select min(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_usefullbackupend and BKS.type = 'D')
-- ensures restore doesnt use files assoc with next full backup (if any)
if @v_nextdayfullbackup is null or @v_nextdayfullbackup = ''
set @v_nextdayfullbackup = 99999999
set @v_lastdiffbackup = (select max(BKS.backup_set_id)from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'I' and BKS.backup_set_id < @v_nextdayfullbackup)
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'L' and BKS.backup_set_id < @v_nextdayfullbackup)
-- Set restore options
-- EDIT THIS SINGLE LINE BELOW TO ADD YOUR OWN OPTIONS FOR ALL RESTORES
if @p_stats is not null and isnumeric(@p_stats) = 1 and @p_stats between 1 and 100
set @v_restoreoptions_all_db = ', STATS = ' + cast(@p_stats as varchar)
else begin
raiserror('Invalid STATS parameter. Numeric values from 1 to 100 only.', 16,1)
return 1
end
if @p_stopat is not null begin
set @v_logfilestopat = ', STOPAT = ''' + cast(@p_stopat as varchar) + ''''
-- set max log file to where we need to stop
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_set_id > @v_lastfullbackup and BKS.backup_set_id < @v_nextdayfullbackup and BKS.type = 'L' and @p_stopat between backup_start_date and backup_finish_date)
if @v_lastlogbackup is null or @v_lastlogbackup = '' begin
raiserror('Invalid STOP AT date. No log files exist or date does not fit in specific log backup range.', 16,1)
return 1
end
end
-- ##########################################################
-- Generated script of recover commands for a given DB name and date
-- Step 1. FULL database recovery
-- ##########################################################
print '-- Recover last full database backup'
select
'RESTORE DATABASE [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when
(select count(*)
from msdb.dbo.backupset BKS2
where BKS.database_name = BKS2.database_name
and BKS2.backup_start_date > BKS.backup_start_date
andBKS2.type in ('L', 'I')) >= 1
then
', NORECOVERY'
else
', RECOVERY'
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'D' -- full backup
IF @@rowcount <= 0 BEGIN
set @v_error = 'ERROR - Could not find any full backups made during the day specified - ' + CAST(@p_datetime AS VARCHAR)
raiserror(@v_error, 16,1)
return 1
END
-- ##########################################################
-- Step 2. DIFFERENTIAL database recovery
-- ##########################################################
print '-- Recover last differential'
select
'RESTORE DATABASE [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when
(select count(*)
from msdb.dbo.backupset BKS2
where BKS.database_name = BKS2.database_name
and BKS2.backup_start_date > BKS.backup_start_date
andBKS2.type in ('L')) >= 1
then
', NORECOVERY'
else
', RECOVERY'
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastdiffbackup
andBKS.backup_set_id > @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'I' -- differential backup
-- ##########################################################
-- Step 3. LOG file recovery (not including last log)
-- ##########################################################
print '-- Recover log files (not including last log)'
-- if there is a last diff backup, then gets logs after this backup, otherwise logs only after the full
IF @v_lastdiffbackup is not null
select
'RESTORE LOG [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
', NORECOVERY'
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastdiffbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
ELSE
select
'RESTORE LOG [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
', NORECOVERY'
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastfullbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
-- ##########################################################
-- Step 4. LOG file recovery (last file)
-- ##########################################################
print '-- Recover last log file'
select
'RESTORE LOG [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when @v_logfilestopat is null then
', RECOVERY'
else
@v_logfilestopat +
', RECOVERY'
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF--, msdb.dbo.backupfile BF
where
BKS.backup_set_id = @v_lastlogbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
GO
CREATE procedure sp_GenRestoreDBScript_SLS @p_dbname varchar(50), @p_datetime datetime,
@p_usefullbackupend datetime, @p_stats varchar(20) = 10, @p_stopat datetime = null as
SET NOCOUNT ON
--
-- Name: sp_GenRestoreDBScript_SLS
--
-- Parameters
-- @p_dbnameDatabase to generate restore scripts for
--@p_datetimeStart time to search for last FULL backup
--@p_usefullbackupendEnd time to search for last FULL backup
--@p_statsdisplay STATS (default 10%)
--@p_stopatFor Point In Time (PTR) recovery, date which to recover to
--
-- Current Restrictions
--1) doesnt factor in more than 1 device, only works of FROM DISK=
--2) doesnt work with the TAPE option
--3) doesnt restore specific FILE or FILEGROUPS
--4) doesnt restore to a specific MARK, only STOPAT
--5) not tested for replicated or clustered databases
-- How it works
--1) locates a FULL backup between @p_datetime and @p_usefullbackupend
--2) locates any differentials and log backups associated with this FULL (by locating the next FULL backup after the one it found and using anything between these for the DB)
--3) supports multiple appended backups to a single backup device (file)
--4) will script the FULL backup, then the last DIFFERENTIAL then all LOGs after this differential, finally doing a WITH RECOVERY for the last LOG or full/differential (whater the last backup type was)
-- Examples
--
--Generates script starting from the last FULL backup between the two dates below using a stat recalc of 40 for DB tmp
--exec sp_GenRestoreDBScript 'tmpdb', 'Nov 22, 2005 9:00 AM', 'Nov 22, 2002 10:00 PM', 40
--
--Generates script to restore DB to 'Nov 22, 2005 11:45 AM' using available log files
--exec sp_GenRestoreDBScript 'tmpdb', 'Nov 21, 2005 9:10 AM', 'Nov 22, 2005 9:20 AM', 40, 'Nov 22, 2005 11:45 AM'
DECLARE @v_lastfullbackupINTEGER
DECLARE @v_nextdayfullbackup INTEGER
DECLARE @v_lastdiffbackup INTEGER
DECLARE @v_lastlogbackup INTEGER
DECLARE @v_logfilestopat VARCHAR(100)
DECLARE @v_restoreoptions_all_db VARCHAR(150)
DECLARE @v_errorVARCHAR(150)
-- Locate last backup details for the database
-- the @p_datetime defines the FULL backup file to be restored from (last file is always used for the particular date)
set @v_lastfullbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'D')
set @v_nextdayfullbackup = (select min(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_usefullbackupend and BKS.type = 'D')
-- ensures restore doesnt use files assoc with next full backup (if any)
if @v_nextdayfullbackup is null or @v_nextdayfullbackup = ''
set @v_nextdayfullbackup = 99999999
set @v_lastdiffbackup = (select max(BKS.backup_set_id)from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'I' and BKS.backup_set_id < @v_nextdayfullbackup)
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'L' and BKS.backup_set_id < @v_nextdayfullbackup)
-- Set restore options
-- EDIT THIS SINGLE LINE BELOW TO ADD YOUR OWN OPTIONS FOR ALL RESTORES
if @p_stats is not null and isnumeric(@p_stats) = 1 and @p_stats between 1 and 100
set @v_restoreoptions_all_db = ', @with = ''STATS = ' + cast(@p_stats as varchar)+ ''''
else begin
raiserror('Invalid STATS parameter. Numeric values from 1 to 100 only.', 16,1)
return 1
end
if @p_stopat is not null begin
set @v_logfilestopat = ', STOPAT = ''' + cast(@p_stopat as varchar) + ''''
-- set max log file to where we need to stop
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_set_id > @v_lastfullbackup and BKS.backup_set_id < @v_nextdayfullbackup and BKS.type = 'L' and @p_stopat between backup_start_date and backup_finish_date)
if @v_lastlogbackup is null or @v_lastlogbackup = '' begin
raiserror('Invalid STOP AT date. No log files exist or date does not fit in specific log backup range.', 16,1)
return 1
end
end
-- ##########################################################
-- Generated script of recover commands for a given DB name and date
-- Step 1. FULL database recovery
-- ##########################################################
print '-- Recover last full database backup'
select
'EXEC master.dbo.xp_Restore_database @database=[' + BKS.database_name + '] ' +
'@Filename = ''' + BMF.physical_device_name + ''', ' +
'@Filenumber = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when
(select count(*)
from msdb.dbo.backupset BKS2
where BKS.database_name = BKS2.database_name
and BKS2.backup_start_date > BKS.backup_start_date
andBKS2.type in ('L', 'I')) >= 1
then
', @with = ''NORECOVERY'''
else
', @with = ''RECOVERY'''
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'D' -- full backup
IF @@rowcount <= 0 BEGIN
set @v_error = 'ERROR - Could not find any full backups made during the day specified - ' + CAST(@p_datetime AS VARCHAR)
raiserror(@v_error, 16,1)
return 1
END
-- ##########################################################
-- Step 2. DIFFERENTIAL database recovery
-- ##########################################################
print '-- Recover last differential'
select
'EXEC master.dbo.xp_Restore_database @database=[' + BKS.database_name + '] ' +
'@Filename = ''' + BMF.physical_device_name + ''', ' +
'@Filenumber = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when
(select count(*)
from msdb.dbo.backupset BKS2
where BKS.database_name = BKS2.database_name
and BKS2.backup_start_date > BKS.backup_start_date
andBKS2.type in ('L')) >= 1
then
', @with = ''NORECOVERY'''
else
', @with = ''RECOVERY'''
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastdiffbackup
andBKS.backup_set_id > @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'I' -- differential backup
-- ##########################################################
-- Step 3. LOG file recovery (not including last log)
-- ##########################################################
print '-- Recover log files (not including last log)'
-- if there is a last diff backup, then gets logs after this backup, otherwise logs only after the full
IF @v_lastdiffbackup is not null
select
'EXEC master.dbo.xp_restore_log @database=[' + BKS.database_name + '] ' +
'@Filename = ''' + BMF.physical_device_name + ''', ' +
'@Filenumber = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
', @with = ''NORECOVERY'''
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastdiffbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
ELSE
select
'EXEC master.dbo.xp_restore_log @database=[' + BKS.database_name + '] ' +
'@Filename = ''' + BMF.physical_device_name + ''', ' +
'@Filenumber = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
', @with = ''NORECOVERY'''
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastfullbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
-- ##########################################################
-- Step 4. LOG file recovery (last file)
-- ##########################################################
print '-- Recover last log file'
select
'EXEC master.dbo.xp_restore_log @database=[' + BKS.database_name + '] ' +
'@Filename = ''' + BMF.physical_device_name + ''', ' +
'@Filenumber = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when @v_logfilestopat is null then
', @with = ''RECOVERY'''
else
@v_logfilestopat +
', @with = ''RECOVERY'''
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF--, msdb.dbo.backupfile BF
where
BKS.backup_set_id = @v_lastlogbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
GO
October 31, 2007 at 9:17 am
Keep in mind that your standby server logs should be loaded with a delay (a good tiemframe is 2 hours). Continue the backups and the copy's, but delay the load to the standby server for 120 minutes.
This protects you in the event of data corruption. If someone trucates a large table or modifies the data incorrectly, you don't want the change immediatly applied to your standby server. Imagine a 400Gig database needing to be restored because code didn't include a where clause? You'd have 2 corrupt 400Gig databases.
To recover from data error: immediately stop the job that performs the log restore on standby. Find the approximate time the corruption occurred and apply the log on standby with a STOP AT command--stopping immediately prior to applying the error.
On heavily used systems, I've found that 2 hours is ample time for someone to be notified about data corruption.
December 16, 2008 at 11:54 pm
Hi Janet,
Where do you set the delay time in SQL 2005? I've been looking for that but haven't found it.
Appreciate the help.
Regards
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply