In the technology world, it is just matter of time before disaster happens. A database could become corrupted due to a user error, hardware malfunction, or software bug. A disk array could fail, making databases unavailable to users. An engineer can accidently change the LUN configuration in a SAN array and affect the database it stores. A natural disaster could affect the availability of a datacenter. In any of these instances, it is essential to recover the database and bring the system online with minimal data loss and downtime. Thus an appropriate backup strategy is a key element of every SQL Server installation.

In this chapter, we will discuss how to design backup strategies in a way that minimizes system downtime and data loss. It is impossible to touch that subject without talking about database backup, restore, and recovery processes in detail, which we will also cover in this chapter.

Database Backup Types

There are three different types of database backups available in SQL Server.

A Full database backup backs up the whole database. SQL Server performs a CHECKPOINT as the first step of database backup, backs up all allocated extents from the data files and, finally, backs up the portion of transaction log required to recover the database after a restore. That portion includes all log records starting from the oldest of these events:

• The last CHECKPOINT.
• The beginning of the oldest active transaction.
• The beginning of the unscanned portion of the log if there are any processes that rely on the transaction log scan, such as Transactional Replication, Database Mirroring, AlwaysOn Availability Groups, and others.

A full database backup represents the database at the time when the backup operation is finished. It is supported in every recovery model.

A differential backup backs up extents, which have been modified since the last full backup. SQL Server tracks what extents have been changed with a special type of allocation map pages called Differential Changed Map (DCM). SQL Server clears those map pages only during a full database backup. Therefore, differential backups are cumulative, and each of them stores all extents that have been modified since the last full, rather than last differential, backup.

Like a full database backup, differential backups work in every recovery model.

A Log backup backs up the active portion of transaction log starting with the LSN of last full or log backup. This backup type is only supported in the FULL or BULK-LOGGED recovery models, and it is an essential part of transaction log management, which is required to trigger log truncation.

For a database in the BULK-LOGGED recovery model, log backups also store the data from extents that were allocated during minimally logged bulk-copy operations, such as CREATE INDEX, ALTER INDEX REBUILD, BULK INSERT, INSERT INTO, INSERT SELECT, and few others. Those extents are tracked with another set of allocation map pages called Bulk Changed Map (BCM). SQL Server must be able to access data files with those extents for the log backup to succeed.

In contrast to differential backups, log backups are incremental. Each subsequent log backup stores the portion of the transaction log starting at the point where the previous log backup finished. You must apply all log backups one-by-one during restore process.

The sequence of log backups contains log records for all operations performed by SQL Server since last full backup. This allows you to redo the work and recover the database, even when database files are corrupted or unavailable. Moreover, it supports point-in-time recovery and allows you to recover the database up to a particular time. One case when this is beneficial is upon the accidental deletion of the data or a database object. We will talk about such a situation later in the chapter.

The special kind of log backup, called tail-log backup, is used when you need to recover a database after a disaster. It backs up log records that have not been backed up since the last log backup, and it prevents potential data loss during recovery. We will talk about tail-log backups in detail later in this chapter.

A continuous sequence of log backups is called a log chain. A log chain starts with a full database backup, and it is required to restore database up to the point of failure and/or a point in time.

Figure 30-1 shows an example of a log chain and a tail-log backup.

Backing Up the Database

You can backup and restore the database using Management Studio UI, T-SQL, and PowerShell, as well as with third-party tools. In this chapter, we will focus on the T-SQL implementation.

Listing 30-1 shows the T-SQL statements that perform a full database backup with a disk as the destination.

Listing 30-1.  Performing a full database backup

BACKUP DATABASE [OrderEntryDb]
TO DISK = N'e:ackupsOrderEntry.bak'
WITH FORMAT, INIT,
NAME = N'OrderEntryDb-Full Database Backup',
STATS = 5, CHECKSUM, COMPRESSION

SQL Server allows you to store multiple backups in a single file. This approach, however, has its pros and cons. While it reduces the number of files on the disk and simplifies their management, you should be extremely careful during backup operations to make sure that it does not override existing backups. Moreover, you should design your backup placement in a way that reduces the amount of data that needs to be copied over the network in case of disaster.

FORMAT and INIT options tell SQL Server to override all existing backups in the backup file.

The CHECKSUM option forces SQL Server to validate the checksum on the data pages and generate a checksum of the backup file. This helps to validate that the data pages have not been corrupted by the I/O subsystem after they were saved to disk. It is also worth mentioning that this option adds extra CPU load and reduces backup throughput.

Finally, the COMPRESSION option forces SQL Server to compress the backup. Backup compression can significantly reduce the size of the backup file, although it uses more CPU resources during the backup and restore processes. It is recommended that you use backup compression unless the system is heavily CPU-bound or the database is encrypted. In the latter case, backup compression does not introduce any space saving.

Backup compression is available in the Enterprise and Standard editions of SQL Server 2008R2 and above and in the Enterprise edition of SQL Server 2008. It is worth mentioning that every edition of SQL Server can restore compressed backup.

You can perform a differential backup using the DIFFERENTIAL option, as shown in Listing 30-2.

Listing 30-2.  Performing a differential database backup

BACKUP DATABASE [OrderEntryDb]
TO DISK = N'e:ackupsOrderEntry.bak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'OrderEntryDb-Differential Database Backup',
STATS = 5, CHECKSUM, COMPRESSION

Now our backup file OrderEntry.bak has two backups: one FULL and another DIFFERENTIAL. Finally, Listing 30-3 shows you how to perform a transaction log backup by placing it into another file.

Listing 30-3.  Performing a transaction log backup

BACKUP LOG [OrderEntryDb]
TO DISK = N'e:ackupsOrderEntry.trn'
WITH FORMAT, INIT,
NAME = N'OrderEntryDb-Transaction Log Backup',
STATS = 5, CHECKSUM, COMPRESSION

You can specify multiple destination backup files and allow SQL Server to stripe backup across all of them. This can improve the performance of backup and subsequent restore operations if the I/O performance of the backup drive becomes a bottleneck.

The COPY_ONLY option allows you to perform a backup without breaking the log chain. One possible use-case for such option is the situation where you need to bring the copy of the database to a development environment.

SQL Server stores the information about every backup and restore operation on a server instance in the set of the tables defined in the msdb database. A description of those tables is outside of the scope of this book. You can read the Books Online article “Backup History and Header Information,” at: http://msdn.microsoft.com/en-us/library/ms188653.aspx for more details.

Finally, SQL Server writes information about every backup to the error log file. This could quickly balloon the size of the log file if backups are running frequently. You can disable this behavior with Trace Flag T3226. This makes error logs more compact at the cost of requiring a query against msdb to obtain backup history.

Restoring the Database

You can restore a database with the RESTORE DATABASE command. You can see an example of this command in action in Listing 30-4. It restores the OrderEntryDB database at a new destination (the MOVE option controls this), and it applies differential and transaction log backups after that.

Listing 30-4.  Restoring the database

use [master]
go
 
-- Initial FULL backup
RESTORE DATABASE [OrderEntryDbDev]
FROM DISK = N'C:BackupsOrderEntry.bak'
WITH FILE = 1,
MOVE N'OrderEntryDB' TO N'c:ackupsOrderEntryDB.mdf',
MOVE N'OrderEntryDB_log' TO N'c:ackupsOrderEntryDB_log.ldf',
NORECOVERY,  NOUNLOAD,  STATS = 5;
 
-- Differential backup
RESTORE DATABASE [OrderEntryDbDev]
FROM DISK = N'C:BackupsOrderEntry.bak'
WITH FILE = 2,
NORECOVERY,  NOUNLOAD,  STATS = 5;
 
-- Transaction Log backup
RESTORE LOG [OrderEntryDbDev]
FROM DISK = N'C:BackupsOrderEntry.trn'
WITH NOUNLOAD, NORECOVERY, STATS = 10;
 
RESTORE DATABASE [OrderEntryDbDev] WITH RECOVERY;

When the backup file stores multiple backups, you should specify a file number by using the WITH FILE option. As I noted earlier, be careful with this approach and make sure that your backup routine does not accidentally override existing backups in the file.

Each RESTORE operation should have a database recovery option specified. When a backup is restored with the RECOVERY option, SQL Server recovers the database by performing both the redo and undo recovery stages, and it makes the database available to the users. No further backups can be restored. Alternatively, the NORECOVERY option performs only the redo stage of database recovery, and it leaves the database in the RESTORING state. It allows you to restore further backups from the log chain.

Accidental use of the RECOVERY option would require you to repeat the restore process from the beginning, which could be very time consuming in the case of large databases. It is safer to restore all backups with the T-SQL RESTORE command using the NORECOVERY option all of the time. Finally, you can recover the database and bring it online with the RESTORE DATABASE WITH RECOVERY command, as was shown in Listing 30-4.

We will discuss how to restore the database after a disaster later in this chapter. Now let’s cover a couple useful options that you can use during a restore.

use master
go
 
create database MyDB
go
 
create table MyDB.dbo.Invoices
(
        InvoiceId int not null
);
 
insert into MyDB.dbo.Invoices values(1),(2),(3)
go
  
BACKUP DATABASE [MyDB]
TO  DISK = N'c:ackupsMyDB.bak'
WITH NOFORMAT, INIT,
NAME = N'MyDB-Full Database Backup', STATS = 5

BACKUP LOG MyDB
TO DISK = N'c:ackupsMyDB.trn'
WITH NOFORMAT, INIT,
NAME = N'MyDB-Transaction Log  Backup', STATS = 5

declare
        @TraceFilePath nvarchar(2000)
 
select @TraceFilePath  = convert(nvarchar(2000),value)
from ::fn_trace_getinfo(0)
where traceid = 1 and property = 2
 
select
        StartTime
        ,EventClass
        ,case EventSubClass
                when 0 then 'DROP'
                when 1 then 'COMMIT'
                when 2 then 'ROLLBACK'
        end as SubClass
        ,ObjectID
        ,ObjectName
        ,TransactionID
from ::fn_trace_gettable(@TraceFilePath, default)
where EventClass = 47 and DatabaseName = 'MyDB'
order by StartTime desc

RESTORE DATABASE [MyDBCopy]
FROM DISK = N'C:BackupsMyDB.bak'
WITH FILE = 1,
MOVE N'MyDB' TO N'c:dbMyDBCopy.mdf',
MOVE N'MyDB_log' TO N'c:dbMyDBCopy.ldf',
NORECOVERY,  STATS = 5;
 
RESTORE LOG [MyDBCopy]
FROM DISK = N'C:BackupsMyDB.trn'
WITH FILE = 1,  NORECOVERY, STATS = 5,
STOPAT = N'2013-12-07T12:07:02.633' ;
 
RESTORE DATABASE [MyDBCopy] WITH RECOVERY

select [Current LSN], [Begin Time], Operation
 ,[Transaction Name], [Description]
from fn_dump_dblog
(
default, default, default, default, 'C:ackupsmydb.trn',
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default, default, default, default,
default, default, default)
where [Transaction Name] = 'DROPOBJ'

RESTORE LOG [MyDBCopy]
FROM DISK = N'C:BackupsMyDB.trn'
WITH FILE = 1,  NORECOVERY, STATS = 5,
STOPBEFOREMARK = 'lsn:0x00000021:000000bb:0004'

RESTORE LOG [MyDBCopy]
FROM DISK = N'C:BackupsMyDB.trn'
WITH FILE = 1,  STATS = 5,
STANDBY = 'C:Backupsundo.trn'

Designing a Backup Strategy

Every production system has two requirements, which affect and shape Backup Strategy implementation. The first is the Recovery Point Objective (RPO),which dictates how much data loss is acceptable in the case of disaster. The second requirement is the Recovery Time Objective (RTO),which defines the acceptable downtime for the recovery process.

RPO and RTO metrics are usually included in the Service-Level Agreements defined for the system. When RPO and RTO are not formally documented, you can determine them by interviewing stakeholders and gathering information about their expectations.

Non-technical stakeholders often have unrealistic expectations when defining RPO and RTO requirements. They often assume zero data loss and system downtime. It is impossible to guarantee or achieve such goals in real life. Moreover, very small RPO/RTO adds additional load to the server and is often impractical to implement. It is your job to educate stakeholders and work with them to define realistic RPO and RTO based on business requirements.

The RPO dictates the recovery model that the database should use. Table 30-1 shows possible data loss and recovery points for the different database recovery models, assuming that backup files are available and the backup chain is intact. Obviously, if both the data and log files are corrupted, restoring the last backup is the only option, regardless of the recovery model.

In the SIMPLE recovery model, all changes since last full or differential backup must be redone. Therefore, this model is not the best candidate for databases with volatile data. However, the SIMPLE recovery model is perfectly acceptable when the data is static, for example in data warehouse and/or reporting systems where the data is refreshed based on some schedule. You can use the SIMPLE recovery model by performing a full database backup after each data refresh.

Another possible use-case for the SIMPLE recovery model is a database with data that can be easily and quickly reconstructed from other sources. In these cases, you may consider using this model to avoid transaction log maintenance.

The FULL and BULK-LOGGED recovery models log regular (non bulk-copy operations) in the same way and have the same transaction log maintenance requirements. Even though the BULK-LOGGED recovery model improves the performance of bulk-copy operations due to minimal logging, it is exposed to data loss in the case of data file corruption. You should avoid using the BULK-LOGGED recovery model because of this. Nevertheless, you may consider switching the database from the FULL to the BULK-LOGGED recovery model for the duration of bulk-copy operations (for example during index rebuild) and then switching the database back to the FULL recovery model afterwards.

Neither of the recovery models would survive transaction log corruption and keep the database transactionally consistent. You should store the transaction log on a highly redundant disk array in order to minimize the chance of such situations. Neither solution, however, is 100 percent redundant. You should make regular log backups to minimize possible data loss. The frequency of log backups helps control possible data loss and indicates how much work must be redone in the case of transaction log corruption. For example, if you performed a log backup every hour, you can only lose up to one hour’s work when restoring the last log backup.

While it is relatively easy to define a backup strategy based on the RPO, it is much trickier with RTO, which specifies maximum duration of the recovery process and, therefore, system downtime. That time depends on a few factors, such as network throughput, which dictates how much time is required to transmit backup files over the network as well as on the size and number of backup files. Moreover, it changes over time as the database and load grows.

Figure 30-4 shows a recovery scenario for a database that has multiple differentials and log backups. As a first step during recovery, you should make a tail-log backup, which backs up the portion of the transaction log that had not been backed up since the last log backup. After that, you should restore the last full backup, most recent differential backup, and all log backups taken afterwards, including the tail-log backup.

Let’s assume that the example shown in Figure 30-4 represents a database with the primary filegroup residing on disk M:, secondary filegroup on disk N:, and transaction log on disk L:. All backup files are stored on disk V:. Listing 30-12 shows the script that recovers the database after a disaster when disk N: becomes corrupted and unavailable. The data files from the secondary filegroup are moved to disk M.

Listing 30-12.  Restoring the database after a disaster

-- Backing up Tail-Log.
-- Database will be left in RESTORING stage
BACKUP LOG [RecoveryDemo]
TO DISK = N'V:RecoveryDemo-tail-log.trn'
WITH NO_TRUNCATE, NOFORMAT, INIT,
NAME = N'RecoveryDemo-Tail-log backup',
NORECOVERY, STATS = 5;
 
-- Restoring FULL backup moving files
-- from SECONDARY FG to M: drive
RESTORE DATABASE [RecoveryDemo]
FROM DISK = N'V:RecoveryDemo-F1.bak'
WITH FILE = 1,
MOVE N'RecoveryDemo_Secondary'
    TO N'M:RecoveryDemo_Secondary.ndf',
NORECOVERY, STATS = 5;
 
-- Restoring DIFF backup
RESTORE DATABASE [RecoveryDemo]
FROM DISK = N'V:RecoveryDemo-D2.bak'
WITH  FILE = 1, NORECOVERY, STATS = 5;
 
-- Restoring L5 Log backup
RESTORE LOG [RecoveryDemo]
FROM DISK = N'V:RecoveryDemo-L5.trn'
WITH  FILE = 1, NORECOVERY, STATS = 5;
 
-- Restoring L6 Log backup
RESTORE LOG [RecoveryDemo]
FROM DISK = N'V:RecoveryDemo-L6.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
 
-- Restoring tail-log backup
RESTORE LOG [RecoveryDemo]
FROM DISK = N'V:RecoveryDemo-tail-log.trn'
WITH FILE = 1,  NORECOVERY, STATS = 5;
 
-- Recovering database
RESTORE DATABASE [RecoveryDemo] WITH RECOVERY;

You can take multiple restore paths while recovering the database. In addition to the method shown above, you can also use differential backup D1, applying log backups L3-L7 and the tail-log backup. As another option, you can use only log backups after you have restored a full backup without using any differential backups at all. However, the time required for the restore process greatly depends on the amount of transaction log records that need to be replayed. Differential backups allow you to reduce the amount of time involved and speed up the restore process.

You should design a backup strategy and find the right combination of full, differential, and log backups that allow you to restore the database within the time defined by the RTO requirements. The key point here is to define the schedule of full and differential backups because the frequency of log backups depends on RPO and possible data loss.

You should create differential backups often enough to minimize the number of log backups that need to be restored and log records that need to be replayed in case of recovery. Differential backups are cumulative, though, and you should avoid the situation where they store a large amount of data modified since the last full backup. It would be better to perform full backups more often in that case.

As an example, consider a database that collects some data from external sources, keeping one week of the most recent data and purging it on a daily basis using a sliding-window pattern implementation. In this schema, one-seventh of the data is changing on a daily basis.

Let’s assume that a full backup is taken weekly and differential backups are taken daily. If the size of the full backup is 1TB, the incremental backups would grow at a rate of 140-150MB per day. In that case, if a disaster happened on the seventh day after the last full backup, you would need to restore 1TB of full backup and about 850MB of differential backups before applying log backups, which is very time consuming and redundant. It would be much more efficient to perform full backups on a daily basis in that case.

The location of backup files is another important factor that affects recovery time. It could be very time consuming to copy a large amount of data over the network. Consider keeping multiple copies of backup files when it is appropriate—off-site, on-site, and perhaps, even locally on the server.

I/O subsystem and network performance are usually the biggest bottlenecks during backup and restore. Backup compression helps to reduce the size of the data that needs to be transmitted over the network or read from disk. Always use backup compression if the database is not encrypted and the server can handle the extra CPU load introduced by compression.

One of the key elements of a good backup strategy is backup validation. It is not enough to back up the database. You should make sure that backup files are not corrupted and that the database can be restored from them. You can validate backup files by restoring them on another server.

Another good practice that ensures the safety of a backup is storing a redundant set of backup files. Do not delete backup files with old differential and log backups after you make a new differential backup. Such strategy may help you to recover the database when the most recent backup is corrupted.

Finally, databases do not live in a vacuum. It is not enough to recover a database after a disaster; it must also be available to the client applications. Backup and disaster recovery strategies should incorporate other elements from the database ecosystem and support database restore on another SQL Server. Those elements include Server Logins, SQL Jobs, Database Mail Profiles, procedures in the master database, and a few others. They should be scripted and tested together with the backup strategy.

Partial Database Availability and Piecemeal Restore

Partial Database Availabilityis an Enterprise Edition feature, which allows you to keep part of the database online during disaster or to restore the database on a filegroup-by-filegroup basis, making these filegroups available to users one-by-one. Partial database availability works on a filegroup basis, and it requires a primary filegroup and transaction log file to be available and online.

Partial database availability is especially beneficial in cases of data partitioning. Different data in the system may have different RTO requirements. For example, it is not uncommon to have the recovery time requirement for current critical operation data in minutes, while the recovery time for older, historical data is listed in hours or even days. Piecemeal restore allows you to perform a partial database restore and quickly bring operational data online without waiting for historical data to be restored.

Let’s assume that we have the database OrderEntryDB with four filegroups:Primary, Entities, OperationalData, and HistoricalData. The Primary filegroup resides on the M: drive, Entities and OperationalDatareside on the N: drive, and HistoricalData resides on the S: drive. Listing 30-13 shows the database layout for this.

Listing 30-13.  Partial DB Availability: Database layout

create database OrderEntryDB
on primary
(name = N'OrderEntryDB',
        filename = N'M:OrderEntryDB.mdf'),
filegroup Entities
(name = N'OrderEntryDB_Entities',
        filename = N'N:OrderEntryDB_Entities.ndf'),
filegroup OperationalData
(name = N'OrderEntryDB_Operational',
        filename = N'N:OrderEntryDB_Operational.ndf'),
filegroup HistoricalData
(name = N'OrderEntryDB_Historical',
        filename = N'S:OrderEntryDB_Historical.ndf')
log on
(name = N'OrderEntryDB_log',
        filename = N'L:OrderEntryDB_log.ldf')

In the first example, let’s assume that the S: drive is corrupted and the HistoricalData filegroup becomes unavailable. Let’s see how you can recover the data from this filegroup and move the files to another drive.

As a first step, shown in Listing 30-14, you need to mark the corrupted file as being offline. This operation terminates all database connections, although users can reconnect to the database immediately afterwards.

Listing 30-14.  Partial DB Availability: Mark file as offline

alter database OrderEntryDb
modify file(name = OrderEntryDB_Historical, offline)

At this point, all of the data in the HistoricalData filegroup is unavailable to users. However, users can still work with the data from the other filegroups.

If you queried the sys.database_files view with the query shown in Listing 30-15, you would see that the data files from the HistoricalData filegroup have an OFFLINE state. Figure 30-5 shows this state.

Listing 30-15.  Partial DB Availability: Querying state of the files

select file_id, name, state_desc, physical_name
from sys.database_files

In the next step, you should make a tail-log backup, as shown in Listing 30-16. It does not matter that the database is still online and that other sessions are generating log records. The OrderEntryDB_Historical file is offline and, therefore, neither of the newly generated log records would apply to the data in that file. It is worth mentioning that you should not use the NORECOVERY option when making a tail-log backup because NORECOVERY switches the database to a restoring state.

Listing 30-16.  Partial DB Availability: Making tail-log backup

BACKUP LOG [OrderEntryDB]
TO DISK = N'V:OrderEntryDB-tail-log.trn'
WITH NO_TRUNCATE, INIT,
NAME = N'OrderEntryDB-Tail-log backup',
STATS = 5

As a next step, you should restore a full backup from the current log chain, restoring individual files as shown in Listing 30-17.

Listing 30-17.  Partial DB Availability: Restoring a full backup

RESTORE DATABASE [OrderEntryDB]
        FILE = N'OrderEntryDB_Historical'
FROM DISK = N'V:OrderEntryDB.bak'
WITH FILE = 1,
MOVE N'OrderEntryDB_Historical'
        TO N'P:OrderEntryDB_Historical.ndf',
NORECOVERY , STATS = 5

If you ran a query that shows the state of the files from Listing 30-15 again, you would see the results shown in Figure 30-6. Only one file would be in the RESTORING stage, while all other files are online and available to users.

Finally, you should restore all other differential and log backup files, finishing with the tail-log backup. You do not need to specify each individual file here. SQL Server will restore only files that are in the RESTORING state. Review the code for doing this, as shown in Listing 30-18.

Listing 30-18.  Partial DB Availability: Restoring other backup files

RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
 
-- Restoring tail-log backup
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB-tail-log.trn'
WITH FILE = 1,  NORECOVERY, STATS = 5;
 
RESTORE DATABASE [OrderEntryDB] WITH RECOVERY;

The database is recovered, and all files are now online, as shown in Figure 30-7.

You can use the same sequence of actions while recovering individual files in the non-Enterpise Editions of SQL Server, although the database switches to RESTORING state and would not be available to users during this process.

The same technique can be applied when you want to perform a piecemeal restore of the database, bringing it online on a filegroup-by-filegroup basis. You could use a RESTORE statement, specifying the list of the filegroups, and use the PARTIAL option. Listing 30-19 shows you how to perform a piecemeal restore of the Primary, Entities, and OperationalData filegroups.

Listing 30-19.  Piecemeal filegroup restore: Restoring Primary, Entities, and OperationalData filegroups

RESTORE DATABASE [OrderEntryDB]
FILEGROUP='Primary',FILEGROUP='Entities',FILEGROUP='OperationalData'
FROM  DISK = N'V:OrderEntryDB.bak' WITH  FILE = 1,
MOVE N'OrderEntryDB' TO N'M:OrderEntryDB.mdf',
MOVE N'OrderEntryDB_Entities' TO N'N:OrderEntryDB_Entities.ndf',
MOVE N'OrderEntryDB_Operational' TO N'N:OrderEntryDB_Operational.ndf',
MOVE N'OrderEntryDB_log' TO N'L:OrderEntryDB_log.ldf',
NORECOVERY, PARTIAL, STATS = 5;
 
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB.trn'
WITH  FILE = 1, NORECOVERY, STATS = 5;
 
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB-tail-log.trn'
WITH FILE = 1,  NORECOVERY, STATS = 5;
 
RESTORE DATABASE [OrderEntryDB] WITH RECOVERY;

At this point, files from the restored filegroups are online, while the historical data file is in a RECOVERY_PENDING state. You can see the results of the query from Listing 30-15 in Figure 30-8.

Finally, you can bring the HistoricalData filegroup online by using the RESTORE statements shown in Listing 30-20.

Listing 30-20.  Piecemeal filegroup restore: Restoring the HistoricalData filegroup

RESTORE DATABASE [OrderEntryDB]
FILEGROUP='HistoricalData'
FROM  DISK = N'V:OrderEntryDB.bak' WITH  FILE = 1,
MOVE N'OrderEntryDB_Historical' TO N'S:OrderEntryDB_Historical.ndf',
NORECOVERY, STATS = 5;
 
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB.trn'
WITH  FILE = 1, NORECOVERY, STATS = 5;
 
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB-tail-log.trn'
WITH FILE = 1,  NORECOVERY, STATS = 5;
 
RESTORE DATABASE [OrderEntryDB] WITH RECOVERY;

A piecemeal restore greatly improves the availability of the system; however, you should design the data layout in such a way that allows you to utilize it. Usually, this implies the use of data partitioning techniques, which we discussed in Chapter 15, “Data Partitioning.”

Partial Database Backup

SQL Server allows you to back up individual files and filegroups as well as excluding read-only filegroups from a backup. You can back up read-only filegroups separately and exclude them from regular full backups, which could dramatically reduce the size of backup files and backup time.

Listing 30-21 marks the HistoricalData filegroup as read-only, and it backs up the data from this filegroup. After that, it performs a full backup for read-write filegroups only using the READ_WRITE_FILEGROUPS option and log backup.

Listing 30-21.  Partial backup: Performing backups

alter database [OrderEntryDB]
modify FILEGROUP [HistoricalData] readonly
go
 
BACKUP DATABASE OrderEntryDB
FILEGROUP = N'HistoricalData'
TO  DISK = N'V:OrderEntryDB-hd.bak'
WITH NOFORMAT, INIT,
NAME = N'OrderEntryDB-HistoricalData Backup', STATS = 5;
 
BACKUP DATABASE OrderEntryDB READ_WRITE_FILEGROUPS
TO  DISK = N'V:OrderEntryDB-rw.bak'
WITH NOFORMAT, INIT,
NAME = N'OrderEntryDB-Full', STATS = 5;
 
BACKUP LOG OrderEntryDB
TO DISK = N'V:OrderEntryDB.trn'
WITH NOFORMAT, INIT,
NAME = N'OrderEntryDB-Transaction Log ', STATS = 5;

You can exclude HistoricalData filegroup from all further full backups as long as you keep them read-only.

If you need to restore the database after a disaster, you could perform a piecemeal restore of read-write filegroups, as shown in Listing 30-22.

Listing 30-22.  Partial backup: Piecemeal restore of read-write filegroups

RESTORE DATABASE [OrderEntryDB]
FILEGROUP='Primary',FILEGROUP='Entities',FILEGROUP='OperationalData'
FROM DISK = N'V:OrderEntryDB-rw.bak' WITH  FILE = 1,
MOVE N'OrderEntryDB' TO N'M:OrderEntryDB.mdf',
MOVE N'OrderEntryDB_Entities' TO N'N:OrderEntryDB_Entities.ndf',
MOVE N'OrderEntryDB_Operational' TO N'N:OrderEntryDB_Operational.ndf',
MOVE N'OrderEntryDB_log' TO N'L:OrderEntryDB_log.ldf',
NORECOVERY,  PARTIAL,  STATS = 5;
 
RESTORE DATABASE [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB-rw.bak' WITH  FILE = 1,
NORECOVERY,  STATS = 5;
 
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:OrderEntryDB.trn'
WITH  FILE = 1, NORECOVERY, STATS = 5;
 
RESTORE DATABASE [OrderEntryDB] WITH RECOVERY;

Now the Primary, Entities, and OperationData filegroups are online and the HistoricalData filegroup is in RECOVERY_PENDING state, as shown in Figure 30-9.

You can bring the HistoricalData filegroup online by performing a restore of the original filegroup backup file, as shown in Listing 30-23.

Listing 30-23.  Partial backup: Read-only filegroup restore

RESTORE DATABASE [OrderEntryDB]
FILEGROUP='HistoricalData'
FROM  DISK = N'V:OrderEntryDB-hd.bak' WITH  FILE = 1,
MOVE N'OrderEntryDB_Historical' TO N'S:OrderEntryDB_Historical.ndf',
RECOVERY, STATS = 5;

Backup to Windows Azure

You can perform a database backup and store the backup files in the Windows Azure Blob Storage. There are two different ways to implement this:

1. In SQL Server 2012 SP1 CU2 and above, you can backup directly to or restore from Azure Blob Storage by specifying the URL location as part of the BACKUP and RESTORE commands. Listing 30-24 shows an example of this process.

   Listing 30-24.  Backup to and restore from Windows Azure Blob Storage

   CREATE CREDENTIAL mycredential
   WITH IDENTITY = 'mystorageaccount', SECRET = '<Secret Key>';
    
   BACKUP DATABASE MyDB
   TO URL =
   'https://mystorageaccount.blob.core.windows.net/mycontainer/MyDB.bak'
   WITH CREDENTIAL = 'mycredential', STATS = 5;
    
   RESTORE DATABASE MyDB
   FROM URL =
   'https://mystorageaccount.blob.core.windows.net/mycontainer/db.bak'
   WITH CREDENTIAL = 'mycredential', RECOVERY, STATS = 5;

2. Alternatively, you can use Microsoft SQL Server Backup to Microsoft Windows Azure Tool, which will work with any version and edition of SQL Server. This tool works separately from SQL Server. It intercepts backup files writing to the folders based on the specified rules, and it uploads files to Azure Blob Storage. You can see a configuration of this utility in Figure 30-10.

   

   Figure 30-10. Microsoft SQL Server Backup to Microsoft Windows Azure Tool

Figure 30-11 shows the content of the Azure Storage Container after a couple of database backups.

Each backup generates two files. One is the actual database backup, which could be compressed and encrypted if needed. The other is the stab file that contains the metadata information about the backup.

Windows Azure blob storage is a cost-effective option for companies that want redundant storage for the database backup files. However, this option is appropriate only for relatively static, non-mission critical databases with RTOs that allow prolonged downtime. Neither the BACKUP TO URL command nor the Windows Azure Backup Tool keeps a local copy of backup files. Therefore, it can take a lot of time to download backups in case of disaster.

Backing up large databases directly to the Cloud is also a questionable tactic, as it depends on the upload bandwidth available. Uploading large, multi-gigabyte backup files can take hours or even days, which makes it impractical.

With all being said, storing backup files on the Cloud can be a good option when you need a cost-effective, redundant solution. Nonetheless, it is better to implement this separately from the SQL Server backup process, uploading a local copy of the backup files afterwards. This approach allows you to recover a database quickly from disaster using the local copy of the backup files while keeping another copy of the files on the Cloud for redundancy purposes.

Finally, as of December 2013, both methods of database backup to Azure Blob Storage are limited to 1TB backup size. This is more or less a theoretical limit at this point, as the main limitation is dictated by bandwidth rather than file size.

Managed Backup to Windows Azure

SQL Server 2014 introduces the concept of Managed Backup to Windows Azure Blob Storage, which can be enabled at the instance- or database-level. SQL Server automatically performs full and transaction log backups based on the following criteria:

• Full backupis performed in any of the following situations: the last full backup was taken more than a week previously, there is log growth of 1GB or more since the last full backup, or the log chain is broken.
• Transaction log backupis taken every two hours, or when 5MB of log space is used.

Managed backup does not support databases in the SIMPLE recovery model, nor can it perform a differential backup. Backup files can be retained in Azure Blob Storage for up to 30 days.

Managed backup backs up files to Windows Azure Blob Storage only. Local storage is not supported. All considerations that we discussed in the Backup to Windows Azure section also apply to Managed backups.

Summary

A full database backup stores a copy of the database that represents its state at the time when the backup finishes. Differential backup stores extents that have been modified since the last full backup. Log backups store the portion of the transaction log starting from the last full or the end of the last log backup.

Full and differential backups are supported in every recovery model; while log backup is supported only in the FULL or BULK-LOGGED recovery models.

Differential backups are cumulative. Every backup contains all of the extents modified since the last full backup. You can restore the latest differential backup when needed. Conversely, log backups are incremental and do not contain the part of the transaction log backed up by the previous backups.

A full backup and sequence of log backups makes up a log chain. You should restore all of the backups from a chain in the right order when restoring a database. You can use the COPY_ONLY option with full or log backups to keep the log chain intact.

The frequency of log backups is dictated by Recovery Point Objective (RPO) requirements. The log should be backed up in intervals that do not exceed the allowable data loss for a system.

A Recovery Time Objective (RTO) specifies the maximum duration of the recovery process, which affects full and differential backup schedules. You should also factor in the time required to transmit files over the network when designing a backup strategy. Backup compression can help reduce this time and improve the performance of backup and restore operations, but at a cost of extra CPU load and extra time as compression and decompression of the data takes place.

You should validate backup files and make sure that your backup strategy is valid and meets the RTO and RPO requirements. The duration of the backup and restore processes changes over time along with database size and load.

SQL Server Enterprise Edition supports piecemeal restore, which allows you to restore data on a file and filegroup basis, keeping part of the database online. This feature greatly improves the availability of the system and helps to reduce the recovery time of critical operational data when the data is properly partitioned.

You can exclude read-only data from regular full backups, which can reduce backup time and the size of backup files. Consider putting read-only data into a separate filegroup and marking it as read-only when appropriate.