Database Backups

Backup Terminologies:

Backup Device:A backup device is either a tape or disk file that is provided by the operating system. Its a stored path.

Backup Set:A backup set contains the backup from a single, successful backup operation. It can be a FULL/Diff/Tlog backup.

Media Set:A media set is an ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices.

Media Set Family:Backup splited into two files and called backup files belong to same family

Checksum:Checksum is verified to ensure if data is corrupted or not.

A restore scenario in SQL Server is the process of restoring data from one or more backups and then recovering the database. The supported restore scenarios depend on the recovery model of the database and the edition of SQL Server.

Possible Restore Scenarios are:1) Complete database restore2) File restore3) Page restore4) Piecemeal restore

Restore and its Phases:

Restoring is the process of copying data from a backup and applying logged transactions to the data to roll it forward to the target recovery point.

A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:

Data Copy Phase:The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files.

The Redo Phase (Roll Forward):The redo phase applies the logged transactions to the data copied from the backup to roll forward that data to the recovery point.

Undo Phase (Rollbackward Phase):The undo phase, which is the first part of recovery, rolls back any uncommitted transactions and makes the database available to users. After the roll back phase, subsequent backups cannot be restored.

Restore is only copying data/log but Recovery is ensuring that data is consistent and clean. Recovery is a subset of restore.

Types of Restore1) Restore an entire database from a full database backup (a complete restore).

2) Restore part of a database (a partial restore/Piece Meal Restore).

3) Restore specific files or filegroups to a database (a file restore).

4) Restore specific pages to a database (a page restore).

5) Restore a transaction log onto a database (a transaction log restore).

6) Revert a database to the point in time captured by a database snapshot.Full Backup Restore:restore database Test from disk=N'F:\Test.bak' WITH NORECOVERY, replace

Differential Backup Restore:restore database Test from disk=N'F:\Test_Diff.bak' WITH NORECOVERYTlog Backup Restore:

restore log Test from disk=N'F:\Test_Tlog1.trn' WITH NORECOVERY

Point-in-time Recovery:Syntax: restore log dbname from disk='path' stopat 'dd:mm:yyyy hh:mm:ss'

restore log Test from disk=N'F:\Test.trn' WITH NORECOVERY STOPAT '06-21-2012 06:50:00AM

Tlog restore with Recovery:restore log Test from disk=N'F:\Test_Tlog2.trn' WITH NORECOVERY restore database Test with recovery

F&FG Restore:restore database NewDB123 FILEGROUP='Primary', Filegroup='Secondary' from disk='c:\dummy\NewDB_FG.bak' with recovery restore database rummytest filegroup='primary',filegroup='secondary',filegroup='third' from disk='e:\rummyfg.bak' with recovery

Commands of Restores:How to validate Backups?SQL Server provides multiple options to validate backups1) Validating a backup restore verifyonly from disk=Backup file Path'Output: The backup set on file 1 is validThis confirms that the backup has been validated and its a good one which can be used for restore operation.2) Validating a header of a backup restore headeronly from disk= Backup file Path3) Verifying the contents of the backup restore filelistonly from disk= Backup file Path '

DatabaseFullDifferentialTlogMASTERYESNONOMODELYESYESYESMSDBYESYESNOTEMPDBNONONOPlease find system database backup possibility:Resource database: Backup statement wont work on resource database due to Read_only\Hidden database. File level MDF , LDF copy is the option to maintain as backup.3rd Party Backup Tools:

1) Quest Litespeed2) Idera SQL Backup3) IBM Tivoli Storage Manager4) Veritas Netbackup5) Symantec BackupExec6) Acronis 7) EMC Networker 8) Backup and FTPZAMANDA( AMANDA)Backup & Restore Tables in MSDB:Backup and restore tables track information about backup media and devices. You can take backups to a tape as well as disk. SQL Server also allows backing up files to a network share.

Steps in a Transaction Log Backup:1) Verify if the FULL backup has a base present for it or not2) Verify if any tlog backups/differential backups are taken or not3) Verify the last LSN backed up and calculate what all LSN's should be backed up from the transaction log file4) Backup the active log portion of the LDF file5) Finally perform the truncate operation after backup is completed for space reusability in the transaction log fileDifference between TRUNCATE_ONLY and NO_TRUNCATENO_TRUNCATE: It takes a log backup, but will not perform truncate operation. We will generally use this during database crash/suspend scenarios (or) when we need to take backup of the Active portion i.e. the Tail.> backup log to disk='PATH' with NO_TRUNCATETRUNCATE_ONLY: It truncates ONLY active portion of the log and ensures that the VLF's are marked as truncated for usage by further transactions. This generates some free space inside the log file to accomodate further transactions> backup log with TRUNCATE_ONLY> backup log to disk='PATH' with TRUNCATE_ONLY

Questions:If database is in offline state? Can backup is possible?What permissions are required to user to perform backups?Real time Recovery scenarios? How will you recover the database?Differ between full and simple recovery model?Diff between full and diff backup?

Senerio-1

Senerio-2

I haveStandard edition backup file. I can possible to restore inEnterpriseedition? Yes

I haveEnterpriseedition backup file..I can possible to restore inStandardedition..? YesRestore an SQL 2008 full backup to SQL Server 2005? No, No backward computability is not providedMYTHS ON DB BACKUPS:http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/

http://msdn.microsoft.com/en-IN/library/ms188653.aspx