Hand made log shipping

  • Roust_m

    SSCoach

    Points: 17372

    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.

     

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    You can find details about backup and restore files by joining various backup* and restore* tables in MSDB.

  • Mike Good

    SSCertifiable

    Points: 7387

    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.

  • Tony Bater

    SSCrazy

    Points: 2150

    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

  • Karl Klingler

    SSCertifiable

    Points: 5869

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply