Insert Top n records into variables

• sqlhack

  SSC Veteran

  Points: 216

  More actions

  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

• sqlhack

  SSC Veteran

  Points: 216

  More actions

  September 10, 2008 at 2:38 pm

  #869979

  I think I can use a cursor to do this.