使用存储过程备份SqlServer数据库

存储过程代码如下所示：

CREATE PROCEDURE [dbo].[SP_BackupDB]
(
@BackPath NVARCHAR(200),                    --备份路径，如：D:\Backup\
@BackDbName NVARCHAR(50),                   --需要备份的数据库名称
@BackName NVARCHAR(50)=@BackDbName OUTPUT,  --备份后的数据库名称，不需要.bak后缀。若不传，则等同于@BackDbName
@BackPathIsDate BIT=1,                      --备份路径是否需要添加日期文件夹，默认添加
@BackNameIsTime BIT=1                       --备份后的数据库名称是否需要追加当前时间，默认追加
)
AS
BEGIN  

IF(@BackPathIsDate=1)
BEGIN
SET @BackPath=@BackPath + CONVERT(VARCHAR, GETDATE(), 112) +'\';
END

---------------------------创建文件夹 Begin---------------------------
DECLARE @FolderSQL NVARCHAR(MAX),
@Return INT= 0;

SET @FolderSQL = N'EXEC sp_configure ''show advanced options'',1 
    RECONFIGURE WITH OVERRIDE;

    EXEC sp_configure ''xp_cmdshell'',1 
    RECONFIGURE WITH OVERRIDE;

    EXEC @Return=xp_cmdshell ''mkdir ' + @BackPath
+ ''',NO_OUTPUT   --调用DOS命令创建文件夹;

    EXEC sp_configure ''xp_cmdshell'', 0 
    RECONFIGURE WITH OVERRIDE;

    EXEC sys.sp_configure N''show advanced options'', N''0''
    RECONFIGURE WITH OVERRIDE;
';  
--PRINT @FolderSQL;  

EXEC sp_executesql @FolderSQL, N'@Return INT OUTPUT', @Return OUTPUT;  
--SELECT  @Return;   

--PRINT @Return
---------------------------创建文件夹 End---------------------------

---------------------------备份数据库 Begin---------------------------
DECLARE @NowTime VARCHAR(100);
DECLARE @BackUpName VARCHAR(100);

SET @NowTime=CONVERT(VARCHAR, GETDATE(), 112)+REPLACE(CONVERT(VARCHAR, GETDATE(), 108),':','');

/*
    IF(LEN(@BackName)<=0)
        BEGIN
            SET @BackName=@BackDbName;
        END
*/

IF(@BackNameIsTime=1)
BEGIN
SET @BackName=@BackName +'_'+ @NowTime;
END
SET @BackName=@BackName+'.bak';

SET @BackUpName=@BackPath + @BackName;

BACKUP DATABASE @BackDbName
TO DISK=@BackUpName
WITH INIT;
---------------------------备份数据库 End---------------------------

SELECT @BackName BackName,@BackPath BackPath,@BackPath + @BackName FullBackName;

END

GO