4

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is:

Create full backup at the first day of each month.
Create Differential backup daily exclusive first day of each month.

also I use mirror backup in order to copy my backup file to another server.

Total time that need to create full backup is about 3 hour.

I look for a better scenario to create small backup size and copy faster than now.

In this database I have 2 filegroups (ArchiveFG and PRIMARY). The size of ArchiveFG is 200 GB, also this file group is read-only filegroup.

I don't know that can I use following scenario?

create full backup from ArchiveFG filegroup (One time).
create full backup from PRIMARY filegroup the first day of each month.
create differential backup from PRIMARY filegroup daily exclusive first day of each month.

And how can I restore backup with above scenario.

Thanks in advance

migrated from stackoverflow.com Oct 5 '15 at 10:50

This question came from our site for professional and enthusiast programmers.

  • 1
    What is your restore SLA for this database? A multi-part backup/restore strategy can be very handy but also increases complexity and risk. As for ArchiveFG, you're really backing up files or filegroup not full DB backup. That can help reduce backup sizes to smaller chunks if you have FGs that are read-only so don't need regular backup. Before going on a more complex route, have you tried optimizing your current backup? I can consistently backup/restore 1TB raw in under 30min with backup compression. What's your uncompressed size? – SQLmojoe Oct 5 '15 at 17:21
  • @SQLmojoe. Thanks for your comment. The Compressed size is about 250 GB and I didn't get uncompressed backup until now. also most of backup time is used for copy data to another server (Mirror Backup). – mehdi lotfi Oct 6 '15 at 11:13
1

What you're looking to do is called a "piecemeal restore" and is pretty complicated. Here's a small pull quote from the MSDN article:

Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups.

I'm assuming that your database is in simple recovery since you didn't mention any log backup schedule. So, if you ever need to restore your archive filegroup, you'll end up restoring the entire database.

If I were you, I'd try tuning your backup before going this route. For instance, try taking the mirroring out of the backup command note the time difference. You can always copy it to the secondary location after the full backup completes.

But, if you're looking to proceed with the piecemeal restore solution, the actual syntax isn't that bad:

restore database [yourDB]
filegroup = 'PRIMARY'
from disk = '\\path\\to\\full_backup.bak'
with partial, norecovery;

restore database [yourDB]
filegroup = 'PRIMARY'
from disk = '\\path\\to\\diff_backup.bak'
with partial, recovery;

restore database [yourDB]
filegroup = 'ArchiveFG'
from disk = '\\path\\to\\archive.bak'
with recovery;

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.