SQL Server 2019 データベースバックアップ＆リストアスクリプト（一括バックアップ）

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) - the entry (database name)
as
begin
Declare @FileFlag varchar(50)
Set @FileFlag='C:\myfile\database\'+@FullName+'.bak'--which path to backup to (C:\myfile\database\) according to your needs
BackUp DataBase @FullName To Disk=@FileFlag with init--core code
end

exec BackupDataProc xxx

USE MASTER
if exists(SELECT * FROM sys.types WHERE name = 'AllDatabasesNameType')
drop type AllDatabasesNameType
go
create type AllDatabasesNameType as table - custom table type for storing database names
(
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) - input, the destination path for the backup
as
begin
Declare @AllDatabasesName as AllDatabasesNameType - the name of the database used to store the system
Declare @i int -- loop variable
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') --assign value
set @i =1
--loop backup database
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\'

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)--input database name
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' ---(source) backup file path
Set @FileFlagData='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@Name+'.mdf' ---(target) Specify the data file path
Set @FileFlagLog='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@FileLogName+'.ldf'---(target) Specify the log file path
RESTORE DATABASE @Name - for the name of the library to be restored
FROM DISK = @DiskName --- the name of the backup file
WITH MOVE @Name TO @FileFlagData, ---specify data file path
MOVE @FileLogName TO @FileFlagLog, --- Specify log file path
STATS = 10, REPLACE
end
go

exec ReductionProc xxx

USE MASTER
restore filelistonly from disk='D:\sql201database\Sence.bak'--change the path according to your needs

USE MASTER -- use MASTER here to avoid having the database to be restored occupied
RESTORE DATABASE Sence -- for the name of the library to be restored
FROM DISK = 'D:\sql201database\Sence.bak' --- backup file name
WITH MOVE 'Sence_Guangxi' TO 'D:\Database\Data\Sence.mdf', --- specify the path to the data file
MOVE 'Sence_Guangxi_log' TO 'D:\Database\Data\Sence_log.ldf', --- Specify the path to the log file
STATS = 10, REPLACE
GO