SQL Server 2008 data security (Backup and recovery database) _mssql2008

Below is my summary of some of the content, which biased the implementation of T-SQL statements, the SQL Server Management Studio in the object manager's operation does not have much to summarize, because these are some wizards, and most of them are in the corresponding node, right click to find the appropriate action , the corresponding object, and then the Basic Wizard to operate!

The first is the summary of the approximate knowledge points:

Below is a summary of some T-SQL statements, 1, the statement that manages the backup device:

2, back up the statement:

3, the corresponding statement of data recovery:

At last

It says the backup has full backups, differential backups, transaction log backups and filegroup and data file backups, recovery with simple restores, simple restores, and bulk-logged recovery. But what are the different uses of these four types of backup? What are the requirements for these three recoveries? Here, I will give you an anatomy:

Backup:

1, full backup: Backup content, including all data in the backup database, filegroup or data file; applicable type: For small databases and midsize databases, full backups are the most common technology. Disadvantage: This process is time-consuming, and once you start a backup, you cannot stop halfway.

2, differential backup: Back up: Record data that has changed since the last time it was backed up; Type: Database used for full backups; Disadvantage: Restores are time consuming, and restoring needs to restore the last full backup and all subsequent differential backups.

3, transaction log backup: Backup content, backup the completed transactions in the database, the implementation of the backup can be truly flexible point-in-time recovery; applicable type: The database is in full recovery and bulk-logged recovery model;

4, Filegroup Backup: Content, a backup of all data files related to a file in a database. Similar to a full backup, but can be a backup of a small branch. For example, you can back up a backup of one department or workgroup in a company.

5, data file backup: Content, only a file in the filegroup backup, and restore a single data file with the function of working together. Advantages, short time, can be selected to backup some files in the database.

Recovery:

1, Simple recovery: Required: Database recovery using only database and differential backups without transaction log backups. Effect: You can revert to the state of the previous backup, but you cannot revert to the point of failure.

2, Full recovery: need, with database backup, differential backup and transaction log backup to restore to the point of failure, all database operations need to be written to the log file, the effect, do not cause any loss.

3, Bulk-Logged backups: required, and full backups are essentially the same; the effect is superior to the top two in terms of performance, and it works best to reduce the storage space required for batch operations.