September 10, 2008 at 2:25 pm
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
@szCmd varchar(4000)
@iMissingPars varchar(10) 0
@szBackupLocation varchar(1000)
@Bkfile1 varchar(1000)
@Bkfile2 varchar(1000)
@Bkfile3 varchar(1000)
@Bkfile4 varchar(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
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