Backup on the fly based on previous backups

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

  • 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

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 37

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 40

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 43

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 44

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 49

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 53

    Incorrect syntax near '?'.

    please give me Reply

    I want urgent

    Thanks&Regrads

    harishKumar.M

  • you got some special characters in there somehow. try pasting it into notepad first and remove the "boxes"

    -Jack

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

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