22,300
社区成员




CREATE PROCEDURE [dbo].[sp_BackUp] AS
DECLARE @DEVICENAME VARCHAR(16), @DEVICENAME_HDB VARCHAR(16), @DEVICENAME_YDB VARCHAR(16),
@DEVICENAME_WDB VARCHAR(16), @DISK VARCHAR(200), @COMMAND VARCHAR(200), @isdaily bit, @CURDATE DATETIME
SELECT @CURDATE=GETDATE()
if datepart(weekday,@CURDATE)<>1
BEGIN
SELECT @isdaily=1, @DEVICENAME = 'VTSDDB_D2'
END
else
SELECT @isdaily=0, @DEVICENAME = 'VTSDDB_W2'
--SELECT @DISK = 'd:\backupdb\'+@DEVICENAME+'.dat' ---这是原来的备份路径,备份在本地D盘。
SELECT @DISK = 'm:\'+@DEVICENAME+'.dat' ---这是我改变之后的路径,映射了一个M盘。
IF NOT EXISTS(SELECT * FROM master.dbo.sysdevices WHERE name = @DEVICENAME)
BEGIN
EXECUTE master.dbo.sp_addumpdevice 'disk', @DEVICENAME, @DISK
IF @@ERROR<>0
BEGIN
RAISERROR('Add backup device fail.', 16, 1)
RETURN
END
END
IF datepart(hour, @CURDATE) BETWEEN 1 AND 23
BEGIN
SELECT @DEVICENAME = 'VTSDDB_D2'
SELECT @COMMAND = 'BACKUP DATABASE DB1 TO ' + @DEVICENAME + ' WITH NOUNLOAD, STATS=10, INIT, NOSKIP, NAME=''DB1'''
EXECUTE(@COMMAND)
SELECT @COMMAND = 'BACKUP DATABASE DB2 TO ' + @DEVICENAME +' WITH NOUNLOAD, STATS=10, NOINIT, NOSKIP, NAME=''DB2'''
EXECUTE(@COMMAND)
SELECT @COMMAND = 'BACKUP DATABASE master TO ' + @DEVICENAME +' WITH NOUNLOAD, STATS=10, NOINIT, NOSKIP, NAME=''master'''
EXECUTE(@COMMAND)
SELECT @COMMAND = 'BACKUP DATABASE msdb TO ' + @DEVICENAME +' WITH NOUNLOAD, STATS=10, NOINIT, NOSKIP, NAME=''msdb'''
EXECUTE(@COMMAND)
SELECT @COMMAND = 'BACKUP DATABASE model TO ' + @DEVICENAME +' WITH NOUNLOAD, STATS=10, NOINIT, NOSKIP, NAME=''model'''
EXECUTE(@COMMAND)
END
GO
declare @s nvarchar(4000),@del nvarchar(4000)
SQL2000不支持max,自己改改
CREATE PROCEDURE sp_BackDB
WITH ENCRYPTION
AS
declare @s nvarchar(max),@del nvarchar(MAX),@Dt datetime
select @s='',@del='',@Dt=getdate()
select
@s=@s+
char(13)+'backup database '+quotename(Name)+' to disk =''F:\DBBak\'+Name+'_'+convert(varchar(8),@Dt,112)+'.bak'' WITH INIT;',
@del=@del+
char(13)+'exec master..xp_cmdshell '' del F:\DBBak\'+Name+'_'+convert(varchar(8),@Dt-3,112)+'.bak'', no_output;'
from master..sysdatabases
where name IN('master','msdb')--排除系统DB
order by dbid asc
exec (@del)
exec(@s)