SqlServer教程

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

本文主要是介绍关于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

　　

这篇关于关于sqlserver分区数据库还原的sql语句的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航