关于sqlserver分区数据库还原的sql语句
2022/8/1 2:27:45
本文主要是介绍关于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语句的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-08Docker下的SqlServer发布订阅启用
- 2023-06-05Docker安装MS SQL Server并使用Navicat远程连接
- 2023-05-25深入浅出 SQL Server CDC 数据同步
- 2023-05-12通过空间占用和执行计划了解SQL Server的行存储索引
- 2023-04-24以SQLserver为例的Dapper详细讲解
- 2022-11-30SQL server高级函数查询
- 2022-11-26SQL SERVER数据库服务器CPU不能全部利用原因分析
- 2022-11-21SQL Server 时间算差值/常用函数
- 2022-11-20调试Archery连接SQL Server提示驱动错误
- 2022-10-22SQL Server 完整、差异备份+完整、差异还原(详细讲解,规避错误)