数据库备份的存储过程（写在master库中的恢复存储过程）（执行文件夹自动创建）

存储过程内容为（修改数据库名就可以了）：

USE [YingyuYubingBaogao2023]
GO
/****** Object:  StoredProcedure [dbo].[BackupDB]    Script Date: 2024/4/3 15:55:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER  PROCEDURE [dbo].[BackupDB] 
	@DBName nvarchar(40),
    @FileNameOut nvarchar(50) output, --- 文件名输出
	@FilePathAll nvarchar(100) output, --文件路径输出
	@FilePathSetIn nvarchar(250) --文件路径输出
AS  
 
exec SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1;

RECONFIGURE WITH OVERRIDE; 

exec SP_CONFIGURE 'XP_CMDSHELL',1;

RECONFIGURE WITH OVERRIDE; 


---------------------------
--检查文件夹是否存在 不存在则创建
---------------------------
DECLARE @PATH VARCHAR(255);  --路径
DECLARE @PathLen int;
set @PathLen= LEN(@FilePathSetIn);--获取指定的路径长度
if (@PathLen>0 )--如果长度大于零就这个路径文件夹
	begin
	SET @PATH =@FilePathSetIn+'\';
	end 
else 
	begin 
	SET @PATH = 'C:\DBBack\'+@DBName+'_BAK\'+CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR)+'\';
	end 

SET @FileNameOut=CONVERT(VARCHAR, GETDATE(), 112)+CAST(DATEPART(hour ,GETDATE()) AS VARCHAR)+'.bak';--生成文件名称

DECLARE @TEMP TABLE(A INT,B INT,C INT); --建立一个虚拟表 用来判断文件夹是否存在
INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @PATH;
IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1)
BEGIN
    --因为XP_CMDSHELL 函数的参数不允许使用变量拼接  所以使用EXEC(SQL)的方法
    DECLARE @EX NVARCHAR(255);
    SET @EX='EXEC XP_CMDSHELL ''MKDIR '+@PATH+'''';
    EXEC(@EX);
END
---------------------------
--备份数据库
---------------------------
--DECLARE @BAKFILE NVARCHAR(255);
SET @FilePathAll = @PATH + @FileNameOut;
BACKUP DATABASE @DBName TO DISK = @FilePathAll WITH COMPRESSION;
--------------------------
 
 exec SP_CONFIGURE 'XP_CMDSHELL',0;

RECONFIGURE WITH OVERRIDE; 
 
exec  SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0;

RECONFIGURE WITH OVERRIDE; 

应用方式为：

/// <summary>
        /// 备份数据库（命名为）BackupDB的存储过程（默认在C:\\DBBack文件夹下）
        /// </summary>
        /// <param name="FileName">自动备份的数据库名</param>
        /// <param name="FilePath">备份文件的地址</param>
        /// <param name="FilePathSet">自己手动设定的备份地址</param>
        /// <returns></returns>

        public static bool BackThisDB(out string FileName, out string FilePath,string FilePathSet="")
        {
            string DBName = GetThisDBName();
            string sql = @"BackupDB";
            var p = new DynamicParameters();
            p.Add("@DBName", DBName, DbType.String);
            p.Add("@FilePathSetIn", FilePathSet, DbType.String);
            p.Add("@FileNameOut", null, DbType.String, ParameterDirection.Output, 30);
            p.Add("@FilePathAll", null, DbType.String, ParameterDirection.Output, 200); 
            try
            {
                var rows = Execute(sql, p, null, null, CommandType.StoredProcedure);
                FileName = p.Get<string>("@FileNameOut");
                FilePath = p.Get<string>("@FilePathAll");
                return true; 
            }
            catch (Exception ex)
            {
                FileName = "";
                FilePath = "";
                LogWrite.WriteLog(ex.Message);
                return false;
            }
        }

当然也可以直接使用 数据库操作执行 即可
大部分灵感来自
https://www.cnblogs.com/iiwen/p/7650118.html
使用方法：

string SetPathStr = @"D:\DATAS\FujianS\YingyuYubingBaogao2023\2024-04" ;
new dapperDBHelper.BackThisDB(Out FileName,out FilePath,SetPathStr );

如果 SetPathStr 为空或者不传递，则默认为c盘下自动生成的文件夹

数据库 恢复的存储过程（写在master数据库）：

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[KillspidAndRestoreDB]    Script Date: 2022-05-25 17:09:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[KillspidAndRestoreDB] 
 @DBName varchar(80),---如果有出错的情况，请确定此位置长度是够的
 @backFilePath nvarchar(250) 
as
begin
--------------杀掉进程
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
          select spid from sysprocesses where dbid=db_id('''+@DBName+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
-----数据库恢复
restore database @DBName from disk = @backFilePath  with replace----数据库恢复操作
 
end

如果以恢复的方法无效的话使用下面方法一定可以

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[RestoreDB]    Script Date: 2023/8/12 17:19:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[RestoreDB] 
 @DBName varchar(40),
 @backFilePath nvarchar(150) 
as
begin
--1.1修改为单用模式
 exec(N'ALTER DATABASE '+@DBName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
--1.2结束链接进程
    DECLARE @kid varchar(max)  
    SET @kid=''  
    SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10))  FROM master..sysprocesses  
    WHERE dbid=DB_ID(@DBName)  ;
    EXEC(@kid) ;
--2.执行还原语句
   restore database @DBName from  disk=@backFilePath
   with replace  --覆盖现有的数据库
--3.重置数据库为多用户模式
 exec(N'ALTER DATABASE '+@DBName+' SET MULTI_USER WITH ROLLBACK IMMEDIATE'); 
end

调取方法：

new dapperDBHelper.RestorDB(FileName);