There are always a chance databases suffering from severe system failure and hence causes database corruption, for instance, memory failure, hard drive failure, power failure and so on. Once the database gets corrupted, it’s almost hopeless to recover it without backup. So it’s very important to backup the databases for any database systems. There are two big categories in backup world – cold backup and hot backup.

Cold backup is to use operating system command to backup the database. In this case, SQL Server could not hold exclusive locks to the database file. In following circumstances you could do cold backup

  1. When SQL Server service is off, you are free to do cold backup.
  2. While a database is taking offline
  3. When a database is set to read-only in SQL Server 2005/2008
  4. When a database is detached
  5. Resource database in SQL Server 2005/2008 while SQL Server is running
    1. For resource database, you could only do cold backup.
  6. You could backup tempdb but you have to shut down the SQL Service and the database will be recreated every time when the server starts up.
    1. You may fail to attach tempdb files with difference name when there are any encrypted database before shutting down the server.

Another approach is hot backup(again, it has nothing to do with the tempdb). Hot backup allows the end user to backup the database while it is running. A full back first of all executes checkpoint to flush the data from buffer manager to data file and mark the start LSN. Then scans and copies the pages in database data files to specified backup file regardless changes on backed up pages or un-backed up pages. After the data is copied, the backup process will record the end LSN. Before this point, the data in the backup file is logical and/or transactional inconsistent. Last step, it will copy transaction logs to the backup file. Please look at the illustration below, while backing a database, the system can process the user requests without waiting. In order to make the backup transactional to be consistent, all the transaction logs from “Start” to “End” will be saved.

While restoring a backup, fist of all, SQL Server will create database file and initialize it if there isn’t files (tip, don’t drop the database files before restoring). And then, it copies the data and transaction logs from the backup file. The third is to redo the transactions. In the illustration above, the Trans1 and Trans2 will be redone. This means that the data being modified by Trans1 and Trans2 will appear in the restored database. After this point, the database is accessible in SQL Server 2005 and 2008. The last step is to undo the transaction. Transactions haven’t been committed/rolled back after the backup started but before the end of the backup will be rolled back. In the example above, Tran3 and Tran4 will be rolled back. Backup a database: Backup database DatabaseName to disk = ‘d:\DatabaseName.bak’ Restore a database: Restore database DatabaseName from disk = ‘d:\DatabaseName.bak’ For more information regarding syntax, please check with book online. Backup: http://msdn.microsoft.com/en-us/library/ms186865.aspx Restore: http://msdn.microsoft.com/en-us/library/ms186858.aspx Other resource: http://www.sqlskills.com/blogs/paul/category/BackupRestore.aspx

Cold and Hot Backup

You May Also Like

3 thoughts on “Cold and Hot Backup

  1. Heya! I’m at work surfing around your blog from my new apple iphone! Just wanted to say I love reading through your blog and look forward to all your posts! Carry on the outstanding work!

Leave a Reply to John H Cancel reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.