Technical Article

Backup on the fly based on previous backups

,

Run the script on the database you wish to backup and it creates a backup string that is stored in @backup_command and then executed. I've used it to run a differential backup as part of a rollout to multiple servers. As long as the database has been full backed up and the directory still exists etc, all is well.

 

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

Rate

(1)

You rated this post out of 5. Change rating

Share

Share

Rate

(1)

You rated this post out of 5. Change rating