March 4, 2009 at 10:31 am
Hello
My brain seems to be frozen at this point, Can anyone look at this code and tell me why the previous backups are being overwritten when I run this job
USE db_tools_backups
DECLARE @loc_folder_nmnVARCHAR (1500 )
declare @nw_folder_nmnvarchar(1000)
declare @nw_filenvarchar(1000)
,@rcINT
,@nameSYSNAME
,@loc_filenVARCHAR ( 2000 )
,@copycmd nvarchar(2000)
,@cmd nvarchar(1000)
,@verifystatement nvarchar(1000)
,@loc_err int
,@nw_err int
create table #nw_dirtree (subdirectory nvarchar(255), depth int)
create table #loc_dirtree (subdirectorynVARCHAR ( 255 ), depthINT)
DECLARE backup_curCURSOR FOR
SELECTname
FROMmaster.dbo.sysdatabases
WHEREname NOT IN ('master', 'model', 'msdb', 'tempdb')
ANDname NOT IN (SELECT database_nm FROM ignore_databases)
and left(name,1) = 'a' or left(name,1)= 'b' or left(name,1) ='c'
OPEN backup_cur
FETCH NEXT FROM backup_cur INTO @name
SET @loc_folder_nm = 'F:\Backups\mssql\dbserver\' + @name
set @nw_folder_nm ='\\10.23.243.109\backup\mssql\dbserver\' + @name
INSERT #loc_dirtree
EXEC master..xp_dirtree @loc_folder_nm
INSERT #nw_dirtree
EXEC master..xp_dirtree @nw_folder_nm
WHILE @@FETCH_STATUS = 0
BEGIN
SET @loc_folder_nm = 'F:\Backups\mssql\dbserver\' + @name + '\fullbkp'
set @nw_folder_nm = '\\10.23.243.109\backup\mssql\dbserver\' + @name +'\fullbkp'
IF NOT EXISTS (SELECT 1 FROM #loc_dirtree WHERE subdirectory = @name)
set @cmd = 'mkdir '+ @loc_folder_nm
EXECUTE master.dbo.xp_cmdshell @cmd
IF NOT EXISTS (SELECT 1 FROM #nw_dirtree WHERE subdirectory = @name)
set @cmd = 'mkdir '+ @nw_folder_nm
EXECUTE master.dbo.xp_cmdshell @cmd
SET @loc_file = @loc_folder_nm + '\' + @name + '_backup_' + CONVERT(NVARCHAR, GETDATE(), 112) + '.bak'
set @nw_file = @nw_folder_nm + '\' + @name + '_backup_' + CONVERT(NVARCHAR, GETDATE(), 112) + '.bak'
BACKUP DATABASE @name
TO DISK = @loc_file
WITHRETAINDAYS = 5
-- Check if the backup is valid on the local drive before copy
Set @verifystatement = 'restore verifyonly from disk = ''' + @loc_file +''''
--print (@verifystatement)
exec sp_executesql @verifystatement
Select @loc_err = @@error
if @loc_err <>0
begin
insert into tbl_local_backupverify(BackupDevice,BackupStatus,verifydate, BackupError, VerifyStatement)
values (@loc_file, 'Failed', getdate(), @loc_err, @verifystatement)
end
Set @copycmd = 'Move ' + '"'+ @loc_file + '"' + ' '+ '"' + @nw_folder_nm +'"'
exec master..xp_cmdshell @copycmd
-- Check if the backup on the network is valid after the copy
Set @verifystatement = 'restore verifyonly from disk = ''' + @nw_file +''''
exec sp_executesql @verifystatement
Select @nw_err = @@error
--print @nw_err
if @nw_err <>0
begin
insert into tbl_nw_backupverify(BackupDevice,BackupStatus,verifydate, BackupError, VerifyStatement)
values (@nw_file, 'Failed', getdate(), @nw_err, @verifystatement)
end
INSERT db_full_backups
VALUES (@name, @loc_file,@nw_file, GETDATE(), DATEADD(dd, 10, GETDATE()), NULL)
FETCH NEXT FROM backup_cur INTO @name
END
Drop Table #loc_dirtree, #nw_dirtree
--drop table
--END
CLOSE backup_cur
DEALLOCATE backup_cur
Any help is greatly appreciated
Thanks
Shri
March 4, 2009 at 10:42 am
Hi Shri
How many days of back up do u hod currently?? and is all previous days back up deleted when you run this script?? 🙂
March 4, 2009 at 12:07 pm
I was running the job every hour (doing some testing) the filename only had the date in itand hence it was overwriting everything for that day, I fixed it, it did give me a scare though
Thanks
Shri
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply