Database Backup Internals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database Backup Internals

Author

Topic

connectd.ts
Starting Member

4 Posts

Posted - 2010-09-02 : 12:35:08

I was wondering what are the internal steps that SQL Server performs during a database backup. Websites list the steps to accomplish the database backup but I am trying to learn the internals of what goes on during a database backup.

Thank you.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-03 : 04:25:54

There are quite a few, profile it and you will see.

connectd.ts
Starting Member

4 Posts

Posted - 2010-09-03 : 09:58:50

I was wondering if there are known whitepapers/docs that list the logical steps during an SQL Server database backup/restore operation.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-03 : 10:50:01

This has some information on the Virtual Device Interface for backups:

http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en

Not sure if it's exactly what you're looking for.

sequelkid
Starting Member

3 Posts

Posted - 2012-09-19 : 01:59:45

backup is a transparent process, no locks will be taken on the database objects. The increased IO could cause some slight performance issues.

The transaction log will be continue to be used as normal (you will notice that log backups will be blocked until after the full backup is complete - you need to be aware of this if you have dependancies on the log backups ie log shipping). The Transaction log is backed up as part of a full backup.

From "High Availability", a summary of the backup process is:

1. Checkpoint writes all dirty pages
2. A marker is placed in the transaction log (backup has begun)
3. Data is read from the data files
4. Mark the transaction log, (backup complete)
5. Backup the transaction log (not quite the same as an actual log backup)

Of course there is significantly more to it that the summary above (those five lines are detailed in about 2 pages).

I certainly recommend "high availability" as a must have bookshelf item for any DBA...

As luck would have it, Kimbery has made the chapter you're interested in available from her website: http://www.sqlskills.com/resources/...apter9.pdf

Checkout page 54 in the PDF (page 419 in the book)

SEQUELKID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources