数据库自动备份脚本

数据库自动备份脚本（Sql Server）

// This code will create new backup locations on disk on a daily / weekly / yearly basis

-- Declare variables

declare @count int, @max int, @day varchar(10), @path varchar(1000), @dbname varchar(55)
declare @device varchar(255), @physical varchar(1255), @lastbackup smalldatetime

-- Set count to start at database ID +1 
-- master	1
-- tempdb	2
-- model		3
-- msdb		4
-- pubs		5
-- Northwind	6

set @count = 7

-- Set maximum no of databases on server

--set @max = (select count(*) from master..sysdatabases) +1
set @max = 12

-- Set backup path

set @path='D:\SQL\backups\'

-- Get unique cycle number
-- For weekly cycle - datepart(dw,getdate())
-- For monthly cycle - datepart(dd,getdate())
-- For yearly cycle - datepart(dy,getdate())

set @day=datepart(dw,getdate())

-- While the starting number of databases is smaller than the maximum number
-- of databases:
-- 1.	Check if there is already a backup device made for this
-- day of the cycle. If there isn't create the device.
-- 2.	See if a backup has already been done for this day of the cycle. If 
-- it hasn't do a full backup, otherwise do a differential backup.
-- 3.	Repeat for next database.

while (@count < @max)
begin
	
	set @dbname = (select [name] from master..sysdatabases where dbid=@count)
	set @device=@dbname + '_' + @day
	
	if (select count(*) from master..sysdevices where name = @dbname + '_' + @day)=0 begin
		set @physical=@path+@device + '.bak'
		EXEC sp_addumpdevice 'disk', @device, @physical
	end
	
	begin
	
		set @lastbackup=(select max(backup_finish_date) 
		from msdb.dbo.backupmediafamily, msdb.dbo.backupset
		where msdb.dbo.backupset.media_set_id=msdb.dbo.backupmediafamily.media_set_id
		and logical_device_name=@device
		)
		
		if datepart(dd, @lastbackup)!=datepart(dd,getdate()) begin
			BACKUP DATABASE @dbname TO @device WITH INIT
		end
		else begin
			BACKUP DATABASE @dbname TO @device WITH DIFFERENTIAL
		end 
	
	end  
	
	set @count = @count + 1  

continue 
break

end