数据库备份

来源：互联网发布：房产测绘软件下载编辑：程序博客网时间：2024/05/17 22:03

USE [TDCLIB]
GO
/****** Object: StoredProcedure [dbo].[pro_sysdbbak] Script Date: 01/17/2014 11:51:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pro_sysdbbak]
AS
    BEGIN
        DECLARE @filename NVARCHAR(500) ,
            @bkpath NVARCHAR(500) ,
            @dbname NVARCHAR(500)
        SELECT @bkpath = paramvalue
        FROM    xhs_SysParam
        WHERE   ParamName = 'dbbakpath'

        IF exists (select 1 from sysobjects where id = object_id(N'[dbo].[temp_xp_fileexist]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        BEGIN
        DROP TABLE temp_xp_fileexist

        create table temp_xp_fileexist(a bit,b bit,c bit)
        END

        insert into temp_xp_fileexist
  exec xp_fileexist @bkpath
        DECLARE @result VARCHAR(200)
  SELECT TOP 1 @result=CAST(a AS VARCHAR(50))+','+CAST(b AS VARCHAR(50))+','+CAST(c AS VARCHAR(50)) FROM temp_xp_fileexist
        IF @result = '0,0,1'
            BEGIN
                SELECT @bkpath = 'c:'
            END
        SELECT @dbname = DB_NAME()

        SET @filename = CONVERT(NVARCHAR, GETDATE(), 21)
        SET @filename = REPLACE(@filename, ' ', '-')
        SET @filename = REPLACE(@filename, ':', '-')
        SET @filename = REPLACE(@filename, '.', '-')
        SET @bkpath = @bkpath + '\' + @filename + '.bak'

        PRINT 'BACKUP DATABASE ''' + @dbname + ''' TO DISK = ''' + @bkpath
            + ''' WITH INIT'
        BACKUP DATABASE @dbname TO DISK = @bkpath WITH INIT

        UPDATE dbo.xhs_SysParam
        SET     paramvalue = @bkpath
        WHERE   ParamName = 'dbbakhist'
        SELECT '1:' + @bkpath
    END

0 0