SQL Server2019数据库备份与还原脚本(批量备份)

原作者: [db:作者] 来自: [db:来源] 收藏邀请

前言最近公司服务器到期，需要进行数据迁移，而数据库属于多而繁琐，通过图形化界面一个一个备份所需时间成本很大，所以想着写一个sql脚本来执行。

开始

数据库单个备份
数据库批量备份
数据库还原
数据库还原报错问题记录
总结

1.数据库单个备份

图形化界面备份这里就不展示了，可以自行百度，下面直接贴代码

USE MASTER
IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE BackupDataProc
go
create proc BackupDataProc
@FullName Varchar(200)--入参（数据库名）
as
begin
Declare @FileFlag varchar(50)
Set @FileFlag='C:\myfile\database\'+@FullName+'.bak'--备份到哪个路径（C:\myfile\database\）根据自己需求来定
BackUp DataBase @FullName To Disk=@FileFlag with init--核心代码
end

exec BackupDataProc xxx

执行成功后便会生成一个.bak文件到指定文件夹中，如图

2.数据库批量备份（时间有点长，请等待）

USE MASTER
if exists(SELECT * FROM sys.types WHERE name = 'AllDatabasesNameType')
drop type AllDatabasesNameType
go
create type AllDatabasesNameType as table--自定义表类型用于存储数据库名称
(
rowNum int ,
name nvarchar(60),
filename nvarchar(300)
)
go
IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BachBackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE BachBackupDataProc
go
create proc BachBackupDataProc
@filePath nvarchar(300)--入参，备份时的目标路径
as
begin
Declare @AllDatabasesName as AllDatabasesNameType --用于存储系统中的数据库名
Declare @i int --循环变量
insert into @AllDatabasesName(name,filename,rowNum) select name,filename,ROW_NUMBER() over(order by name) as rowNum from sysdatabases where name not in('master','tempdb','model','msdb') --赋值
set @i =1
--循环备份数据库
while @i <= (select COUNT(*) from @AllDatabasesName)
begin
Declare @FileFlag varchar(500)
Declare @FullName varchar(50)
Select @FullName =name from @AllDatabasesName where rowNum = @i
Set @FileFlag=@filePath+@FullName+'.bak'
BackUp DataBase @FullName To Disk=@FileFlag with init
set @i = @i + 1
end
end

exec BachBackupDataProc 'C:\myfile\database\'

执行结果效果如下图：

3.数据库还原

IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[ReductionProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE ReductionProc
go
create proc ReductionProc
@Name nvarchar(200)--入参 数据库名称
as
begin
Declare @DiskName nvarchar(500)
Declare @FileLogName nvarchar(100)
Declare @FileFlagData nvarchar(500)
Declare @FileFlagLog nvarchar(500)
Set @FileLogName = @Name + '_log'
Set @DiskName = 'C:\myfile\database\'+@Name+'.bak' ---（源）备份文件路径
Set @FileFlagData='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@Name+'.mdf'---（目标）指定数据文件路径
Set @FileFlagLog='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@FileLogName+'.ldf'---目标）指定日志文件路径
RESTORE DATABASE @Name --为待还原库名
FROM DISK = @DiskName ---备份文件名
WITH MOVE @Name TO @FileFlagData, ---指定数据文件路径
MOVE @FileLogName TO @FileFlagLog, ---指定日志文件路径
STATS = 10, REPLACE
end
go

exec ReductionProc xxx

执行后便能还原库（我是拿这三个库做测试，截的图可能没什么变化，你们可以尝试下）

4.数据库还原报错问题记录

当然还原的过程可能会遇到一些问题，比如：

1.版本不一样
2.SQL Sql 逻辑文件'XXXXX ' 不是数据库'YYY'的一部分。请使用 RESTORE FILELISTONLY 来列出逻辑文件名。

版本的话我试过了，高版本可以向下兼容，但是低版本不能向上兼容，可以统一版本来解决（如有更好的解决方案欢迎打扰）
第二个问题呢就是脚本中‘MOVE' 他只能跟逻辑名，而有些数据库的逻辑名并不是数据库名称，所以需要替换一下，

下面是查询数据库逻辑名的sql语句：

USE MASTER
restore filelistonly from disk='D:\sql201database\Sence.bak'--根据自己的需求要变更路径

如图，

对于这些逻辑名与数据库名称不一致的情况可以单独拿出来重新执行一下即可：

USE MASTER --这里注意要使用MASTER，以免出现待还原库被占用的情况
RESTORE DATABASE Sence --为待还原库名
FROM DISK = 'D:\sql201database\Sence.bak' ---备份文件名
WITH MOVE 'Sence_Guangxi' TO 'D:\Database\Data\Sence.mdf', ---指定数据文件路径
MOVE 'Sence_Guangxi_log' TO 'D:\Database\Data\Sence_log.ldf', ---指定日志文件路径
STATS = 10, REPLACE
GO