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

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating