Sql-server – Scenario of huge SQL Server Database backup

sql serversql server 2014sql-server-2008sql-server-2008-r2sql-server-2012

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

Best Answer

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;
Related Question