Database Backups and Restore

The SQL database is logically divide into filegroups. A logical filegroup in SQL maps to a set of physical files on the disk. It is mandatory to have atleast one filegroup called PRIMARY and it is created by default. New filegroups can be created by users if required

ALTER DATABASE BCG ADD FILEGROUP masters
ALTER DATABASE BCG ADD FILEGROUP transactions

A filegroup can be considered as a logical storage unit to house database objects which map to one or multiple filesystem files. New filegroups can be easily added to an existing database to partition it.

ALTER DATABASE BCG
ADD FILE (NAME='BCG_data_1', FILENAME='C:\BCG_data_1.ndf')
TO FILEGROUP masters

Filegroups as stored in a system catalogue called sysfilegroups so a list can be easily obtained
select * from sysfilegroups

To move an existing table to a newly created filegroup a clustered index can be created as the base table is always stored with the clustered index so moving the clustered index also moves the table.

CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail (ProductID)
ON masters

Another great advantage of using filegroups apart from partitioning the database is that advanced restore options make it possible to restore just one filegroup from the available ones in a database. This allows a partial recovery of the SQL database which comes very handy when the database is huge.

Irrespective of the filegroup being restored the primary filegroup is always restored as the catalogs are stored on the primary filegroup (except full-text catalogs) they too are always restored and all the ones not available are marked as offline.


backup database BCG
to disk = 'c:\bcg.bak'

This command will list all the files and filegroups in the backup file
restore filelistonly
from disk = 'c:\bcg.bak'

This command will list all the backup sets in the backup. A backup can contain more then one backup sets if a backup is applied on a backup file the file is not replaced instead the new backup is added onto the file with a new file number

restore headeronly
from disk = 'c:\bcg.bak'

which backupset to restore from the backup file can be specified using the file=number clause in the restore statement. This clause should appear after the with keyword in the restore command

A partial recovery is indicated using the partial clause in the restore command as shown below.
restore database BCGNew
filegroup = 'primary'
from disk = 'C:\bcg.bak'
with partial, recovery
, move 'BCGWDCM_GLC_Data' to 'C:\BCGNew_data'
, move 'BCG_data_1' to 'C:\BCG_data_1'
, move 'BCGWDCM_GLC_Log' to 'C:\BCGWDCM_GLC_Log'

When a partial recovery is made although all the tables are created only the tables on the filegroup being restored are available to the user all other tables are marked as offline.