May 13, 2004 at 8:06 am
Hi!
Trying to create my own log shipping scheme. Suppose I place these steps into sheduled jobs:
Job I - Weekly full database restore:
RESTORE DATABASE [mydb] FROM DISK = N'D:\mydb\mydb.BAK'
WITH STANDBY = N'D:\RECOVERY\UNDO_mydb.DAT',
MOVE N'mydb_data' TO N'D:\sql_data\mydb.mdf', MOVE N'mydb_log' TO N'D:\sql_log\mydb_log.ldf'
Job II - Nightly diff database restore:
RESTORE DATABASE [mydb] FROM DISK = N'D:\mydb\mydb_diff.BAK'
WITH STANDBY = N'D:\RECOVERY\UNDO_mydb.DAT',
MOVE N'mydb_data' TO N'D:\sql_data\mydb.mdf', MOVE N'mydb_log' TO N'D:\sql_log\mydb_log.ldf'
Job III - Every 15 minutes log restore:
RESTORE LOG [mydb] FROM DISK = N'D:\mydb\mydb.TLF'
WITH FILE = ???, STANDBY = N'D:\RECOVERY\UNDO_mydb.DAT'
What I can't figure out, is how to "calculate" the file number to be restored, based on what already have been restored? My log backups go to one file, so I can use results of
RESTORE HEADERONLY FROM disk = N'D:\mydb\mydb.TLF'
to see the file numbers and LSNs, but how can I figure out the number of file that was restored the last?
Thanks.
May 14, 2004 at 12:56 am
You can find details about backup and restore files by joining various backup* and restore* tables in MSDB.
May 14, 2004 at 2:33 am
Caveat: I use enterprise edition w/wizard etc. so this is not working solution, just thoughts, but might be enough to get you started.
I don't think the filenames are stored anywhere in msdb. But if you are dumping txlogs you are making up your filenames--so you know the names at this time. Just need to tie the filename w/the LSN info & save this off to your own table while you still know the filename.
Immediately after you backup a txlog, get whatever LSN info you need from msdb..backupset, & insert it into your table. Something like this (probably don't need all these columns):
insert MyLogShipping (...column list...)
select top 1 @my_txlog_filename,
first_lsn, last_lsn, checkpoint_lsn,
database_backup_lsn, database_creation_date,
backup_start_date, backup_finish_date,
from msdb..backupset
where type = 'L'
and database_name = 'YOUR_DB_HERE'
order by backup_finish_date desc
When restoring, you can see what last and next LSNs are on your target:
select top 1 @my_txlog_filename,
first_lsn, last_lsn, checkpoint_lsn,
database_backup_lsn, database_creation_date,
backup_start_date, backup_finish_date,
from msdb..restorehistory r
join msdb..backupset b on b.backup_set_id = r.backup_set_id
where r.destination_database_name = 'YOUR_DB_HERE'
and b.type = 'L'
order by r.restore_date desc
Now use the LSN info to lookup the filename from your MyLogShipping table.
May 14, 2004 at 3:08 am
I have a stored procedure sp_Restore which I use for this purpose, see code below. A couple of notes:
1. all my transaction backup files are titled DBNAMEtransbak, so the DBNAME parameter to the sp can be used to construct the backup device name.
2. You need to change the folder in the set @UndoFile= statemtn to something suitable
3. I use this in several SQL instances with many databases. My overall backup strategy for each db is a complete backup once a day, with the file transferred to the DR machine; once a day, immediately after the full backup, the tx log backup is copied to from DBNAMEtransbak.bak to DBNAMEtransbak.old, and this is followed by a tx backup with INIT; a transaction log backup every hour (or more frequently for some dbs), immediately copied to the DR machine; the sp_Restore procedure then run after completion of each tx log copy. Sometimes the latter fails with LSN mismatch errors, in this case my job tries to restore form the previous tx log copy and then the current tx log backup. If this fails it restores form the full backup and then repeats the tx log restore strategy- more difficult to explain than to do!
For what its worth, here is my code....
CREATE procedure sp_Restore
@DBname varchar(20)
AS
create table #BackupContents (
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackUpStarteDate datetime,
BackUpFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int NULL,
BindingID uniqueidentifier NULL,
RecoveryForkID uniqueidentifier NULL,
Collation nvarchar(128) NULL )
declare @UndoFile varchar(50), @PhyName varchar(50), @BackupFile varchar(50)
declare @Position int, @SQL varchar(1000), @BackUpFinishDate datetime, @LastBackUp datetime
set @BackupFile=@DBname+'transbak'
set @DBname=replace(@DBname,'old','')
set @UndoFile='F:\iscobak\UNDO_'+@DBname+'.DAT'
set @SQL='RESTORE HEADERONLY FROM '+@BackupFile
execute sp_DropISCOUsers @DBname
insert #BackupContents exec(@SQL)
select @Position=max(Position) from #BackupContents
declare cFiles cursor for
select Position, BackUpFinishDate from #BackupContents
open cFiles
fetch next from cFiles into @Position, @BackUpFinishDate
while @@Fetch_Status=0
begin
select @LastBackUp=max(backup_finish_date)
from msdb..backupset where database_name = @DBname
group by database_name
select @LastBackUp=isnull(@LastBackUp,'19000101')
if @BackUpFinishDate>@LastBackUp
begin
select @SQL=' RESTORE LOG '+@DBname+' FROM '+@BackupFile
select @SQL=@SQL+' WITH FILE = '+cast(@Position as varchar(3))+', STANDBY= '''+@UndoFile+''' '
exec( @SQL)
-- print @SQL -- enable this line instead of the one above to see what would be run rather than to run it!
end
fetch next from cFiles into @Position, @BackUpFinishDate
end
close cFiles
deallocate cFiles
drop table #BackupContents
Tony
May 14, 2004 at 5:19 am
Here is another procedure - is's restoring the TA-logs depending on the current-LSN in the recovery-database:
Note: its restoring logs of an original database named 'SITESQL' -- You should change that name
/* **********************************************************************************
Procedure to restore TA-Logs into the Database
Params: name of the file containing the TA-log(s)
timelag in minutes for the recovery-database
author: Karl Klingler
date: 22. April 2004
********************************************************************************** */
CREATE PROCEDURE restore_log_backups_sitesql
@backupfile sysname,
@stop int
AS
-- declare variables
declare @file smallint
declare @lsn decimal
declare @minlsn decimal
declare @maxlsn decimal
declare @stopat sysname
declare @msg nvarchar(2000)
declare @cmd nvarchar(2000)
DECLARE @oldfile int
declare @backupstartdate datetime
declare @hexlsn nvarchar(22)
-- set defaults
select @lsn = 0
select @minlsn = 0
select @maxlsn = 0
select @oldfile = 0
Print '--- Beginne Transaktionslogs zu Restoren...'
-- convert timelag in minutes to datetime
select @stopat= dateadd(mi,-@stop,getdate())
-- temporäre Tabellen erstellen
--drop table #dblog1
--drop table #backupfile_header
select top 0 * into #dblog1 from ::fn_dblog( default, default )
CREATE TABLE #backupfile_header
(
BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime, Compressed tinyint, Position smallint, DeviceType tinyint,
UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0),
FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime,
SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int,
SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier,Collation nvarchar(128)
)
WAITFOR DELAY '00:00:05'
-- get headerinfo from backupfile into table #backupfile_header
insert #backupfile_header exec ('restore headeronly from disk = ''F:\Logshipping\sitesql\' + @backupfile + ''' ' )
WAITFOR DELAY '00:00:05'
-- get current LSN of recovery-database
insert #dblog1 exec (' use sitesql select * from ::fn_dblog( default, default )')
select @hexlsn = (select min([Current LSN]) from #dblog1)
select @lsn = master.dbo.lsn2dec(@hexlsn)
Print 'Aktuelle LSN der Datenbank ist ' + convert(nvarchar,@lsn) + '.'
-- test if backupfile contains the needed TA-backup
select @minlsn = (select top 1 FirstLsn from #backupfile_header where BackupName = 'SITESQL' order by FirstLsn)
select @maxlsn = (select top 1 LastLsn from #backupfile_header where BackupName = 'SITESQL' order by LastLsn desc)
select @msg = 'Die aktuelle LSN der DB lautet ' + rtrim(convert(char,@lsn)) + ', in der Backupdatei ' + @backupfile + ' sind Lsn''s von ' + rtrim(convert(char,@minlsn)) + ' bis ' + rtrim(convert(char,@maxlsn)) + ' vorhanden!'
-- if not raise hell about it
if @lsn @maxlsn
RAISERROR ( @msg,16,1) with LOG, NOWAIT
else print @msg
-- for all valid TA-backups in the backupfile: recover them, but only up until timelag
while not ((select Position from #backupfile_header where BackupName = 'SITESQL' and FirstLSN@lsn)) is NULL
begin
-- get current LSN of recovery-database
delete from #dblog1
insert #dblog1 exec (' use sitesql select * from ::fn_dblog( default, default )')
-- select @hexlsn = (select min([Current LSN]) from master.dbo.dblog where Operation = 'LOP_BEGIN_RECOVERY')
select @hexlsn = (select min([Current LSN]) from #dblog1)
select @lsn = master.dbo.lsn2dec(@hexlsn)
Print ''
Print 'Aktuelle LSN der Datenbank ist ' + convert(nvarchar,@lsn) + '.'
-- get fileposition
select @file = (select Position from #backupfile_header where BackupName = 'SITESQL' and FirstLSN@lsn)
-- same position twice means problems, break
if @oldfile = @file begin
Print '--- Restore fertig, es wurde zwei mal versucht, die selbe Fileposition zu restoren!'
break
end
-- get and print some info about current recovery
select @minlsn = (select FirstLsn from #backupfile_header where BackupName = 'SITESQL' and position = @file)
select @maxlsn = (select LastLsn from #backupfile_header where BackupName = 'SITESQL' and position = @file)
select @backupstartdate = (select BackupStartDate from #backupfile_header where BackupName = 'SITESQL' and position = @file)
Print 'Aktuelle Backup-Fileposition ist '+rtrim(convert(char,@file))+', Anfangs-LSN ist '+rtrim(convert(char,@minlsn)) + ', End-LSN ist ' + rtrim(convert(char,@maxlsn)) + '.'
Print 'Stopat ist "' + @stopat + '", BackupStartDate ist "' + rtrim(convert(varchar,@backupstartdate)) + '".'
if @backupstartdate > dateadd(mi,-@stop,getdate()) Begin
Print '--Dieser Backup muss nicht mehr Restored werden, er wurde um "' + rtrim(convert(varchar,@backupstartdate)) + '" erstellt, '
Print 'die Rücksicherung geht nur bis "' + @stopat + '". Restore wird beendet...'
break
end
-- kick all users out of the recovery-db
exec master..user_trennen 'SiteSQL'
WAITFOR DELAY '00:00:05'
-- do the actual recovery
set @cmd = 'RESTORE LOG SITESQL FROM DISK = ''f:\Logshipping\sitesql\' + @backupfile + '''
WITH DBO_ONLY, STANDBY = ''e:\undo_SITESQL.ldf'', STOPAT = ''' + @stopat + ''', FILE = ' + convert(varchar,@file)
Print 'SQL-Command: "' + @cmd + '".'
EXEC (@cmd)
WAITFOR DELAY '00:00:05'
-- memorize fileposition
select @oldfile = @file
end
drop table #dblog1
drop table #backupfile_header
-- now no more backups in this backupfile
Print 'In der aktuellen Backupdatei ' + @backupfile + ' sind keine weiteren TA-Logs mehr nachzuziehen!. ' + convert(nvarchar,getdate())
Print '--- Ende des Transaktionslog-Restore...'
GO
Needs the following functions:
CREATE FUNCTION Lsn2Dec(@hexlsn nvarchar(22))
RETURNS decimal AS
BEGIN
declare @lsn decimal
-- select @hexlsn = (select min([Current LSN]) from master.dbo.dblog where Operation = 'LOP_BEGIN_RECOVERY')
select @lsn = master.dbo.HexToInt(convert(nchar,left(@hexlsn,patindex('%:%',@hexlsn)-1)))
select @lsn = @lsn * 10000000000
select @lsn = @lsn + master.dbo.HexToInt(substring(@hexlsn,
patindex('%:%',@hexlsn)+1,
charindex(':', @hexlsn,patindex('%:%',@hexlsn)+1)-patindex('%:%',@hexlsn)-1))
select @lsn = @lsn * 100000 + master.dbo.HexToInt(substring(@hexlsn,charindex(':', @hexlsn,patindex('%:%',@hexlsn)+1)+1,len(@hexlsn)))
return @lsn
END
--HexToInt (Sign)
create function HexToInt(@vsData char(8))
RETURNS int AS
begin
declare @iDataLength int
declare @iDataLengthM1 int
declare @iResult int set @iResult = 0
declare @i int set @i = 0
declare @iTemp int set @iTemp = 1
declare @iTempMax int
set @iDataLength=len(@vsData)
if not @vsData like replicate('[0-9a-fA-F]',@iDataLength) return NULL
set @iDataLengthM1=@iDataLength-1
while @i < @iDataLength begin
if @i=@iDataLengthM1 begin
set @iTempMax=(ASCII(substring(@vsData, @iDataLength - @i, 1))&79)%55
set @iResult =(@iResult+(@iTempMax&7)*@iTemp)
if (@iTempMax&8)=8 set @iResult=@iResult-(Power(16,@iDataLengthM1)-1)*8-8
end else begin
set @iResult =@iResult+((ASCII(substring(@vsData, @iDataLength - @i, 1))&79)%55)*@iTemp
end
set @i = @i + 1
if @i<8 set @iTemp = 16 * @iTemp
end
Return @iResult
end
/* **********************************************************************************
Procedure to kick users out of database
Params: name of the database
author: Karl Klingler (actually got it from somewhere else...)
date: 22. April 2004
********************************************************************************** */
CREATE PROCEDURE user_trennen
@dbname sysname
AS
DECLARE @dbid int, @spid int, @execstr varchar(15), @waittime varchar(15), @final_chk int
--Getting the database_id for the specified database
SET @dbid = DB_ID(@dbname)
--Get the lowest spid
TryAgain:
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid)
WHILE @spid IS NOT NULL
BEGIN
--To avoid the KILL attempt on own connection
IF @spid @@SPID
BEGIN
--Killing the connection
SET @execstr = 'KILL ' + LTRIM(STR(@spid))
EXEC(@execstr)
END
--Get the spid higher than the last spid
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid AND spid > @spid)
END
SET @final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid = @dbid)
--New connections popped up, or killed connections aren't cleaned up yet, so try killing them again
IF (@final_chk > 1)
BEGIN
RAISERROR ('Job User rausschmeissen war nicht komplett erfolgreich.', 16, 1)
GOTO TryAgain
END
/* ++++++++++++++++++++++++ END of Procedure user_trennen +++++++++++++++++ */
GO
Best regards
karl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply