Restoring File and FileGroups

• Restoring file or filegroup restores enables you to restore a damaged file instead of restoring an entire database – minimizing the amount of recovery time needed.
• If the damaged filegroup is not in your primary filegroup, then restoration of the damaged filegroup can be done while the otherfiles or filegrops are online.
• By default, when a databae is online.then a filegroup restore will be an online operation. the file group that is being restored will remain offline until restoration is complete.

Execute the RESTORE DATABASE statement to restore the file and filegroup backup, specifying:

• The name of the database to restore.
• The backup device from where the full database backup will be restored.
• The FILE clause for each file to restore.
• The FILEGROUP clause for each filegroup to restore.
• The NORECOVERY clause. If the files have not been modified after the backup was created, specify the RECOVERY clause.
• If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying
• The name of the database to which the transaction log will be applied.
• The backup device from where the transaction log backup will be restored.
• The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.
• The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).

This example restores the files and filegroups for the MyNwind database. Two transaction logs will also be applied, to restore the database to the current time.

other

USE master
GO
-- Restore the files and filesgroups for MyNwind.
RESTORE DATABASE MyNwind
   FILE = 'MyNwind_data_1',
   FILEGROUP = 'new_customers',
   FILE = 'MyNwind_data_2',
   FILEGROUP = 'first_qtr_sales'
   FROM MyNwind_1
   WITH NORECOVERY
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log2
   WITH RECOVERY
GO


Source:
http://msdn.microsoft.com/en-us/library/ms178099.aspx
Pro SQL Server 2008 Administration - Ken Simmons and Sylvester Carstarphen

 

Restoring transaction Logs

• Always used in conjunction with full and/or differential database restores.
• Allows you to retrieve or apply changes within the transaction log to your database
• This allows you to restore to a specific point in time
• As long as the LSN (Logical sequence number) is in order and you have a full full backup, any number of contiguous transaction log backups can be restored.
• Make sure the database is left in a “STANDBY” or a “NORECOVERY” state when you restore the full and the differential database backups.
• If you want to apply multiple transaction logs, make sure you use the standby or the no recovery option for each restore until the last of the transaction log is applied
• the logs must be restored in the order in which they were taken based on the LSN

RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;

When applying the last log backup, you can do either of the following:

• Recover the database as part of the last BACKUP LOG statement:

  RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY;
  GO

• Wait to recover the database by using a separate RESTORE DATABASE statement:
  RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY; 
  RESTORE DATABASE <database_name> WITH RECOVERY;
  GO

  Waiting to recover the database gives you the opportunity to verify that you have restored all of the necessary log backups. This approach is often advisable when you are performing a point-in-time restore.

 

Split File Backup

SQL Server backup command can take backups in two or more split parts. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

The backup is split into three equal parts of the original backup file size.

Restoring it is very similar – just add an additional DISK option.

RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Source: http://blog.sqlauthority.com/2009/09/02/sql-server-mirrored-backup-and-restore-and-split-file-backup-2/

 

Restore Verifyonly

This operation

• checks to make sure that a backup is intact physically
• ensures that all the files in the backup set is readable and can be restored
• Does not verify the structure of the data on the backup. However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup

Checks performed by RESTORE VERIFYONLY include:

• That the backup set is complete and all volumes are readable.
• Some header fields of database pages, such as the page ID (as if it were about to write the data).
• Checksum (if present on the media).
• Checking for sufficient space on destination devices.

RESTORE VERIFYONLY FROMDISK=‘c:\sqlskills\broken2005.bck’;

GO

Source:http://msdn.microsoft.com/en-us/library/ms188902.aspx

 

File and filegroup backups

A file backup contains all the data in one or more files (filegroup). This backup can be performed when the database size and performance requirements make a full database backup impractical.

Minimally, this statement must specify the following:

• The database name.
• A FILE or FILEGROUP clause for each file or filegroup, respectively.
• The backup device on which the full backup will be written.

So long as you do not need to restore the priary filegroup and you are running sql server 2008 enterprise, the database can remain online and accessible to applications during the resotre operation. Only theportion of the database being restored is off line.

Partial Backup
To reduce the size of a backup to only the filegroups that can change, you can perform a partial backup. Patrital backps are performed by specifying the READ_WRITE_FILEGROUPS option as follows

BACKUP DATABASE DBNAME READ_WRITE_FILEGROUPS [filegrouplist] TO DEVICE

When a partial backup is executed, SQL SErver backs up the primary filegroup, all read/write filegroups, and any explicitly specified read only filegroups

Examples of filegroup backups

A. Creating a file backup of two files

The following example creates a differential file backup of only the SGrp1Fi2 file of the SalesGroup1 and the SGrp2Fi2 file of the SalesGroup2 filegroup.

--Backup the files in the SalesGroup1 secondary filegroup.
BACKUP DATABASE Sales
   FILE = 'SGrp1Fi2',
   FILE = 'SGrp2Fi2'
   TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck'
GO

B. Creating a full file backup of the secondary filegroups

The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GO

C. Creating a differential file backup of the secondary filegroups

The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GO
   WITH
      DIFFERENTIAL,
GO

Source: http://msdn.microsoft.com/en-us/library/ms179401.aspx

 

Tail Log Backup

Tail Log backups will backup those transactions in the log file which have not yet been backed up since the last backup.

In case of a disaster of a database, and a point in time recovery is needed, a tail log backup must be taken first. If you begin to restore the database first, the opportunity to restore thail log of the backup is forever lost.

Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database and do not need to restore it to a point of time after the most recent backup.

Source: http://msdn.microsoft.com/en-us/library/ms179314.aspx

 

Backupset

This is a table in the msdb database. It contains a single row for each backup set. A backup set contains the backup from a single, successful backup operation. You can get information regarding the size of a backup vs the compressed backup size.

 

Estimate a backup size

SQL Server only backs up the data pages in the database that have been used currently. So a full backup is generally smaller than the database size

You can use sp_spaceused to estimate the full backup size by subtracting the unallocated space from database_size

Source: Pro SQL Server 2008 Administration by Ken Simmons and Sylvester Carstarphen

 

Backups

• Full Backups – Contains all the data in a database or set of files or filegroups and also enough log to allow for recovering that data
• Only operations not allowed during full backup are shrink database, and adding/removing database file
• When CHECKSUM is specified, SQL Server verifies the page checkum, if it exists, before writing the page to the backup. In addition, a checksumis calculated for the entire backup that can be used to determine if the backup has been corrupted. The default behavior for errors encountered during a backup is STOP_ON_ERROR. If an invalid page checksum is encountered during a backup, the backup terminates with an error. To continue past the error and backup as many pages as possible, you can specify the CONTINUE_AFTER_ERROR option
• To ensure concurrent access and consistency, following steps are performed during a backup operation
1. SQL Server locks the database, blocking all transactions
2. Places a mark on the transaction log
3. Releases the database lock
4. Extracts all the pages in the data files and writes them to backup device
5. Locks the database, blocking all transactions
6. Places a mark in the transaction log
7. Releases the database lock
8. Extracts the portion of the log between the marks and appends it to the backup device
• Differential Backup  –
• Based on latest full backup aka differential base
• Differential backup contains only data that has changed since the last full backup (differential base)
• Diff. backups are smaller and faster to create than differential base if done soon after a full backup.
• At restore, full backup is restored first, followed by most recent differential backup
• In simple recovery model, a diff. backup can use only one differential base. In full recovery models, differential file backups can use multiple bases, but gets confusing
• Transaction Log Backup
• Only used for full and bulk logged recovery models
• Steps involves first taking a full backup, followed by an unbroken chain of transaction log backups.
• Each log backup covers that part of the transaction log that was active when the backup was created, and everything else that was not backed up during the previous log backup
• Frequent backups of the log not only minimizes risk of work loss, but also enables log truncation. Log truncation typically occurs after every log backup.
• SQL Server 2005 and later allows simultaneous backup of transaction log file while the full backup is being taken.
• A new log chain is started only when the recovery model is switched from simple to full or bulk logged; or a full backup is taken

Source:
BOL http://msdn.microsoft.com/en-us/library/ms175477.aspx
MCTS 2008 – Training Kit – Mike Hotek