Binaryworld - How to automate your system database backup task using script. ... [ SQL Server -> Backup-Restore ]

The following script will show you some powerful technique to script backup task for system/user database. Example also shows how to create one file which contains several backups which can expire at specified date.

Click here to copy the following block

--drop table #tmpDevice

create table #tmpDevice (device_name sysname, physical_name varchar(255), desription varchar(255), status int, cntrltype smallint, size int)

--//get exisitng backup devices
insert into #tmpDevice
exec sp_helpdevice

--//find 'Daily_Sys_Full_Bak'
declare @count int
select @count = count(*) from #tmpDevice where charindex('Daily_Sys_Full_Bak', device_name) > 0

--select * from #tmpdevice where charindex('Daily_Sys_Full_Bak', device_name) > 0

--//drop 'Daily_Sys_Full_Bak' device if already exists
declare @ret int
if @count > 0
begin
exec @ret = sp_dropdevice 'Daily_Sys_Full_Bak'
end

EXEC sp_addumpdevice 'disk', 'Daily_Sys_Full_Bak', 'c:\BW_Sys_Full_111705.bak'

--//Following DB backups will be written to Disk dump named "Daily_Sys_Full_Bak".

BACKUP DATABASE master TO Daily_Sys_Full_Bak WITH Name = 'MASTER_FULL' , INIT --//Any new backup overwrites the exisiting backup for master DB
BACKUP DATABASE msdb TO Daily_Sys_Full_Bak WITH Name = 'MSDB_FULL', INIT --//Any new backup overwrites the exisiting backup for msdb DB
--BACKUP DATABASE msdb TO Daily_Sys_Full_Bak WITH Name = 'MSDB_FULL', RETAINDAYS =2 --//This DB backup expires after 2 days
--BACKUP DATABASE msdb TO Daily_Sys_Full_Bak WITH Name = 'MSDB_FULL', EXPIREDATE ='11/19/2005' --//This DB backup expires on 11/19/2005