我有一个包含许多表的大型数据库。数字并不重要。我对 Server 2016 上的 MSSQL 2017 (14.0.2037.2) 拥有完全控制权。
我的大量数据都存储在两三个表中。想象一下,如果你在跟踪汽车,那么你的表里会有几千辆汽车,而每辆车一段时间后都会有数百万个数据点。实际上,我的 80% 的存储空间都被三个表占用了。
生产中没有问题。备份已完成,恢复测试成功,一切正常。
我希望能够偶尔将不太大的表拉到开发环境中进行故障排除,或者只是从其他表中获取一些真实数据。我不需要大表。这需要一个小时,并且必须通过文件传输服务(这会让我的数据库包含私人数据,而不受我的控制)。
为了简化问题,是否有一种简单的方法可以仅备份要在开发服务器上恢复的选定表?我想我即将尝试将大表移动到单独的文件中,然后复制剩余的小文件,但这当然不像正确的备份/恢复那样干净或无懈可击。
有 SQL 专家给出建议吗?
**编辑 - 运行了一段时间后,下面的答案在技术上是正确的,但最终却没有实际用处。我确实将大表放在自己的文件组中,这样我就可以单独备份文件,但是无论如何,我不能简单地取出较小的文件并在没有其大表的情况下将其恢复,否则会引入风险和手动填充。
“最快”的选项是在本地服务器上恢复数据库,然后截断不必要的表,然后备份并将该数据库恢复到开发环境中。
当然现在我已经使用了 Azure,所以这一切都是多余的。
去吧,Azure 人。
答案1
我的建议是在新的文件组上创建一个辅助数据库文件(.ndf 文件),并将一个或多个大表移动到新的文件组,并使用备份/恢复文件组功能。
创建新的数据库文件和文件组后,将表移动到新创建的文件组,如这文章。
下面您将看到使用从中恢复的文件组备份/恢复的完整演示这里。
USE [master]
GO
ALTER database [FGRestoreTEST] set offline with ROLLBACK IMMEDIATE;
DROP DATABASE [FGRestoreTEST]
GO
--Create sample database
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
NAME = N'FGRestoreTEST'
,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
,SIZE = 5120 KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024 KB
)
,FILEGROUP [secondaryFilegroup] (
NAME = N'secondaryFilegroup'
,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
,SIZE = 5120 KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024 KB
) LOG ON (
NAME = N'FGRestoreTEST_log'
,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
,SIZE = 2048 KB
,MAXSIZE = 2048 GB
,FILEGROWTH = 10 %
)
GO
--Insert some test data
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup
insert into TestTable values(1),(2)
--Backup the secondary filegroup and then backup the transactionlog
USE [master]
GO
BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\tlog.trn'
WITH INIT;
--Restore the secondary filegroup and then restore the transactionlog
--taken in the previous step
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH norecovery
RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\tlog.trn'
WITH recovery;
--At this point, you should have seen the messages about
--Additional roll forward past LSN ... is required to complete the restore sequence
--Take an additional log backup and then restore with recovery
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\Taillog.trn'
WITH INIT;
RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\Taillog.trn'
WITH recovery;
--Verify you can select from TestTable
use [FGRestoreTEST]
go
SELECT *
FROM TestTable