Overwrite all existing backup sets with the current backup, leaving the current media header in place.ĭefault Backups performed at different times from the same or different databases only at the end Number of backup devices Type of backup devices Number of backup setsĭeletes the old header (if any) deletes all previous contents of the currently mounted tape writes a new media header The number of backup media in a media set depends on several factors: Successful backup operation adds a single backup set to the media set.īackup media (only one media family) – entire backup set (multiple media families) – backup distributedīACKUP DATABASE AdventureWorks2012 TO TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2‚ WITH FORMAT, MEDIANAME = 'MyAdvWorks_MediaSet_1' Standard backup format – Microsoft Tape Format(MTF) Informations: Name, sequence number of the media family, unique vendor identification number, etc. Media Families Backups created on a single nonmirrored device or a set of mirrored devices in a media set Each disk/tape - media sequence number SELECT backup_size/compressed_backup_size FROM msdb.backupset Low-priority compressed backups - Resource Governorīackup_size:compressed_backup_size Factors: type of data, consistency of the data, encryption, if its compressed Increases the reliability of backups by providing redundancy damaged ->no longer usable mirrored media: 2-4 mirrorsįrom SQL Server 2014 Compressed and uncompressed backups cannot coexist in a media set. User-defined name points to a specific physical backup device must be unique sys.backup_devices - to view the existing logical device names BACKUP DATABASE AdventureWorks2012 TO AdventureWorksBackups GO Permissions needed – to read/write Universal Naming Convention (UNC) \\Systemname\ShareName\Path\FileNameīACKUP DATABASE AdventureWorks2012 TO DISK = '\\BackupSystem\BackupDisk1\AW_backups\AdventureWorksData.Bak' GO Specifying a Backup File by Using its Physical Name BACKUP DATABASE database_name TO DISK = īacking Up to a File on a Network Share
Only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.Ĭontains all the data in a specific databaseīackup disk media set physical backup device maximum size – free disk space backup disk - different than the database data and log disks
Represent the whole database at the time the backup finished. Recorded in the is_copy_only column of the backupset table.
Without affecting the overall backup and restore procedures for the database
Backup and Recovery in MS SQL Server Andrea ImrichováĬopy-only backup database backup differential backup full backup log backup file backup partial backup