Backup on the fly based on previous backups

Question

Post reply

Backup on the fly based on previous backups

jack-311987

SSC Journeyman

Points: 95
More actions
February 18, 2009 at 5:15 pm

#206717

Comments posted to this topic are about the item Backup on the fly based on previous backups

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

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

macherla.harish SSC Enthusiast Points: 133 More actions · Answer 1

This Code Same iam Paste in Sqlserver2005 Backup on the fly based on previous backups But Iam Getting Error

declare @Physical_device_name nvarchar(500)

declare @backup_path nvarchar(500)

declare @i int

declare @backup_command nvarchar(1000)

declare @date_string nvarchar(100)

select top 1

@Physical_device_name = bumf.physical_device_name

from

master.dbo.sysdatabases sysdb

left join

(

select database_name, max(backup_set_id) maxbusid

from msdb..backupset

where type = 'D' and @@servername like '%' + machine_name + '%' --- server has instance, machine

and media_set_id in (select media_set_id from msdb.dbo.backupmediafamily where device_type in (2,102) )

group by database_name

) maxbus

on maxbus.database_name = sysdb.name

join

msdb..backupset bus on

bus.backup_set_id = maxbus.maxbusid

join

msdb.dbo.backupmediafamily bumf on

bumf.media_set_id = bus.media_set_id

and bumf.device_type in (2,102) -- disk

where sysdb.name =db_name()

order by bus.backup_finish_date desc

if @Physical_device_name IS NOT NULL

????

????BEGIN

????--tells you where to cut it off, grabs the last slash

????????select @i = charindex('\', reverse(@Physical_device_name) )

???--now just get the path before the last slash

????????

????????select @backup_path = left(@Physical_device_name, len(@Physical_device_name)-@i)

????

????????select @date_string = replace(replace(replace(convert(varchar(16),getdate(),120),':',''),space(1),space(0)),'-',space(0))

????????set @backup_command = 'backup database ' + db_name() + ' to disk=' + char(39) + @backup_path +'\' + db_name() + '_diff_' + @date_string + '.bak' + char(39) + ' with differential'

????????

????????print @backup_command

????

????????exec(@backup_command)

????END

ELSE

????BEGIN

????????raiserror('Cannot determine backup directory',16,1)

????END

????end

????

HI Friends iam geting Error

In IF Condition Place :

Please Tell me :

Msg 102, Level 15, State 1, Line 32