Lecture 7 - Backup and restore

Backup and restore Marcin Szeliga [email protected]

Backup and restore › Database recovery options › Planning a database backup strategy › Backing up a user database › Restoring user databases › System databases and disaster recovery

Database recovery models › Databases can operate in one of three model: – FULL – SIMPLE – BULK_LOGGED

› Sets using ALTER DATABASE – And through the execution of the first, full backup of the database

› Affects: – – – –

The speed of some operations The size of the transaction log The size of the transaction log backup Ability to restore a databse to specyfic point in time

FULL recovery model › All operations are fully logged – Actions logged minimally save entire pages of data plus information about formatting new pages

› Checkpoints do not deactivate the virtual log files – The transaction log will grow – Only a copy of the log disables zero or more VLFs

› It is necessary to regularly backup log › Ability to restore the database from any point in time – Guaranteed restoration from the last copy log – Possible recovery from the time of failure

BULK_LOGGED recovery model › Minimally logged operations do not write whole pages of data – The log can be much smaller

› Log backup contains pages modified by these operations – Data files must be ONLINE during the execution of a copy of the log – A backup can be much bigger

› Checkpoints do not deactivate the virtual log files – The transaction log will grow

› Only a copy of the log disables zero or more VLFs – It is necessary to regularly backup log

› Inability to restore the database from the time in which was performed minimally logged operation

SIMPLE recovery model › The default for Express edition › Checkpoints deactivate zero or more VLs › The transaction log is periodically overwritten – Log can be much smaller

› Minimally logged operations do not write whole pages of data › It is impossible to make a copy of the log – It is possible to make only copy files and groups of files read-only

› Strategy for playing backups will be very simple – In this mode, it is possible to restore the database only until the last executed full or differential database – Switching mode to SIMPLE breaks the continuity of log backups

Backup and restore › Database recovery options › Planning a database restore strategy › Backing up a user database › Restoring user databases › System databases and disaster recovery

Restore strategy › Backups are created in order to: – Restore the database in case of: › Failure › user error

– Initiate replication or duplication of databases – Transfer the database to another SQL server – Protection against unexpected consequences of: › SQL Server update › Attempts to repair the consistency of the database

– Controlling the size of the transaction log

SQL Server backup devices › Device backups can be: – Local hard disk – Network Share – Locally connected and Windows-compatible tape drive

› Two types: – Physical › Identified by file names

– Logical › Their definition is stored in a master database and creating and recreating a copy, we ca use their name

› In one device, a backup can save multiple copies – Storing multiple copies of individual files has two major disadvantages: › Adding to file another copy can accidentally (by using the FORMAT or INIT) erase previously stored in the file copies › Restore requires a copy of their number (position)

– Each backup device has a header with meta information stored in the copies

SQL Server backup types › The three main types: – Full database backups – Differential database backups – Transaction log backups

› Additional: – Files/filegroups backup – Partial (primary filegroup plus all read and write filegroups, plus the indicated read-only filegroups) – Tail-Log backup (log backup taken of the tail of the log just before a restore operation) – Copy only (the database or log, without affecting the backup sequence)

› All backups types are created online

Full database backups › They include: – All allocated extends (extends with at least one page allocated) › Size of uncompressed backup can be estimated on the basis of the results of the procedure call sp_spaceused

– Part of the transaction log necessary to restore the database from this backup – They are the backbone of any restore strategy – Allow you to turn off the automatic log truncation › LSN of last archived transaction returns view sys.database_recovery_status

Full database backups – creation process › SQL Server synchronizes, using the checkpoint, buffers with pages stored in the data files › Writes the number of the oldest transactions required to reproduce backup – LSN of first transaction performed after the checkpoint – Or LSN oldest transaction active during its execution – Or smallest number LSN unreplicated transaction

› Sequentially copies extends from data files to the backup device – Copying extends is a physical operation, performed outside buffer cache › In the course database objects are not locked

– After data copied completions, transaction log is saved to backup file › Transaction log is copied to the point of last entry made during a backup

Full database backups – summary › Lack of full backup: – Makes it impossible to restore the database from the backup of other types – Turns the database to automatic log truncation mode

› A full backup can be restored to the point in which data copy phase completed

› STOPAT clause will either be ignored or the database will remain in the RESTORING state

Differential database backups › Contains only extends modified since the last full backup plus extends required to restore the required part of the transaction log – This includes pages modified by the rolled back transactions

› They can not be performed if a full backup is missing › They are independent of the other differential backups of the database › They are performed in the same manner as full backup – The only difference is read (stored in the DIFF) bitmap containing information about extends modified since the last full backup – Extends where the bit is set to be copied – Other extends will be ignored

Differential database backups - summary › Differential backups are cumulative › The only way to reset the bit indicating the modified extends is to perform a full backup – Before switching the file/filegroup of files in read-only mode, do a full backup

› Differential backups can be used to restore broken chain of transaction log backups – More on this later

› Differential backup can be restored to the point in which data copy phase completed

Transaction log backups › They are incremental – Contain all the transaction log records stored in it from the time of the previous log backup – Subsequent copies of the log will never contain the same transactions, except for transactions that were open during backup

› Can not be made if: – Full database backup was not made – The database is in SIMPLE recover model

› After copying the file the appropriate part of the log, SQL Server disables zero or more virtual log files › If the database operates in BULK_LOGGED, copies of the transaction log will also contain data extends modified by minimally logged operations – Make a copy of the log fails, if the file will not be ONLINE

Transaction log backups - summary › They must be carried out regularly if the base operates in a recover model other than SIMPLE – The greater the activity of users, the more frequently – The more frequently will be created, the smaller will be

› Because they are incremental, dependent on all completed earlier (after the last data backup) copies of the log

› They can be made, even if the data file is corrupted – Before restoring the database one should make a copy of the active portion of the log › More on this later

Full database backup strategy › A full database backup strategy: – Involves performing regular full backups – Is appropriate for small databases running in SIMPLE recover model – Is almost always the first to be restored and that other types of backup depend on it

Sunday

Monday

Tuesday

Database and transaction log backup strategy › A full database and transaction log backup strategy: – Involves performing regular backups – Supplementing regular database backups with transaction log backups – Enables restoring the database to the last transaction log backup

Sunday

Monday

Differential backup strategy › A differential backup strategy: – Involves performing full database backups – Supplementing full database backups with differentia backups – Is useful if a subset of a database is modified more frequently than the rest of the database

Monday

Tuesday

File or filegroup backup strategy › A file or filegroup strategy: – Involves backing up individual files or filegroups on a regular basis – Utilizes less disk space than full database backups – Is a strategy for very large databases

Data file 1 Sunday

Monday

Data file 2 Tuesday

Data file 3 Wednesday

Backup and restore › Database recovery options › Planning a database restore strategy › Backing up a user database › Restoring user databases › System databases and disaster recovery

Performing a full database backup T-SQL Syntax:

BACKUP DATABASE database TO backup_device [ ,...n ] [ WITH with_options [ ,...o ] ] ;

› Performs the following actions: – Backups up the specified database – Specifies the backup device – Specifies options i.e. { COMPRESSION | NO_COMPRESSION }

Performing a transaction log backup T-SQL Syntax:

BACKUP LOG database_name TO

› Performs the following actions: – Specifies a backup of the transaction log only – Backs up log from the last successfully executed log backup to the current end of the log – Truncates inactive log records unless {NO_TRUNCATE or COPY_ONLY} is specified

Performing a differential backup T-SQL Syntax:

BACKUP DATABASE database_name TO WITH DIFFERENTIAL

Important: You cannot create a differential database backup unless the database has been backed up first

Performing file or filegroup backups T-SQL Syntax: BACKUP DATABASE database { FILE = logical_file_name | FILEGROUP = logical_filegroup_name } [ ,...f ] TO backup_device [ ,...n ] [ WITH with_options [ ,...o ] ] ;

› Performs the following actions: – Backs up a file or filegroup

› Note: A differential file backup requires a full file backup as a base

Performing a tail log and a copy only backup › Tail log backup performs the following actions: – Backs up transaction log – Uses NORECOVERY to take the database into the restoring state – Optionally specify NO_TRUNCATE or CONTINUE_AFTER_ERROR if you are backing up the tail of a damaged database

› Copy only backup performs the following actions: – Backs up the database without changing the restore order – Backs up the transaction log without truncating it

Backup compression › Backup Compression – Added in SQL Server 2008 – Compresses backup size on device – Increases I/O performance, increases CPU usage

› Restrictions – Can not co-exists on same media as uncompressed backups – Can not be read by earlier versions of SQL Server – Can not share a tape with NT Backups

Options for ensuring backup integrity › Mirrored Media Sets – Copy of the backup media set optionally created during backup – Require the same number of devices one per family

› Backup Verification – Backup checksums can be used to as an additional check to ensure that the backup media is not corrupted – Verifying the backup checks the physical integrity of all backup files, testing to make sure that the files can be restored when needed

Backup and restore › Database recovery options › Planning a database restore strategy › Backing up a user database › Restoring user databases › System databases and disaster recovery

Restore process › The restore process consists of three distinct phases: – The data copy phase involves copying all data, log and index pages from the backup media – The redo phase applies the transactions to the data copied from the backup to be rolled forward to the recovery point – The undo phase is the first part of any recovery and rolls back any uncommitted transactions and makes the database available to users

› After the rollback phase, subsequent backups cannot be restore

Restoring a database › Steps to restore a database: – Perform a tail-log backup – Determine the target recovery point › Full or bulk-logged recovery models only

– Determine the type of restore to apply – Identify which backups you require to restore the database to the required point › Ensure that the required media sets and backup devices are available

– Begin the restore sequence by restoring one or more database backups, partial backups or file backups

Restoring a Transaction log › Steps to restore the transaction log: – Full or differential database restore WITH NORECOVERY › The immediately previous full database backup or differential database backup must be restored first

– Restore transaction logs in order WITH NORECOVERY › All transaction logs created after the last full or differential backup must be restored in chronological order with no break in the chain of back ups and must specify WITH NORECOVERY

– Restore latest log WITH RECOVERY › With the final log backup indicate WITH RECOVERY to recover the database

Overview of online restore › Online Restores – Refers to restoring a database while it is online › A database is considered to be online when it’s primary filegroup is online, even if secondary filegroups are offline

– Supported by all recovery models › Under the simple recovery model only restores of read-only file groups are supported, page and piecemeal restore are not

– Restores occur online by default › Databases need to be taken manually Offline for a restore operation to not be an online restore

› During an online file restore any file or filegroups being restored is offline – This is in contrast to an online page restore where only the page is offline during the restore operation

Backup and restore › Database recovery options › Planning a database restore strategy › Backing up a user database › Restoring user databases › System databases and disaster recovery

Considerations for backing up systems databases › SQL Server maintains a set of system-level databases which are essential for the operation of a server instance › The following system databases must be backed up: – msdb › The database used by SQL Server Agent for scheduling alerts and jobs, and for recording operators › Also contains history tables such as the backup and restore history tables

– master › The database that records all of the system level information for a SQL Server system

– model › The template for all databases that are created on the instance of SQL Server

› Back up the distribution system database if any database uses replication on the server instance

BACKUP AND RESTORE Database recovery options Planning a database restore strategy Backing up a user database Restoring user databases System databases and disaster recovery