Backup on the fly based on previous backups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by jack » Backup on the fly based on previous backups

Backup on the fly based on previous backups

Rate Topic

Display Mode

Topic Options

Author

Message

jack-311987

Posted Wednesday, February 18, 2009 5:15 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 8:23 AM
Points: 55, Visits: 135

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

Post #659975

macherla.harish

Posted Wednesday, February 24, 2010 5:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 10, 2012 12:16 AM
Points: 6, Visits: 59

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

Post #871844

jack-311987

Posted Wednesday, February 24, 2010 10:12 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 8:23 AM
Points: 55, Visits: 135

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

Post #872042

« Prev Topic | Next Topic »

Permissions