Post reply

Insert Top n records into variables

  • September 10, 2008 at 2:25 pm

    #197429

    Hi, This is my first post here. We're moving away from Litespeed to native SQL backups and have to use 4 backup files to get close to the same performance we had using lightspeed. My problem is we had several backup/restore sp jobs set up by previous DBAs and I'm having to modify them to account for this. I'm a relatively new DBA and don't do alot of coding in the position I'm in and have probably forgotten more stuff than I knew when i was studying this stuff. So my question is how can I get the restore job to find the latest 4 files and use them instead of only the one latest file? I think there's two ways to do it, maybe all the files could be put into one variable but I'm going the easier route of using 4 variables. I was thinking add TOP 4 to the select from the temp table but not sure how to put those into the variables. Here are sections of the code I'm currently working with, it's not finished and I can post the original code if it would help. '@szBackupFile' was the old variable, I'm replacing it with '@Bkfile1,2,3,4,etc' but not finished yet.

    declare

    @szCmdvarchar(4000)

    @iMissingParsvarchar(10) 0

    @szBackupLocationvarchar(1000)

    @Bkfile1varchar(1000)

    @Bkfile2varchar(1000)

    @Bkfile3varchar(1000)

    @Bkfile4varchar(1000)

    set @szBackupFile = ''

    if (@iMissingPars = 0) begin

    set @szCmd = 'dir ' + rtrim(@szBackupLocation) + '\' + rtrim(@szBackupName) + '_*.BAK /B /A-d'

    -- temporary tables with all full backups for given database

    create table #files (szFilename varchar(255) )

    insert into #files exec master.dbo.xp_CmdShell @szCmd

    delete #files where (szFilename is null)

    -- Name of the last full backup for given backup database

    select @szBackupFile = max(upper(szFilename))

    from #files

    drop table #files

    end

    set @Bkfile1 = rtrim(@szBackupLocation) + '\' + @Bkfile1

    set @Bkfile2 = rtrim(@szBackupLocation) + '\' + @Bkfile2

    set @Bkfile3 = rtrim(@szBackupLocation) + '\' + @Bkfile3

    set @Bkfile4 = rtrim(@szBackupLocation) + '\' + @Bkfile4

    print 'Backup Files used: ' + @Bkfile1 + ', ' + Bkfile2 + ', ' + Bkfile3 + ', ' + Bkfile4

    -- Restore full backup

    -- ===============================================================================================

    if (@iRetCode = 0) and (@iExecMode = 1) begin

    exec utils.dbo.usp_ClearConnecctions @szDatabase = @szDBName

    exec @iRetCode = master.dbo.xp_restore_database

    @database = @szDBName,

    @filename = @szBackupFile,

    @with = @szWith

    --,@with='NORECOVERY' --for DR Site T-LOG restore

    end

    -- ===============================================================================================

    Thanks

  • September 10, 2008 at 2:38 pm

    #869979

    I think I can use a cursor to do this.

Viewing 2 posts - 1 through 1 (of 1 total)

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