SQL Server - What backup/restore strategy should I use to accomplish the following?

Question

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?

Answer 1

Given the following info:

Our support engineers need to grab database backups from clients.

and:

The most "valuable" data is "the configuration" of our system. It is contained in about 50 tables...

and:

I'm concerned about temporarily changing the recovery model for this purpose [of allowing the "Data" filegroup to become ReadOnly so that it can be omitted from the backup]

I would suggest a slightly different approach: use SqlPackage.exe (part of SQL Server Data Tools / SSDT) to either "extract" or "export" the desired tables and data into a single archive file which can then be retrieved and either "published" or "imported". SSDT is free, so there should be no licensing issue related to putting the SqlPackage.exe executable (and any dependent DLLs, etc) on the client systems.

You just need to make sure that SqlPackage.exe (and any dependent DLLs, etc) are on each client system. You can initiate SqlPackage.exe from xp_cmdshell. You can even create a .CMD script that executes SqlPackage.exe with the appropriate command-line parameters (there are several -- or you can place most options into an XML-based "publish profile" file and specify that on the command-line). You would then transfer the archive file (.dacpac or .bacpac) locally, just like you are transfering the backup file now. If you are FTPing to your local server from theirs, you can include the FTP commands in the .CMD script and then if you place the EXEC xp_cmdshell 'ExportAndTransferData.cmd'; statement into a stored procedure, you would only need to execute that stored procedure :-).

Looking at the various options, and knowing that you want a subset of the tables, I am thinking that you should first attempt an "Extract" (i.e. /Action:Extract. You don't want all tables, so then specify: /p:ExtractAllTableData=false. Then, for each table that you do want, specify: /p:TableData=schema.table (you will have around 50 of these options specified given that you want around 50 tables). Finally, it might be a good idea to verify the extraction by specifying: /p:VerifyExtraction=true.

Once the .dacpac file is retrieved locally, you would "Publish" it (i.e. /Action:Publish. For this action you just need a template database with all of the schema (tables, PKs, FKs, indexes, views, etc) and stored procedures / functions already there. This operation will just put the data into the proper tables.

Example CMD script (ExportAndTransfer.cmd)

@ECHO OFF

SET SQLPATH="C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin"

%SQLPATH%\SqlPackage.exe ^
    /Action:Extract ^
    /OverwriteFiles:True ^
    /SourceDatabaseName:ClientDatabaseName ^
    /SourceServerName:(local) ^
    /TargetFile:%TEMP%\ConfigurationTables.dacpac ^
    /p:IgnoreExtendedProperties=True ^
    /p:ExtractAllTableData=False ^
    /p:TableData="dbo.Table1" ^
    /p:TableData="dbo.Table2" ^
    /p:VerifyExtraction=true

ftp -s:path\to\FtpScript.txt my_server_hostname

Example Stored Procedure to call CMD script

CREATE PROCEDURE dbo.RunExtract
AS
SET NOCOUNT ON;

EXEC xp_cmdshell 'C:\path\to\ExportAndTransfer.cmd';

GO

All you need after those two is the ftp script.

---

UPDATE

While the "Extract" operation is generally more forgiving than the "Export" operation due to "Export" not liking various types of objects, in this particular case, the "Export" operation worked for the O.P. as it does not require grabbing all tables that are related via Foreign Keys. The "Extract" operation requires that, if a table is specified that has any FKs to other tables or if any other tables FK to it, all tables linked in any way to the desired table must be included. I found this MSDN forum thread discussing the reason for this requirement:

A dacpac can be published to any database -- empty or non-empty. And when table data is published, if the target table isn't empty, all of the data is dropped and replaced. But if the target table isn't empty and it has incoming FK dependencies, then there could possibly be broken FK references. Requiring that all tables be a closed set resolves this problem, because any incoming references will be erased as well.

There are fewer options to specify for "Export", so the command-line would look more like the following:

%SQLPATH%\SqlPackage.exe ^
    /Action:Export ^
    /OverwriteFiles:True ^
    /SourceDatabaseName:ClientDatabaseName ^
    /SourceServerName:(local) ^
    /TargetFile:%TEMP%\ConfigurationTables.bacpac ^
    /p:TableData="dbo.Table1" ^
    /p:TableData="dbo.Table2"

Answer 2

You are correct, except that you can do partial backups and piecemeal restores with the database in simple recovery mode, even though you loose certain flexibility that way. To do an online piecemeal restore, that is to bring the database online without the read-only filegroups you need the enterprise edition of SQL Server. Remember that the development edition has all the enterprise features.

1. Yes a partial backup is a backup of the primary datafile and all read/write filegroups in the database
2. This is an online restore, you will need the enterprise edition to do this. But this will function on the dev edition as well
3. You can use the simple recovery model for this and you will not need to do any exercises changing the recovery model, just change your current backups to be partial and go though all the possible scenarios.

A good write up with examples is on simple-talk