2

I'm setting up a large SQL Server database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may change on occasion, so I am not going to mark any file or file group as read only.

Will I still be able to do a partial backup on just the files I choose without marking any files as read only? Or is it required that the files I want to skip be set to read only?

Now, if I have to mark the old data partitions as read only - if an old record is updated some time in the future, including the field that is used to assign it to a partition, would SQL server move the record to the read write partition, or would this update fail? (I know, read only means read only, but what would happen in this scenario?). In this instance, perhaps I would just add the record to the read write partition, and search for the latest version of the record (max modificationdate etc..)

Out of one million records, I would expect only 20,000 new or updated records per year. Only 200k of the records are "current".

I'm looking to only have to regularly back up the read /write files, and performing less frequent backup of the old data.

This is a SQL Server 2016 installation (Standard Edition).

1
  • For a full answer, we'd need to know the recovery model of the database, partitioning scheme, how the files/filegroups might be laid out on disk, as well as your recovery point/time objectives. Consider adding that information to your question.
    – Paul White
    Oct 3, 2021 at 8:34

3 Answers 3

2

I am not going to mark any file or file group as read only.

Will I still be able to do a partial backup on just the files

No. A minimal restore sequence includes all read/write filegroups. And so "a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files"

if an old record is updated some time in the future, including the field that is used to assign it to a partition, would SQL server move the record to the read write partition, or would this update fail?

The update would fail.

2
  • Thanks. So basically, the only way to get around this is to allow duplicate entries into the table, but just taking the latest entry in my queries (max mydatefield). That way the older data can stay in the read only files, and the most recent version would be in the read/write files. Out of one million records, I would expect only 20,000 new or updated records per year. Only 200k of the records are "current" - Would this give me what I'm looking for? That is, only having to regularly back up the read /write files, and performing less frequent backup of the old data? Oct 2, 2021 at 22:58
  • 1
    Or you could try infrequent full backups plus differential and/or log backups, and/or data duplication of your full backups. Eg learn.microsoft.com/en-us/windows-server/storage/…. Or backup to Azure and apply a lifecycle policy. Oct 2, 2021 at 23:22
2

You could just do a full backup less frequently, then regular differential and/or transaction log backups. These would be very small if little has changed.

The size of the diffs will grow larger and larger as more and more data changes. They are not incremental, they include all changes since the last full backup.

Restoring would take a little longer, as you would need the most recent full backup, the most recent diff which could be large, and all following log backups.

2

You could consider a Full File Backup strategy. This is primarily of benefit in your scenario if the database is using the full recovery model.

This alternative can be more challenging to set up and maintain, but offers advantages that seem relevant to your needs. Quoting from the documentation link above:

Benefits of File Backups

File backups offer the following advantages over database backups:

  • Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database.

  • For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored. The damaged file can be quickly restored, and recovery is faster than it would be for an entire database.

  • File backups increase flexibility in scheduling and media handling over full database backups, which for very large databases can become unmanageable. The increased flexibility of file or filegroup backups is also useful for large databases that contain data that has varying update characteristics.

Disadvantages of File Backups

  • The primary disadvantage of file backups compared to full database backups is the additional administrative complexity. Maintaining and keeping track of a complete set of these backups can be a time-consuming task that might outweigh the space requirements of full database backups.

  • A media failure can make a complete database unrecoverable if a damaged file lacks a backup. You must therefore maintain a complete set of file backups, and, for the full/bulk-logged recovery model, one or more log backups covering minimally the interval between the first full file backup and last full file backup.

The number of transaction log backups you need to keep to achieve complete recovery can be reduced by also taking differential file backups.

Please do read the extensive documentation, and try various restore scenarios on your test system before committing to this option. It does allow the maximum amount of flexibility around how often each file is backed up, but keeping everything consistent and recoverable can be a challenge.

Note that Standard Edition only supports offline file restores.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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