关于sqlserver分区数据库还原的sql语句

--按顺序执行对应步骤
--第一步新建一个数据库(略)



--第二步新建分区的文件组与备份的数据库保持一致
alter database [LEMES_2021] add filegroup PROCESSDATA_2016
alter database [LEMES_2021] add filegroup PROCESSDATA_2017
alter database [LEMES_2021] add filegroup PROCESSDATA_2018
alter database [LEMES_2021] add filegroup PROCESSDATA_2019
alter database [LEMES_2021] add filegroup PROCESSDATA_2020
alter database [LEMES_2021] add filegroup PROCESSDATA_2021

--第三步新建分区文件并添加到对应的文件组
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2017_Q1', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q1.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2017]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2017_Q2', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2017]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2017_Q3', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q3.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2017]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2017_Q4', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q4.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2017]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2018_Q1', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q1.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2018]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2018_Q2', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2018]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2018_Q3', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q3.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2018]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2018_Q4', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q4.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2018]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2019_Q1', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q1.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2019]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2019_Q2', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2019]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2019_Q3', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q3.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2019]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2019_Q4', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q4.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2019]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2020_Q1', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q1.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2020]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2020_Q2', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2020]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2020_Q3', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q3.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2020]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2020_Q4', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q4.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2020]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2021_Q1', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q1.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2021]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2021_Q2', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2021]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2021_Q3', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q3.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2021]
ALTER DATABASE [LEMES_2021] ADD FILE ( NAME = N'PROCESSDATA_2021_Q4', FILENAME = N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q4.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PROCESSDATA_2021]


--第四步把数据库分区文件还原到对应的文件路径 MOVE后跟分区文件的路径
RESTORE DATABASE [LEMES_2021] FROM  DISK = N'E:/LEMES_DX-2022-07-30.bak' WITH  FILE = 1,
  MOVE N'LEMES' TO N'E:/2022DATA/LEMES_2021.mdf',
  MOVE N'PROCESSDATA_2017_Q1' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q1.mdf',
  MOVE N'PROCESSDATA_2017_Q2' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q2.mdf',
  MOVE N'PROCESSDATA_2017_Q3' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q3.mdf',
  MOVE N'PROCESSDATA_2017_Q4' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2017_Q4.mdf',
  MOVE N'PROCESSDATA_2018_Q1' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q1.mdf',
  MOVE N'PROCESSDATA_2018_Q2' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q2.mdf',
  MOVE N'PROCESSDATA_2018_Q3' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q3.mdf',
  MOVE N'PROCESSDATA_2018_Q4' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2018_Q4.mdf',
  MOVE N'PROCESSDATA_2019_Q1' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q1.mdf',
  MOVE N'PROCESSDATA_2019_Q2' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q2.mdf',
  MOVE N'PROCESSDATA_2019_Q3' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q3.mdf',
  MOVE N'PROCESSDATA_2019_Q4' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2019_Q4.mdf',
  MOVE N'PROCESSDATA_2020_Q1' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q1.mdf',
  MOVE N'PROCESSDATA_2020_Q2' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q2.mdf',
  MOVE N'PROCESSDATA_2020_Q3' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q3.mdf',
  MOVE N'PROCESSDATA_2020_Q4' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2020_Q4.mdf',
  MOVE N'PROCESSDATA_2021_Q1' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q1.mdf',
  MOVE N'PROCESSDATA_2021_Q2' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q2.mdf',
  MOVE N'PROCESSDATA_2021_Q3' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q3.mdf',
  MOVE N'PROCESSDATA_2021_Q4' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2021_Q4.mdf',
  MOVE N'PROCESSDATA_2016_Q4' TO N'E:/2022DATA/PROCESSDATA/PROCESSDATA_2016_Q4.mdf',
  MOVE N'LEMES_log' TO N'E:/2022DATA/LEMES_2021_log.ldf',
  NOUNLOAD,
  STATS = 10
GO