使用T-SQL进行数据库备份并检查该备份文件是否存在且作出相应处理

本文链接：https://blog.youkuaiyun.com/i_like_cpp/article/details/5918530

本文介绍如何使用T-SQL进行数据库备份，并通过检查备份文件是否存在来避免数据重复累加的问题。提供了一种方法，当备份文件已存在时，自动按日期重新命名备份文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用T-SQL进行数据库备份并检查该备份文件是否存在且作出相应处理

USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'E:/开发软件/数据库/SQl2005示例数据库/AdvWorksData.bak'
BACKUP DATABASE AdventureWorks
TO AdvWorksData

1.备份数据库语句：

EXEC sp_addumpdevice 'disk','数据库备份名称','文件路径'

BACKUP DATABASE 数据库名称
TO 数据库备份名称

2.sp_addumpdevice

代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [sys].[sp_addumpdevice]    -- 1995/09/07 12:01
    @devtype varchar(20),                -- disk, tape, or virtual_device
    @logicalname   sysname,                -- logical name of the device
    @physicalname nvarchar(260),        -- physical name of the device
    @cntrltype smallint = null,        -- obsolete: controller type - ignored.
    @devstatus varchar(40) = null      -- obsolete: device characteristics -ignored
as
    declare @type_enum    smallint -- devtype enumeration value
    declare @returncode int
    declare @devtypeIn varchar(20)
    select @devtypeIn = @devtype
           ,@devtype = LOWER (@devtype collate Latin1_General_CI_AS)
    -- An open txn might jeopardize a recovery.
    set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sys.sp_addumpdevice')
        return (1)
    end

    -- You must be SA to execute this sproc.
    if (not is_srvrolemember('diskadmin') = 1)
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- Check out the @devtype.
    select @type_enum = (case @devtype
        when 'disk'                then 2
        when 'tape'                then 5
        when 'virtual_device'    then 7
        end)

    if @type_enum is null
    begin
        raiserror(15044,-1,-1,@devtypeIn)
        return (1)
    end

    -- Check the args are not NULL.
    if @logicalname is null
    begin
        raiserror(15045,-1,-1)
        return(1)
    end

    -- Check to see that the @logicalname is valid.
    EXEC @returncode = sys.sp_validname @logicalname
    if @returncode <> 0
        return(1)

    if @physicalname is null
    begin
        raiserror(15046,-1,-1)
        return(1)
    end

    -- Prohibit certain special english words from being logical names.
    if (@logicalname IN ('disk', 'tape', 'virtual_device'))
    begin
        raiserror(15285,-1,-1,@logicalname)
        return (1)
    end

    BEGIN TRANSACTION
    -- Make sure that a device with @logicalname doesn't already exist.
    -- Always turn on the dump status bit, ignore @skip_tape (not in use)
    EXEC %%Device().NewDevice(Name = @logicalname, PhysicalName = @physicalname,
        Type = @type_enum, Size = 0)
    if @@error <> 0    -- duplicate logical name
    begin
        ROLLBACK TRANSACTION
        raiserror(15026,-1,-1,@logicalname)
        return (1)
    end

    -- Make sure physical file name would be unique among devices.
    if (select count(*) from master.dbo.sysdevices where phyname = @physicalname) > 1
    begin
        ROLLBACK TRANSACTION
        raiserror(15061,-1,-1,@physicalname)
        return (1)
    end

COMMIT TRANSACTION

return (0) -- sp_addumpdevice

3.这样备份有一个弊端。如果没有修改数据库备份名称，多次使用原有备份的名称，会将数据累加到该原有备份文件上面，比如该数据库第一次备份是100M，还是使用此名称备份下次就是200M，300M....,这样就要判断该备份文件是否存在！

代码
USE master
GO
declare @num int --申明一个接受返回值的变量
EXEC xp_fileexist 'E:/开发软件/数据库/SQl2005示例数据库/AdvWorksData.bak',@num output -- 执行文件存在否的验证存在返回1 不存在返回0
if(@num = 1) --如果存在就给出提示或做其他功能的实现
begin
print '备份文件已经存在'
end
else --该文件不存在执行备份操作
begin
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'E:/开发软件/数据库/SQl2005示例数据库/AdvWorksData.bak'
BACKUP DATABASE AdventureWorks
TO AdvWorksData
end

改进，如果该备份文件已经存在使用当前系统日期时间作为文件名的一部分，这样每次备份几乎就不可能出现同名数据累加的现象了

代码
USE master
GO
declare @num int --申明一个接受返回值的变量
EXEC xp_fileexist 'E:/开发软件/数据库/SQl2005示例数据库/AdvWorksData.bak',@num output -- 执行文件存在否的验证存在返回1 不存在返回0
if(@num = 1) --如果存在就给出提示或做其他功能的实现
begin
declare @file varchar(120),@name varchar(30)
set @file = 'E:/开发软件/数据库/SQl2005示例数据库/AdvWorksData_'+ replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-','_'),' ','_'),':','')+'.bak'
set @name = 'AdvWorksData'+CONVERT(VARCHAR(30),GETDATE(),9)
print '备份文件已经存在，自动按照日期重新命名进行备份'
EXEC sp_addumpdevice 'disk',@name , @file
BACKUP DATABASE AdventureWorks
TO @name
end
else --该文件不存在执行备份操作
begin
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'E:/开发软件/数据库/SQl2005示例数据库/AdvWorksData.bak'
BACKUP DATABASE AdventureWorks
TO AdvWorksData
end