Thread: Databse Backup

Hello Friends,

How to take backup of SQL Server Databse.

Thanks in Advance,
Adarsh

Recovery models sql server 2000 introduces the concept of recovery models for databases. Recovery models are designed to simplify the administration of sql server 2000 databases. There are three recovery models in sql server 2000—full, bulk-logged, and simple. System databases (including master, msdb, and tempdb) are set to the simple recovery model. All user databases, by default, are created with the full recovery model (it should be noted that the full recovery model takes affect once a complete database backup is performed). The recovery model may be changed once the database is created. The recovery model for a database incorporates the two most often used settings—truncate log on checkpoint and select into/bulkcopy. Truncate log on checkpoint. In previous versions of sql server, this setting was selected to automatically truncate the transaction log every time checkpoint is activated for the database. Select into/bulkcopy. This setting was used in previous versions of sql server to perform non-logged operations. Following are the settings and their relation to the three recovery models. Recovery model select into / bulkcopy truncate log on checkpoint full false false bulk-logged true false simple true/false true top of page simple recovery model this recovery model facilitates the maintenance of a database by making the transaction log virtually maintenance free. There are limitations placed on the recoverability of a database if this recovery model is used. Top of page bulk-logged recovery model a database in this recovery model will have minimum logging for bulk import operations. Space allocation and deallocation is only logged for bulk import operations. Point-in-time and point-of-failure recovery may be possible when a database is in bulk-logged recovery model. Top of page full recovery model sql server performs full transaction logging for any bulk load operations if a database is in full recovery model. Transaction log backups should be performed at regular intervals for maximum recoverability. This model provides the safest mode of operation for production systems. The following table summarizes the recovery models and backup types available with each recovery model. Recovery model/ backup complete differential transaction log file / filegroup simple required allowed not allowed not allowed bulk-logged required allowed required allowed full required allowed required allowed top of page partial database restore new functionality in sql server 2000 provides commands to restore a database backup partially. If a database contains several filegroups, a single filegroup may be recovered using this new functionality. Partial database restore operations provide a means to restore only certain parts of the database, as needed. Top of page enhancements to differential backups differential backups have been enhanced in sql server 2000. A bitmap of modified extents has been added to the database structure. This bitmap contains a bit for each extent that has been modified since the previous complete backup. This bitmap is referenced when a differential backup is initiated and only modified extents are referenced and backed up. This significantly improves the performance of differential backups in sql server 2000. Top of page password-protected backup files new functionality has been added to backup functionality to password-protect the backup files. This is in line with the security initiative and helps protect against unauthorized access