SQL Server-Data Security

Backing up and recovering databases is an important task for database administrators to ensure data security. SQL Server 2008 provides high-performance backup and recovery functions, allowing you to perform database backup and recovery in multiple ways, avoiding data loss caused by various faults! Below is my summary of some content, which is biased towards the summary of the implementation of T-SQL statements, for the SQL Server Management studio Object Manager operations are not too much summary, these all have some wizard, and most of them are on the corresponding node. Right-click the corresponding operation, find the corresponding object, and then perform the operation in the basic wizard!

 

The first is the summary of general knowledge points:

 

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

 

2. Backup statement:

 

3. Statement for data recovery:

 

Finally,

As mentioned above, full backup, differential backup, transaction log backup, file group, and data file backup are provided for backup, which provides simple recovery, simple recovery, and large-capacity log recovery. But what are the differences between the four backup methods and their respective functions? What are the conditions for these three types of recovery? Here, I will give you an analysis:

 

Backup:

1. Full backup: Backup content, including all data, file groups, or data files in the backup database. Applicable type: Full backup is the most common technology for small and medium-sized databases. disadvantage: This process is very time-consuming. Once backup is started, it cannot be stopped midway through.

2. Differential backup: Backup content: records the data changed since the last backup; applicable type: Used in databases that have been fully backed up; disadvantage: time-consuming recovery, restore the last full backup and all subsequent differential backups.

3. Transaction Log backup: Backup content, backup of completed transactions in the database, enabling real and flexible Backup recovery at a time point. applicable types: the database is in full recovery and large-capacity log recovery mode;

4. File Group Backup: content, which backs up all data files related to a file in the database. it is similar to a full backup, but it can be a backup of a small branch. for example, you can back up a backup of a department or working group in a company.

5. data file backup: content, which backs up only one file in the file group. It works together with the function of restoring a data file separately. advantages: it takes a short time to back up some files in the database selectively.

 

Recovery:

1. Simple recovery: required: Only database backup and differential backup are used for database recovery, but transaction log backup is not involved. Effect: the status of the last backup can be restored, but the status of the failed point cannot be recovered.

2. Full recovery: You need to use database backup, differential backup, and transaction log backup to restore to the point of failure. You need to write all database operations to the log file. effect, no loss.

3. large-capacity log backup: Yes. It is basically the same as full backup. The performance is superior to the above two methods. It minimizes the storage space required for batch operations.