4

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?

  • Can you please explain the overall goal / intention behind "Our support engineers need to be able to take/transfer a "copy-only" backup of just the data in the Configuration filegroup"? It would help determine what would qualify as a "working" solution, given that you tried the FileGroup method, it did omit the Data filegroup as requested, and yet it didn't work. Also, did you try creating an empty FileGroup and data file for Data 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. – Solomon Rutzky Oct 14 '16 at 14:28
  • See updated question with "problem statement". Let me know if anything else requires further clarification. Correct. The filebackup appears to work. I'm concerned about temporarily changing the recovery model for this purpose. Is that a good/bad practice? Any potential pitfalls? Also, you mentioned creating an empty filegroup for the data file. Currently, it's not accessible which could cause some problems. Can you swap in an empty data file? Also, what do you mean by SSDT, an integration services package? – John Russell Oct 14 '16 at 15:18
5
+50

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"
  • Still googling, but SqlPackage.exe /Action:Extract for just a couple of tables seems to barf when there are additional tables that Fk to the table being extracted (if they are not included themselves). Error SQL71564: Foreign key Foreign Key: xyz cannot reference table Table: abc when used as part of a data package. Any ideas if there's an option to suppress that error? – John Russell Oct 14 '16 at 19:46
  • No problem. Export looks promising. Still investigating... – John Russell Oct 14 '16 at 21:05
  • 1
    OMG, You are my new favorite person! I'm not sure why Extract had issues, but Export does the trick. Obviously, you have to include dependent data, etc. – John Russell Oct 14 '16 at 21:10
  • @JohnRussell If you have to include dependent data anyway, then what's the issue with Extract? I had recommedned "Extract" because I thought it would have been the more likely one to work without dependent data ;-). Also, have you tested the Publish operation to make sure that the data comes back into the empty structure as expected, and is usable by the Support group? Oh, and do you have the transfer part of it working, even if not via FTP? I realize that wasn't part of the question, but am curious if you were able to fully automate this. You are quite welcome, and thanks for the compliment. – Solomon Rutzky Oct 14 '16 at 21:21
  • 1
    By dependent data, I mean Fk data in which the tables I'm exporting require to be present - lookups, etc. I couldn't even get that far with Extract. It seemed to fail at the schema validation step. I have tried the publish and everything looks good. I will continue testing. – John Russell Oct 14 '16 at 21:30
1

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

  • Thanks for the input and the links. Part of my dilemma is that I don't think I can use a Partial Backup in this scenario. I don't think I can take a Partial Backup of just one read/write filegroup. Alternatively, if I mark my Configuration filegroup read-only, I can't just take a Partial Backup of the Primary and the one read-only filegroup - correct? It must contain all read/write. – John Russell Oct 14 '16 at 1:36
  • I think to make Partial Backups work in this case, I would have to mark my Data filegroup read-only and leave Configuration read/write. Then I could take a Partial Backup including Primary and Configuration. But converting the Data filegroup to read-only requires exclusive access to the database - right? – John Russell Oct 14 '16 at 1:42
  • You are correct, a partial backup is always a backup of all the Read/Write filegroups, and this is a lot of administrative overhead. I would rather create a data load process to get the relevant data or even create a configuration database. – Spörri Oct 14 '16 at 8:34
  • Valid point. We've thought about a separate Configuration and Data databases. I worry about Fk constraints and maintaining referential integrity between the two databases. – John Russell Oct 14 '16 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.