Problem Statement: Our support engineers need to grab database backups from clients. Currently, our database is contained in one Primary/.mdf filegroup/file. These databases have been collecting historical data for several years now and are growing large. It takes our engineers day(s) to copy the backups to our office.
There seems to have been no provisions/thought put in place to manage the growth of the database.
The most "valuable" data is "the configuration" of our system. It is contained in about 50 tables that we're considering moving to a new Configuration filegroup separate from the historical Data (which would be placed in the Data filegroup) in an attempt to set the stage for Partial or file backups.
But I'm not really sure which backup/restore strategy I need to accomplish this - Partial Backups or File Backups?
For R&D purposes, I have a database named FilegroupDemo
that uses the SIMPLE
recovery model. FilegroupDemo
contains 3 filegroups:
- Primary (maps to FilegroupDemo.mdf)
- Configuration (maps to FilegroupDemo_Configuration.ndf)
- Data (maps to FilegroupDemo_Data.ndf)
Data in the Configuration filegroup does not change often and could potentially be marked read-only (if it helps) while data in the Data filegroup changes as frequently as every minute.
I want the flexibility to backup/restore just the Primary and Configuration filegroups/files.
In the BOL article for SQL Server Partial Backups it states:
A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files.
The way this reads, it makes me think that Partial Backups are designed for omitting read-only filegroups, most likely for large volumes of data that won't change and are marked read-only. These don't need to be backed up each time.
1. Am I correct in saying that Partial Backups exist to backup all filegroups EXCEPT the ones marked read-only? In other words, if I'm not using read-only filegroups, there's no point of using Partial Backups - correct? And I don't think I can use a Partial Backup to backup just a readonly filegroup?
Therefore, I don't think Partial Backups are what I need. Rather naively, I attempted a file backup/restore. I switched my database to FULL
recovery model and ran:
BACKUP DATABASE FilegroupDemo FILEGROUP = N'PRIMARY'
TO DISK = N'C:\Backups\FilegroupDemo_FG_Primary.bak'
WITH INIT
GO
BACKUP DATABASE FilegroupDemo FILEGROUP = N'Configuration'
TO DISK = N'C:\Backups\FilegroupDemo_FG_Configuration.bak'
WITH INIT
GO
BACKUP DATABASE FilegroupDemo FILEGROUP = N'Data'
TO DISK = N'C:\Backups\FilegroupDemo_FG_Data.bak'
WITH INIT
GO
<delete the database>
RESTORE DATABASE FilegroupDemo FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\FilegroupDemo_FG_Primary.bak'
WITH PARTIAL, NORECOVERY, REPLACE
GO
RESTORE DATABASE FilegroupDemo
FROM DISK = N'C:\Backups\FilegroupDemo_FG_Configuration.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE LOG FilegroupDemo
FROM DISK = 'C:\Backups\FilegroupDemo_Log.trn'
WITH RECOVERY
GO
Much to my surprise, this actually seemed to work. I can query the tables in my Configuration filegroup whereas when I query the tables in my Data filegroup, I get:
The query processor is unable to produce a plan for the table or view 'xxx' because the table resides in a filegroup that is not online.
2. I guess I'm surprised this worked because I thought this was an "ONLINE restore" which was an Enterprise feature. In other words, my database is functional while ancillary filegroups remain offline - no?
I am definitely using standard edition:
Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
3. Does a file restore require FULL
recovery model? If I can't use the SIMPLE recovery model to perform this type of file backup and I must switch to FULL, is it something I can do temporarily - just to get a backup copy - and then switch it back to SIMPLE
? Is there any harm in this? Also, any idea why you have to use FULL - just curious?
Data
filegroup as requested, and yet it didn't work. Also, did you try creating an empty FileGroup and data file forData
after you did the restore? AND, if support just needs to recreate scenarios with test data, SSDT can extract certain tables to be imported somewhere else.