Home

Backup data

Prev Page Next Page
Introduction
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Failover
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Failover
Log shipping in SQL Backup
Using the CopyTool utility
Failover
3rd party backup applications
VDI
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Encryption
Integrated database verification
Database file relocation
Improved backup retention
RESTORE HELP
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
Pending
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship



This document describes how SQL Backup performs backups, to aid readers in understanding the similarities between a backup performed using SQL Backup and one performed using SQL Server.

Native SQL Server backup

When you perform a backup using SQL Server, you issue s a T-SQL BACKUP command to SQL Server, with the relevant options set e.g. DISK, DIFFERENTIAL etc. E.g.

BACKUP DATABASE pubs TO DISK = 'E:\Backups\pubs.sqb' WITH DIFFERENTIAL

SQL Server then generates the relevant backup data and writes them out to disk or tape.

flow01

SQL Backup backup

When you perform a backup using SQL Backup, SQL Backup issues a T-SQL BACKUP command to SQL Server, with the relevant options set e.g. DISK, DIFFERENTIAL etc. The command is almost similar to that used when using native SQL Server backup. One difference is that instead of the DISK option, SQL Backup uses the VIRTUAL_DEVICE option e.g.

BACKUP DATABASE pubs TO VIRTUAL_DEVICE = '<some unique value>' WITH DIFFERENTIAL

SQL Server then generates the relevant backup data, but does not write it out to disk or tape. Instead, it informs SQL Backup where the backup data can be found. SQL Backup then compresses this data, optionally encrypts it, and writes it out to disk.

flow02

Points to note:

·The backup data is generated by SQL Server in all cases. SQL Backup does not generate any backup data.

·The backup data that is generated by SQL Server is identical (aside from block sizing issues) in both the above cases.

·Because the backup data is identical, it is possible for SQL Backup to convert its compressed files to native SQL Server backup files, using either the CONVERT command or the sqb2mtf utility. These converted files can then be restored using native SQL Server RESTORE commands.

·Because the backup data is identical, you can perform the same tasks using SQL Backup as you would using native SQL Server backups e.g.

1. perform full, differential, transaction log, partial, filegroup backups and restores, STOPAT, PAGE restores etc, as long as SQL Backup supports the required SQL Server option
2. run RESTORE VERIFYONLY, RESTORE HEADERONLY, RESTORE FILELISTONLY commands
3. backup a SQL Server 2000 database and restore the database on a SQL Server 2005 or SQL Server 2008 instance

For further details, see this document

 




Document history
12/3/2008    Initial release.    
 
Copyright 2008 - 2017 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.