The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.
Backing up your SQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss.
With valid backups of a database, you can recover your data from many failures, such as:
- Media failure
- User errors, for example, dropping a table by mistake.
- Hardware failures, for example, a damaged disk drive or permanent loss of a server.
- Natural disasters.
Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up Always On Availability Groups or database mirroring, and archiving.
differential backup : A data backup that is based on the latest full backup of a complete or partial database or a set of data files or file groups (the differential base) and that contains only the data that has changed since that base.
log backup : A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)
full backup : A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
**Place the database and backups on separate devices. Otherwise, if the device containing the database fails, your backups will be unavailable. Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database.
Create a Full Database Backup
-After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
-Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.
-Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears
-In the Database list box, verify the database name. You can optionally select a different database from the list.
In the Backup type list box, select Full.
Database Backups Under the simple Recovery model
Under the simple recovery model, after each backup, the database is exposed to potential work loss if a disaster were to occur. The work-loss exposure increases with each update until the next backup, when the work-loss exposure returns to zero and a new cycle of work-loss exposure starts. Work-loss exposure increases over time between backups. The following illustration shows the work-loss exposure for a backup strategy that uses only full database backups.
Database Backups Under the Full Recovery model
For databases that use full and bulk-logged recovery, database backups are necessary but not sufficient. Transaction log backups are also required. The following illustration shows the least complex backup strategy that is possible under the full recovery model.