Home

Introduction

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
Why you shouldn't rely on RESTORE VERIFYONLY
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
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.

Welcome to SQLBackupRestore.com, your online resource for SQL Server backup and recovery issues.  The articles are generally applicable to both SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL 2012.  If any parts are applicable only to a specific version, they will be clearly indicated.

The SQL Server Books Online documentation should still be your primary source of information on SQL Server.  The information presented here is more of a troubleshooting guide, a discussion of common issues faced by users, when dealing with SQL Server backup and recovery issues.

If there are any topics you would like me to add, or suggestions on improving the articles, please drop me a line at yeoh.ray.mond@yohz.com.


Conventions

When a reference is made to a topic in the Books Online documentation, they are highlighted in bold blue, and the version of SQL Server they apply to is also stated.  E.g.  (Bulk-Logged Recovery [SQL2000], Backup Under the Bulk-Logged Recovery Model  [SQL2005]) means look for the 'Bulk-Logged Recovery' topic in the SQL Server 2000 documentation, or the 'Backup Under the Bulk Logged Recovery Model' in the SQL Server 2005 documentation.

Notes are highlighted this way, and are issues that are important to the current topic.

Code fragments are highlighted this way:

BACKUP DATABASE AdventureWorks TO DISK = 'G:\Backups\AdventureWorks.bak'

Output from SQL Server are highlighted this way:

Processed 20584 pages for database 'AdventureWorkds', file 'AdventureWorks_Data' on file 1.
Processed 2 pages for database 'AdventureWorkds', file 'AdventureWorks_Log' on file 1.
BACKUP DATABASE successfully processed 20586 pages in 10.101 seconds (16.694 MB/sec).

Database names are highlighted this way e.g. AdventureWorks, and tables are highlighted this way e.g.Vendor.

SQL Server commands are highlighted this way e.g. BACKUP LOG, and command options are highlighted this way e.g. WITH NORECOVERY.


About me

My name is Yeoh Ray Mond, and I presently work as a support analyst supporting a third party SQL Server backup tool.  I have previously worked as a administrative DBA on Oracle databases, development DBA for SQL Server 7, 2000 and 2005, and application developer for the above mentioned backup tool.  As a support analyst, I frequently encounter very interesting questions from users, which I thought would be useful to share, hence this site.

In my spare time, I develop database tools and miscellaneous utilities.  You can find details on my web site at http://www.yohz.com.

 

 
Copyright 2008 - 2013 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.